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
Hrushikesh, Tableau’s URL action simply allows to pass a URL to the web page object on the dashboard. You can create a specified URL based on data from your data source in a Calculated Field just like I have shown in most of the examples in the article. If you want to open a web page which requires user credentials, you would need the name of the user (the function USERNAME() returns the name of the current user) and the password. Even if you would have the password(s) in the data source or hard-code it in the Calculated Field, I doubt there will be a web application allowing to pass the username and the password in a URL. And for very good reason, because this would be a serious security issue.
1 reply
Juan, I would recommend to use one of the approaches taking advantage of the chart mouse move event (techniques 1 and 2 described above). They work with logarithmic scaled axes as they are. No changes of the VBA necessary.
1 reply
Honey, In general, this would be possible if you add an ActiveX Webbrowser Object to each of the sheets and pass the URLs of the Tableau dashboards to the corresponding webbrowsers. Having said that, please be advised that the technique described above is outdated, because with Office 2013 Microsoft disabled scriptable ActiveX controls embedded in PowerPoint slides or Excel worksheets. I.e. the technique described here only works for Office 2010 or earlier. The following post describes a way to open Tableau directly from a PowerPoint slide show: Embed and Open Tableau Workbooks in PowerPoint Maybe this could be an alternative option.
1 reply
Ofthetone, agreed, many variations are possible based on the template provided above. The workbook and VBA code is without password protection and you are welcome to adjust the code as you like. If you want to change the coloring, simply change the section "Color the shapes" of Step 7 of the algorithm according to your requirements.
1 reply
Gowtham, you have to create one Calculated Field for each possible entry (the names of the programming languages in your example). So, you would finally have one Calculated Field called e.g. [C Count], one called [C++ Count], one called [Java Count], etc. There is more than one way to skin the cat. If you know for sure that a programming language occurs maximum one time in each data row of [Technologies], you can use this formula: INT(CONTAINS([Technoligies], "C++")) CONTAINS checks if the field [Technologies] contains the substring "C++" and returns TRUE if it does and FALSE otherwise. INT then converts the Boolean value to a number. If it is possible that a programming language occurs more than once (e.g. "C, C++, Java, C++"), you would use the formula [24 – Count substrings] described in the article above. For both approaches, SUM([C++ Count]]) is then the number of occurrences of "C++" in the [Technologies] column.
Toggle Commented Oct 25, 2017 on String Calculations in Tableau at Clearly and Simply
1 reply
Sven, you are absolutely right, regions without data should be shown in a different color instead of visualizing them as if the value would be zero. I recognized this, too, but in my defense, you are referring to an article which is already more than 8 years old. Have a look at the first paragraph of this more up-to-date article and at the work-book provided for download there: http://www.clearlyandsimply.com/clearly_and_simply/2017/06/us-choropleth-map-by-county-per-state.html Please be advised that this workbook is using an improved version of the Choropleth Map algorithm described here: http://www.clearlyandsimply.com/clearly_and_simply/2012/09/faster-choropleth-maps-with-microsoft-excel.html I hope this will be helpful.
Toggle Commented Oct 13, 2017 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Anne, have a look at this workbook with a small variation of the technique: tooltips_on_xy_scatter_charts_multiple_data_points (60.5K) The tooltip shows up to maximum 5 data points with the same X and X values. A few extra formulas on the [data] and [tooltip] sheet, but no changes of the VBA code. I hope this will be helpful.
1 reply
Angela, the code I am using in my tool is not changing or projecting the polygon data, but rather simply creates freeform shapes according the X and Y values on the data sheet using the .AddPolyline method of the Shapes object in VBA. If the polygon data created by MapInfo results in a distorted freeform shape in Excel, I would assume that MapInfo e.g. exports the polygon data without the Mercator Projec-tion (https://en.wikipedia.org/wiki/Mercator_projection). I can't tell you for sure, though, this is just a wild guess. In any case, it has nothing to do with my code, because the code simply and mindlessly draws a polygon of the values on the data sheet.
1 reply
tj33, as is, the workbook with a Choropleth Map in Excel only works with one map. However, it is possible to use the approach to also color several maps on several worksheets in one workbook. You have to adjust and enhance quite a few things in the workbook and in the code, though: You need multiple instances for the ranges of values, shape names, shape index, legend and the range mapping values to colors. Furthermore you have to adjust the code and call the UpdateChoroplethMap sub with the according parameter / ranges for each map. A little bit of work to do, but not impossible.
1 reply
Annie, I assume you are workink on a Mac? If so, please refer to my response to Catherine above.
1 reply
Iz, a polygon data set usually includes the X and Y coordinates (in most cases longitude and latitude) and a point order ID. The point order is crucial. If it is missing, the data simply provides the coordinates of the data points, but no information how they form a polygon. If your data set lacks this point order information, I do not see a way how you could generate or calculate it simply based on the X and Y data points. How should that be possible? Using an algorithm like a simple Nearest Neighbor? This wouldn't work, because very often, the next point in the point order of a polygon is not the nearest neighbor. I am sorry, but I do not see a way how this could be solved. I assume you have to look for a polygon data set including the point order information.
1 reply
Jay, here is a generic template only providing the mouse hovering feature: Download mouse_hovering_generic_template.xlsm (44.4K) Maybe you can build your own solution based upon this template?
1 reply
Poorna, the following formula works even if the filename has more than one period: RIGHT([Filename], LEN([Filename]) - FINDNTH([Filename], ".", LEN([Filename])-LEN(REPLACE([Filename],".","")))) The formula uses FINDNTH, which requires Tableau version 9 or later.
Toggle Commented Sep 30, 2017 on String Calculations in Tableau at Clearly and Simply
1 reply
Poorna, have a look at the Calculated Field "31 – Extract last part" described above. You just have to adjust the name of the dimension and replace the blank at the end by a period, like this: RIGHT([Filename], LEN([Filename])-FIND([Filename],".")) Please be advised that this formula will only work if there is only one period in the filename, i.e. the one seperating the filename from the file extension.
Toggle Commented Sep 30, 2017 on String Calculations in Tableau at Clearly and Simply
1 reply
Chin, you can use the function DATENAME to display the desired values. The Calculated Field Hierarchy 1 (Quarter) would then look like this: CASE [Hierarchy Chooser] WHEN 'Region ► Customer State ► Zip Code' THEN [Region] WHEN 'Category ► Sub-Category ► Product Name' THEN [Product Category] WHEN 'Quarter ► Month ► Week' THEN "Q"+ DATENAME('quarter',[Order Date]) END Hierarchy 2 (month): … WHEN 'Quarter ► Month ► Week' THEN DATENAME('month',[Order Date]) … Hierarchy 3 (wwek): … WHEN 'Quarter ► Month ► Week' THEN DATENAME('week',[Order Date]) …
1 reply
Brian, runtime error 424 usually means that the code refers to an object without providing a valid object qualifier. This can be the case if you e.g. refer to a named range of the workbook, but the name does not exist (or is not spelled correctly). Now, if the code stops at the line (as it did in Chris’ case) CreateMarimekko [myChartArea], [myData], [myColorScheme], 2 and states a 424 error, the only root cause I can think of is one of the named ranges passed to the sub CreateMarimekko does not exist. Of course, this is not the case in the workbook I provided for download and I can’t reproduce your issue. I just downloaded the workbook again and it is working like a charm for me. Are you sure you did not delete or rename a named range?
1 reply
Ulrik, many thanks for your comment. Great idea. As you said, not as elegant as the Excel solution, but I definitely like your version better than my two approaches. In case you haven’t seen it already: I recommend to have a look at Dave Cately’s solution provided in a comment above. Dave’s approach isn’t easy to implement and has a few drawbacks, but it is very close to the Excel version. Many thanks again to Dave and you for sharing your great alternatives.
1 reply
Leroy, 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 .xlsm to .zip during download. Simply change the file extension back to .xlsm and you can open the workbook with Excel by double clicking on the file.
1 reply
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