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
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 6 days ago 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, 2) 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
Brian, 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 Microsoft's Internet Explorer or Edge, the IE/Edge will change the file extension from .xlsx to .zip during download. Simply change the file extension back to .xlsx and you can open the workbook with Excel by double clicking on the file.
1 reply
Matt, many thanks for your comment and your kind words. Since you are an expert on Bullet Graphs, I am very honored and happy to hear you like it. With regards to the problematic edge cases: yes, I had issues similar to the ones you are describing and I developed some more or less helpful techniques to get around these problems. As for your first example, I am usually defining a minimum gap (either in absolute numbers or in percentage of target) and display the number and span only, if the gap exceeds this minimum. The reasoning behind that: metrics with relatively small deviations from target are less important for the analysis and you can usually live without the gap information for those metrics. In an implementation in Excel, this approach requires the feature of linking data labels to defined cell ranges (available in Excel 2013 and 2016 only), because you can't simply use the data label of the invisible bar anymore. I do have templates in my toolbox for this, but I intentionally did not mention this in the post. I wanted to keep the workbook clear and simple and to avoid an over-engineering of the described approach. As for your second example: you can change the position of the label to inside end, but this leads to other problems, e.g. the metric label may obscure the target label (in my example this is the case for Category 8). Moreover, I prefer to have the metric labels aligned, because this makes the chart less "busy" than inside end or center. Truth be told, this isn't a real problem on most of my dashboards, because I am usually combining worksheet cells and embedded charts. In my dashboards, the list of categories is shown in a cell range. In the column right to this range, the actuals are displayed (also in cells) and right to that the chart is positioned. The chart does not have labels on the vertical axis and no data labels for the metric. It is then resized and repositioned that every bar sits exactly on top of a worksheet row. This way, the labels of the metrics never interfere with other elements of the chart.
1 reply
Robert, maybe it was unfair to call it cryptic, because it is documented. However, it still is a DOS command you have to enter in the command prompt and this procedure is everything else than user-friendly for the not unusual request of producing a list of all files inside a folder and its subfolders. What do you mean by "the copy from the ribbon", by the way?
1 reply
Image
An alternative design of Bullet Graphs: no qualitative ranges, additional data labels and a visualization of the performance gap (if applicable) Continue reading
Posted May 8, 2017 at Clearly and Simply
10
George, I just sent you an email with a map of Nigeria.
1 reply
Image
Options for displaying a Progress Indicator in Microsoft Excel during time consuming VBA procedures and a Speed Comparison of the different approaches Continue reading
Posted May 2, 2017 at Clearly and Simply
Image
What is the fastest way to read values from and write values to an Excel worksheet with VBA? And how much faster is it? Continue reading
Posted Apr 28, 2017 at Clearly and Simply
Image
Add context and interactivity to your Excel Dashboards with mouse hovering and clicking actions Continue reading
Posted Apr 25, 2017 at Clearly and Simply
Sharaz, this is possible. Here are the steps you have to do: 1. Go to the VBE, cut (or copy and delete) the entire code from the worksheet module 2. Insert a new module 3. Insert the code you copied in step 1 into the new module 4. Change the name of the sub, i.e. replace Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) by e.g. Sub FilterData() 5. Go to the worksheet, click on the Developer tab and on Macros. In the upcoming dialogue window, select FilterData, click on Options and define the shortcut you like. Since CTRL-F is taken already by Excel (Search), you would have to use e.g. CTRL-Shift-F (or whatever you choose).
1 reply
reza, if you refer with "pso" to a Particle Swarm Optimization algorithm, please have a look at this Excel model provided on sourceforge.net: Excel VBA Particle Swarm Optimization The VBA code is free (no password protection), so I think this might get you started.
1 reply
Image
A ready-to-use template for an S-shaped Function in Excel Continue reading
Posted Apr 24, 2017 at Clearly and Simply
Image
How to create a customizable S-shaped Function in Tableau Continue reading
Posted Apr 23, 2017 at Clearly and Simply