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
Edouard, you can use the color code approach for the multiple rows Bullet Graphs, too. The technique is along the same lines as in variation 5. You need three data series for the actual instead of only one, assign the value (if appicaple) or #NA (if not applicable) and format the three series in red, yellow, green. Check out the implementation of variation 5 and you will certainly figure out how to apply this to the multiple rows bullet graph.
1 reply
Aman, I do not think this is possible in Tableau. Calling a VBA macro would mean you open an application like Excel from Tableau (e.g. with an action) and the code would be executed. I do not see how this would be possible in Tableau. Doesn't mean it is impossible, though. It is just above my head. Maybe you want to post your question on the Tableau Forum?
1 reply
Image
Variations of the Alternative Bullet Graph Design: Visualization of Gaps and Exceedances, two Targets and two Gaps, conditionally formatted Actuals and dynamically sorted Multiple Rows Bullet Graphs Continue reading
Posted 6 days ago at Clearly and Simply
Larry, absolutely. I can think of several solutions: One way would be to change all subs/functions to check if the filetype (objFile.Type) equals the desired filetype. Probably the most elegant and fastest approach, but you have to adjust all functions and some of them would become more complicated (e.g. CountFiles). Another solution would be to change only the main sub: first let the code create a list of all files (as it is now), define another VBA array, loop through the array with all files and transfer only those to the new array which have the desired filetype. Finally write this new array to the worksheet. Probably a bit slower than the first approach, but easier to implement, I guess.
1 reply
s, in general, there are two main sources for a URL-link in Tableau: you either have a dimension with the according links in your data source or you have a Calculated Field which creates the URL-links from other dimensions and measures. In both cases, deleting a dashboard or worksheet with an URL-action will not delete the links. Tableau never deletes source data or Calculated Fields when deleting worksheets, dashboards or storyboards. Having said that, if you want to use the same URL-action on another dashboard or worksheet, you have to insert the it again.
Toggle Commented Jun 29, 2017 on The Power of Tableau Actions at Clearly and Simply
1 reply
Robert, yes, thematic or Choropleth Maps by ZIP-code can be very useful to visualize geographical data, too, especially in corporate environments. Unfortunately I do not have a US map by zip codes in Excel, but I do have Excel postcode maps for a few European countries.
1 reply
Image
Another option to display a Choropleth Map of the United States by County for one user-selected state in Microsoft Excel Continue reading
Posted Jun 21, 2017 at Clearly and Simply
Chris, the templates work well with later versions of Excel, too (Excel 2010, 2013 and 2016 for Windows in my case). The VBA does not use any Windows specific functions, so I also do not see why the code should not work with Excel for Mac. I can't test this, though, because I do not have a Mac available. Does the problem exist with the original template I posted for download or did you transfer the code to your own workbook? If the latter is the case, are you sure you defined the named ranges correctly ([myChartArea], [myData], [myLabels], [myColor-Scheme])?
1 reply
Leonid, many thanks for your comment and your kind words. You are probably right: there may be even more ideas regarding Choropleth Maps in Excel I haven't thought of yet. I really like your idea of combining option 2 and 3 in one view. I will put this on my list of potential future blog posts. No promise I will ever publish it, though. But I will keep it in mind. Regarding the topmost image: I wrote a small VBA routine (in the option 3 workbook) to loop through all the states and to export the maps to PNG files. I then imported all files into PowerPoint, arranged them, grouped the images and exported the group as a picture. Sounds like a lot of work, but actually didn't take more than 30 minutes.
1 reply
Image
How to display a Choropleth Map of the United States by County for one user-selected state Continue reading
Posted Jun 19, 2017 at Clearly and Simply
Image
How to implement Highlight Actions in Microsoft Excel with the option to select more than one category (Multiselecting) Continue reading
Posted Jun 18, 2017 at Clearly and Simply
Image
How to implement a specific Highlight Action Use Case in Microsoft Excel Continue reading
Posted Jun 13, 2017 at Clearly and Simply
David, first of all, thank you very much for sharing your work. Much appreciated. I now had a closer look at your workbook. So, you are using 2 annotations with no line, no fill and an arrow as the line end to visualize the span line and an additional sheet to show the data label above the target on a worksheet. Interesting ideas. It looks great in the view you posted and it is definitely closer to the Excel version than the Tableau approaches I posted. I see two disadvantages, though: 1. It takes quite some extra effort to insert, position and format the annotations and to set up and align the views on the dashboard. 2. Second disadvantage – and this is the major drawback in my humble opinion – the solution is static and the annotations have to be manually adjusted when the data (actuals and/or targets) changes or if a filter is used and another category or month is selected. So, it looks great and is closer to what I had in mind, but it is static and therefore probably not applicable to most dynamic business dashboards like monthly reports or analytical dashboards with filters. But you already knew all that, because you said it wouldn’t be ideal… Anyway, many thanks again for your ideas and contributions!
1 reply
David, many thanks for your speedy reply. I can only speak for myself and I do neither know if many Tableau users are still reading my blog nor if people really like the bullet graph variations I suggested. In any case, I would definitely be very interested in seeing how you solve the challenge, even if it is (as you said) not ideal. If it isn't too much effort, I would greatly appreciate if you could share a sanitized version. Thank you.
1 reply
David, many thanks for your comment and contribution. Very interesting! This looks great. I assume you worked with additional Calculated Fields and Custom Shapes? Would you mind sharing your workbook, e.g. on Tableau Public? This would be greatly appreciated.
1 reply
Allan, no offense, but "having limited success" is not detailed enough to help you. You have to be more specific about what you are trying to do, what you have changed and what does not work.
1 reply
Jeffrey, I do not think this is a problem caused by the code change. I’d rather assume it is a problem with the numbering of topics and items in the first column: the number of the topic followed by a period and the number of the checklist item. The code uses this first column to identify what is a topic and what is an item and which item belongs to which topic. I assume you may not have followed this convention in your checklist. Another possible root cause for your issue is the definition of the named range myCheckList. Are you sure the named range covers the entire range of your checklist? You can also send me your workbook by email and I will have a look.
1 reply
Image
How to realize an alternative Design of Bullet Graphs in Tableau Continue reading
Posted May 23, 2017 at Clearly and Simply
Jeffrey, the code reads the entire checklist into a VBA array and makes the user-triggered changes in this array. For performance reasons, the code then writes the entire array back to the worksheet in one go and thereby overwrites the formulas by values. If you want to keep formulas in your checklist, you have to change the VBA code to only write back the column(s) with the checkboxes: Replace Range("myCheckList") = varData by something like this: Dim i As Integer For i = 1 To UBound(varData, 1) Range("myCheckList").Cells(i, UBound(varData,2)) = varData(i, UBound(varData,2) Next i You have to do this in 2 subs (ChangeTopicStatus and AutomaticSetTopicStatus) This way, the code writes back only the last column of the list, i.e. the checkboxes. The columns left to the checkboxes stay unchanged, i.e. formulas or links inside those cells are retained.
1 reply
Reza, I am sorry, I am not working for the company which developed and sells XL Optimizer. I'd suggest you directly contact Technologismiki.
1 reply
Claude, many thanks for your comment and the link. I haven't seen this. Thanks for the heads-up. Interesting. Actually, I think Rolf's and Juergen's points are not so far from my suggestions: 1. They are taking out the background color at least of the last background range. As I wrote in the article, I am not using the background ranges at all, because there is usually no common understanding of the ranges. And it seems as if Rolf and Juergen would concur with that. At the end of the article they write "…we strongly recommend to waive those ranges…" 2. They add data labels to the target and the gap, just like I am suggesting 3. They add a red bar to visualize the gap, whereas I am visualizing it with a thin line and arrows. They also add a visualization for the exceedance values, but this would be possible with my approach, too (and I will soon show how this would look like). I would still prefer my version, though, because you do not really need the colored bar. The fact that it is a gap or an exceedance is already visualized by the value bar and the target line. All you really need is a graphical element setting the data label of the gap / exceedance into context. In my humble opinion, the thin line with arrows is doing this job with a higher data/ink ratio. Finally, I fully agree with Rolf and Juergen regarding the normalization of the targets, but this does not apply to the examples I used in my article.
1 reply
Marko, this is possible. You would have to add a For Next loop to the end of the code (after the data was inserted into the table), looping through all rows in the table and use the Hyperlinks.Add method to add the value of the second column (the path) as the hyperlink to the cells. Have a look here how the .Add method works: Hyperlinks.Add Method (Excel)
1 reply
Image
An interactive Multiple Row Bullet Graph in Excel displaying the gap and data labels only when hovering over a bar with the mouse Continue reading
Posted May 12, 2017 at Clearly and Simply
Nick, I am sorry, I do not understand your question. What do you mean by "correct headers"?
1 reply
keratl, thanks for your comment. I agree, with long running subs, Excel sometimes loses focus and maybe even states that it is not responding at the top of the application window. This is an annoyance and the DoEvents statement often helps, but not always. That is one reason why I am recommending the good old user form for progress updates. Regarding the MOD-function you mentioned: I am actually covering this option in the article and workbook. My tests (see above) showed that the overhead of updating every iteration of 1,000 iterations is comparable to the overhead of updating every 10th of 10,000 iterations. If you want to do your own tests, please download the workbook and play around with the number of iterations and the update interval.
1 reply