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
Fuzzyguzzy, yes, that's what this post is about: embedding a Tableau Packaged Workbook in PowerPoint and opening the workbook with Tableau Desktop during a PPT slide show.
1 reply
Frank, yes and no. You are right, some statistics are not detected correctly if worksheets are protected, but only the metrics which are calculated using the .SpecialCells property, i.e. blank, constant, formula cells and the cells with conditional format and data validation. All other metrics are including the protected worksheets, too. If you know for sure the sheets are not protected with a password, you would add the following lines of code to include the protected sheets: Define a Boolean variable to store the current protection status: Dim blnProtected As Boolean Inside the loop through all worksheets, add these lines at the beginning of the code: blnProtected = .Protection If blnProtected Then .Unprotect and this one at the end: If blnProtected Then .Protect This should do the job. Although, as already mentioned above, this will not work with password protected sheets.
1 reply
Frank, Roberto Mensa just provided me with an idea what your issue may be (many thanks, Roberto): Open Excel and go to File | Options | Trust Center | Trust Center Settings | Macro Settings. In this window activate the check box next to "Trust access to the VBA project object model". Then try again to run the macro. Does this solve your issue?
1 reply
Frank, I developed and tested the workbook with Excel 2016 only, but I do not see why it should not work with 2013 or 2010. Go to the VBE, comment out or delete the line On Error Resume Next and try to run the macro again. If an error message is displayed, click on debug and let me know at which line the code stopped.
1 reply
Image
A small VBA code snippet to display the most important metrics of an Excel workbook like the count of sheets, cells, formulas, constants, named ranges, charts, tables, lines of code and many more Continue reading
Posted Aug 6, 2017 at Clearly and Simply
Alex, have a look at this workbook: Download 05 check list stamped right (33.3K)
1 reply
Joe, good point. You are absolutely right. I never thought of checking the start dates of the holidays. It should have been obvious especially for Martin Luther King Jr. Day, though. Thanks for pointing this out. Actually, there are more and similar inaccuracies in my visualization, like the observation of Presidents’ Day was shifted from February 22nd to the third Monday in February in 1968 and the observation of Veterans Day started in 1938. However, fixing these issues isn’t a big deal. For the data blending approach, you would simply change the according entries in the Excel data source, e.g. set the third Monday in January to a workday for all years before 1986. For the Calculated Fields approach, you would only have to wrap up the formula in an IF-Clause like // Martin Luther King Jr. Day = 3rd Monday of January IF Year([Date]) >=1986 THEN DATETIME(STR(YEAR([Date]))+"-1-15")+ IF DATEPART('weekday', DATE(STR(YEAR([Date]))+"-1-1")) > 2 THEN 9 - DATEPART('weekday', DATE(STR(YEAR([Date]))+"-1-1")) ELSE 2 - DATEPART('weekday', DATE(STR(YEAR([Date]))+"-1-1")) END END
Toggle Commented Jul 30, 2017 on US Public Holidays in Tableau at Clearly and Simply
1 reply
Stanely, this is possible. Simply define a matrix with 10 rows and 52 columns somewhere on sheet [control] and define individual thresholds for USA total, the 50 states and DC in this matrix. Then replace the hard coded percentage thresholds on sheet [control] in range B92:B101 by INDEX formulas based on the cell called mySelectedState to retrieve the thresholds of the selected state in the previously defined matrix. Finally you have to make sure the sub for coloring the map is also called when the user changes the state (which isn’t the case in the implementation I posted for download). Therefore go to the VBE and call the sub to update the full map at the beginning of the sub UpdateStateMap.
1 reply
Govardhan, your request sounds easier than it is. The code in my tool uses the VBA File Object. What you are referring to (author and owner) are Microsoft Office built-in document properties. That means, the VBA File Object does not have these properties and you can't easily add them to the list the tool provides as an output. To meet your requirement, you would have to considerably enhance the code to check if the currently processed file is a Microsoft Office file (Excel workbook, Word document, etc.), if so, read the required attributes from the built-in document properties and add them to the table. Not impossible, but quite some extra coding necessary.
1 reply
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 Jul 17, 2017 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