This is Robert's Typepad Profile.
Join Typepad and start following Robert's activity
Join Now!
Already a member? Sign In
Robert
Munich, Germany
Interests: new media, optimization, strategy, forecasting, business intelligence, project management, financial planning, business plans, telecommunications, revenue management, business performance management, simulation, pricing, intelligent data analysis, business cases, revenue assurance, cable operator, operations research
Recent Activity
Image
How to create customizable, meaningful tooltips on XY Scatter Charts in Microsoft Excel Continue reading
Posted 8 hours ago at Clearly and Simply
Hema, sorry, I do not understand the problem. Why should the parameter based custom sorting not work across several worksheets? A parameter is global, i.e. it has one defined value throughout all sheets in the workbook. As long as you have set the Sort by Field correctly on all sheets, the sort order will change on all sheets after you changed the parameter.
Toggle Commented Aug 20, 2016 on Dynamic Sorting with Tableau at Clearly and Simply
1 reply
Kevin, since version 2007, Microsoft Excel files are actually zipped folders including XML and other files. If your Internet browser opens Windows Explorer when clicking on the download link, right click on the link instead and select "Save Target As" to download. If you are using Firefox or Chrome, you can then open the workbook with Excel by double clicking on the file. If you are using Microsoft's Internet Explorer or Edge, however, the IE will change the file extension from .xlsm to .zip during download. Simply change the file extension back to .xlsm.
1 reply
Vinod, I am sorry, but I do not have the time to do the VBA development for you. The workbook and code is open, i.e. not password protected, though. If you do not have the experience in writing VBA code, maybe you can find a colleague of yours who does?
1 reply
Vinod, Requirement A: 1. You can add or delete rows as you like, you just have to take care of the numbering convention in the first column of the checklist and – in case you add items at the end of the checklist – to make sure the named range covers the entire checklist. Please refer to my reply to Evelyn's comment above for a step-by-step. 2. The Completion rate UDFs will always be based on the number of rows in the checklist, so there is no need to change anything here. Requirement B: This needs some extra coding for the additional checkboxes above each column and some changes in the UDFs. Have a look at checklist 12_check_list_select_relevant_items, which allows to decide for each item whether it is relevant and shall be included in the completion rate calculation. You would have to change the completion rate functions of the template 08 along these lines.
1 reply
karthik, you have to adjust the left position of the vertical line according to the date where you want it to be. If – for instance – the vertical line shall be at today, you would do a calculation like this: (cnstTableWidth / _ ((Month(datProjectFinish) - _ Month(datProjectStart) + 1) * 30)) * _ (Date - datProjectStart + 1) Then add the offset constants which define the left of the Gantt chart and use the result as the left position of your vertical line.
1 reply
Jake, this post was published in 2011, the other one even back in 2010. The technique with the simple URL-link worked fine back then. Nowadays Google requires a mandatory API-key which has to be specified in the URL. You can get an API key from Google for free and you would then have to change URL-links in the data as described here: Google Maps Embed API
1 reply
James, yes, I would only change the alias to e.g. "Post_Code_5": required_geocoding_fields: name (String): alias: Post_Code_5 unique_id: true If you can't get it working, you can send me a download link to your shape files (here in a comment or by email) and I will give it a try.
1 reply
James, ah, ok, I didn't know that. If Tableau does not recognize 5 digit postcodes of the UK, you will probably indeed have to either work with TabGeoHack or with the polygon map approach. I looked at the –-info output you provided in one of your previous comments and it seems to me as if "name (String)" is the name of the field in the shape file which contains the names of the postcodes, because –-info says name (String) = AB10 1 so you have to specify "name (String)" as the required_geocoding_field in your YML file.
1 reply
James, if you want to have a zoom of e.g. the greater London area (and maybe Manchester, too) close to the "regular" map of the UK, you can do this in Tableau on a dashboard by using several views on a dashboard, i.e. one view showing the UK and one view showing the London area only (by setting the filter accordingly). Have a look at this Tableau knowledge base article which describes how to show Alaska and Hawaii close to the continental United States: http://kb.tableau.com/articles/howto/showing-hawaii-and-alaska-near-continental-us
1 reply
James, which version of Tableau are you using? As far as I know, Tableau recognizes UK postcodes since version 9.3. I.e. if you have the latest version of Tableau, you do not need the TabGeoHack workaround for the United Kingdom (and many other countries) anymore.
1 reply
James, you did not go wrong, I rather think this is just a misunderstanding. RS and GEN are the specific field names in the German shape files I used as the example for the post. Those are not standard field names in ESRI shape files, i.e. other shape files will have different field names. Tabgehohack's –-info option displays the names of the fields and the values of the first 5 records and thereby helps you to find out which field names you need for the YML-file in step 7. I recommend to read again the steps 6 and 7 of the article. If you are still stuck, you could point me to the shape files you want to use and I will have a look.
1 reply
Rachel, you have to update the declaration of the function ChooseColor and the Private Type myChooseColor at the top of the module in order to get the code working on a 64Bit Office installation. The following threat at StackOverflow explains how to do this: http://stackoverflow.com/questions/5724396/excel-64-bit-and-comdlg32-dll-custom-colours Please be advised that this is only necessary for the color picking feature. If you do not need this part of the code, simply delete the code and the command button on the sheet.
1 reply
Charles, the sub is assigned to the spinner form control. Right click on the spinner, select Assign Macro, select the sub and click Ok.
1 reply
Manuj, the tool does import milestones, too (duration 0 days), no changes necessary from my point of view. However, it highlights all tasks in the Pseudo-Gantt in Excel which have the same start and end date with an orange fill color, i.e. milestones and tasks which have a duration equal or greater than 0 and less or equal one day. If you want to highlight only milestones, you have to change the Conditional Formatting rule of the Gantt-range. The new Conditional Formatting rule would not check if start and end date are the same, it would rather check if the duration of the task is zero.
Toggle Commented Jul 27, 2016 on Bring your tasks in a row at Clearly and Simply
1 reply
Phil, have a look at this workbook: Download 05/06 collapse checked topic stamped (33.9K)
1 reply
Nilrey Jim, I created the dashboard with Tableau Desktop and uploaded it to my Tableau Public account. Tableau Public provides a HTML code snippet which you can directly copy and paste it into your blog or website. Have a look at the dashboard above. At the bottom right of the dashboard you see a little share icon. If you click on this icon, you can copy the link and/or the HTML code. I hope this answers your question.
Toggle Commented Jul 20, 2016 on Word Clouds with Tableau at Clearly and Simply
1 reply
Adam, you can, but you do not necessarily have to change the code. The technique uses the named range "myData" to define the range of the table which shall be sorted. You can replace the fixed range of this name by a formula making it dynamic, e.g. using a combination of OFFSET and COUNTA.
1 reply
Evelyn, after you added new topics and items at the end of the checklist, you have to expand the named range myCheckList to make sure this name covers your entire list. Here is the step-by-step: 1. Make sure your new topics and items follow the required numbering convention in the first column, i.e. a topic has a unique number (e.g. 6), the items the number of the topic followed by a period followed by another number (e.g. 6.1, 6.2, 6.3, etc.). This is crucial, because the first number is used to detect which item belongs to which topic and the period is used to distinguish between topics and items. 2. Go to the Name Manager (on the FORMULA tab of the ribbon or by pressing CTRL-F3) 3. In the upcoming Window click on myCheckList 4. Click on the range selection icon at bottom right of the window, select the entire range of your checklist and press Enter 5. Click on the Close button of the Name Manager and confirm your changes in the next window by clicking on Yes This should do the job.
1 reply
abilashini, you can use the example workbook provided in the article right away. Just follow the steps described in the section "The Preparation of the Workbook". The template requires a dimension to be used as a filter. I suggest you define one of your dimensions as the filter and set the filter to "All". Another option would be to change the VBA code and take out all lines of code managing the filtering.
1 reply
Camille, the maps are a collection of freeform shapes and you cannot assign a text to freeform shapes. Hence, if you want to display additional information, you have to add textboxes, position them where you want them to have and finally write some additional VBA code to update the text in the textboxes. The latter is not necessary for the names of the regions, but it is necessary for the textbox displaying the value (to be updated after the data changed or another measure is selected). Having said that, I would not recommend this, because it will clutter the display and does not add much value. I would rather use tooltips on the map showing the region names and values when hovering over a region (see the follow-up article to this post) or add a sorted bar chart next to the map visualizing the regions and their values.
1 reply
Laurence, I am sorry, but I do not know a site where you can download all historical results of the FIFA World Cups and the UEFA Euro Championships in one go. As mentioned above, I compiled all the data from the FIFA website and if you need the European Championship data, I am sure you will find the data on the website of the UEFA. You have to either compile the data manually or try to use a web scraping tool like importio.
1 reply
Peeush, activating the chart means that you have to select the chart by clicking on it.
1 reply
Alexandra, the additional tab and icons on the ribbon are not created by the VBA code. You can change the ribbon in Excel with the freely available Custom UI Editor. Have a look here for more details: http://www.rondebruin.nl/win/s2/win001.htm
1 reply
Robert, if the sort order of the shapes isn't the problem, you may want to send me your workbook by email (email-address at the top of the blog) and I will have a look. As per your second question: unfortunately, no, I do not have a world map version with filters and tooltips, but it shouldn't be too difficult to replace the US map by a world map in the template I provided. Not much more than an hour or two, I think.
1 reply