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
Interactive Selecting and Highlighting Areas on an Map or an XY Scatter Chart in Microsoft Excel Continue reading
Posted yesterday at Clearly and Simply
Image
Another technique to create interactive charts in Microsoft Excel using an ActiveX Label Control on top of the chart Continue reading
Posted 3 days ago at Clearly and Simply
Maxime, unlike Excel, Tableau does not have a REPT function to repeat texts. However, if the maximum number of repetitions is limited (let's say 5), you can build your own function with a CASE statement and simple string concatenations. Let's say [Text] is the text to repeat and [N] is the parameter defining how often the text shall be repeated. The Calculated Field would look like this: CASE [N] WHEN 1 THEN [Text] WHEN 2 THEN [Text]+[Text] WHEN 3 THEN [Text]+[Text]+[Text] WHEN 4 THEN [Text]+[Text]+[Text]+[Text] WHEN 5 THEN [Text]+[Text]+[Text]+[Text]+[Text] END Agreed, not really an elegant solution, but I do not see another way to do this in a Calculated Field in Tableau.
1 reply
John, it is a bit more complicated in Tableau 8, but it is possible: You have [Count Delimiter] as before; LEN([Name])-LEN(REPLACE([Name],"-","")) You have a second helper CF called [Position second]: IF [Count Delimiter] >= 2 THEN FIND([Name], "-", FIND([Name], "-") + 1) END And another one called [Position third]: IF [Count Delimiter] >= 3 THEN FIND([Name], "-", [Position second]+1) END Then the CFs to be displayed: [First Name]: Left([Name],FIND([Name],"-")-1) [Middle Name]: IF [Count Delimiter] > 2 THEN MID([Name], FIND([Name],"-")+1, [Position second]-FIND([Name],"-")-1) END [Last Name]: IF [Count Delimiter] = 2 THEN MID([Name],FIND([Name],"-")+1,[Position second]-FIND([Name],"-")-1) ELSEIF [Count Delimiter] >= 2 THEN MID([Name], [Position second]+1, [Position third] - [Position second]-1) ELSE RIGHT ([Name], LEN([Name]) - FIND([Name],"-")) END [Namesake]: IF [Count Delimiter] >= 3 THEN MID([Name], [Position third]+1) END I hope this will be helpful.
1 reply
John, I hope you already upgraded to Tableau 9, because since version 9, Tableau provides a FINDNTH function. Let's assume the name field in your database is [Name]. First define a helper Calculated Field called [Count Delimiter]: LEN([Name])-LEN(REPLACE([Name],"-","")) This field helps you identify how many delimiters there are. You do not really need to define this CF, you could also use the formulas within the other CFs, but since you need it more than once, I defined a separate CF. Then create a CF for the first name: Left([Name],FIND([Name],"-")-1) Next extract the second part, i.e. the middle name: IF [Count Delimiter] > 2 THEN MID([Name], FINDNTH([Name],"-",1)+1, FINDNTH([Name],"-",2)-FINDNTH([Name],"-",1)-1) END The formula for the last name is as follows: CASE [Count Delimiter] WHEN 1 THEN RIGHT([Name],LEN([Name])-FIND([Name],"-")) WHEN 2 THEN MID([Name], FIND([Name],"-")+1, FINDNTH([Name],"-",2)-FIND([Name],"-")-1) WHEN 3 THEN MID([Name], FINDNTH([Name],"-",2)+1, FINDNTH([Name],"-",3)-FINDNTH([Name],"-",2)-1) END Finally, the namesake formula: If [Count Delimiter] > 2 THEN MID([Name], FINDNTH([Name],"-",3)+1) END I hope this helps.
1 reply
Scott, you do not have to change anything in the VBA code if you need more topics and items. Simply add topics and items as you like, extend the named range "myChecklist" in the Name Manager to cover your entire checklist and finally make sure you follow the naming convention in the first column (item number = topic number followed by a period followed by the item number). Please have a look at the original article. There is a section called "How to use this template for your own checklists" which describes what you have to do.
1 reply
Raj, yes this is possible. Have a look at my reply to Aileen's comment on July, 5 2010.
1 reply
Elle, I developed the workbook using Excel 2013, but it should work with Excel 2007 and 2010, too. If you replace the IFERROR statements on the dashboard by an IF(ISERROR(…)) construct, it should even work with Excel 2003 and earlier. It doesn't work on a Mac, though. See also my reply to Paul above.
1 reply
Pune, I am not saying they have only disadvantages. What I am saying is that they "…are a great technique for analyzing geographical data…" "…make it very easy to identify geographical hot spots…" and "…if you use Choropleth Maps with caution, they can still be very helpful." However, they do have their limitations, too, and there are uses cases where they are simply misleading as shown in the US Presidential Elections case.
Toggle Commented May 9, 2015 on Cartograms in Tableau at Clearly and Simply
1 reply
Santiago, thanks for your comment. I am sorry, I do not get the idea. The Marimekko Chart is colored by row category, i.e. each row category has the same color, which is necessary because the height of the boxes vary across the columns and they do not have a common baseline. I do not understand what you mean by "change the colors using conditional formatting". Coloring the boxes of a row in different shades depending on their value? I also do not understand what you mean by "adding a Z column". Add a Z column to what (and what would be in this Z column)?
1 reply
Neil, many thanks for your comment and kind words. However, credit where it is due: the background map including its coloring of land and sea is provided by Tableau natively. I didn't do anything for this, Tableau is doing this automatically. Thanks also for the idea regarding Delaunay triangulation and Voronoi charts. I have to think about how this could be useful for my use case, but thanks for the heads up. I will have a closer look. The Excel implementation of Cartograms will 1. use the Dougenik et al VBA implementation to create the distorted polygons 2. then create shape files from the distorted polygons as described here: Create Excel Freeform Shapes from Polygons 3. finally color the map as described here: Faster Choropleth Maps with Microsoft Excel The result will be an Excel model dynamically creating filled Cartograms on user input. The main challenge I am facing is the performance. Creating shapes and coloring the map isn't the issue. The algorithm, however, takes some time and the overall performance is not yet acceptable for a dashboard in production. As I said, I am working on it. My plan is – once I finished my work on the code - to publish a simple tool first providing the Dougenik et al algorithm, i.e. a tool to convert the original polygon data into the Cartogram polygon data (to visualize e.g. in Tableau). The next article would then provide the complete Excel tool, i.e. from original polygons to freeform shape Cartogram maps in Excel with one mouse click. Hopefully coming soon. No promise, though. Finally, I appreciate if you want to share your Cartograms here. You could upload screenshots to your DropBox or any other cloud file hosting service and provide a link here in a comment.
Toggle Commented May 7, 2015 on Cartograms in Tableau at Clearly and Simply
1 reply
Kenji, it is possible, but there is no easy way. You have to combine the code of the 2 checklists into one new workbook. I would recommend you take #9 as the master, look at the code, try to figure out what is different in the code of workbook #12 and integrate those differences in the code of #9. The modules have only 123 (#12) and 168 (#9) lines of code and the code is commented. Once you understood how the code works, it isn't complicated to make the changes. There are countless options of combining the versions posted above and I do not have the time to implement and provide them all. I am sorry. As per your second question: if you need more topics and/or more items, you have to expand the range of the name "myChecklist" and follow the topic / item numbering convention. Have a look at the original article for the details.
1 reply
Neil, ha! Finally! I was already wondering why no one asks for the code… Yes, of course I will publish my implementation of the algorithm. I still have to do a little work on it, like some performance tweaks, cleaning up, commenting, etc. So, I have to ask for your patience, but I am working on it and it shall come soon (hopefully end of this week). By the way: I am also working on an Excel version, i.e. creating Cartograms directly in Microsoft Excel. But one post at a time. So please stay tuned.
Toggle Commented May 5, 2015 on Cartograms in Tableau at Clearly and Simply
1 reply
Eric, I double checked and the original version I posted for download above is still working like charm, also with Excel 2013 and Project 2013. If your version acts up, I would assume it has to do with the changes you made. I can’'t say what the problem is without seeing your version of the code. If you want to, you can send me your workbook by email and I will have a look.
Toggle Commented May 5, 2015 on Bring your tasks in a row at Clearly and Simply
1 reply
Diego, since you are calling the sub from Microsoft Project, ActiveSelection refers to the tasks selected in Project, not the appointments selected in Outlook. I didn't try and I am not an expert in VBA for Outlook, but I guess you have to write some code to get the selected appointments in Outlook (probably using the Explorer.Selection property and / or the Selection.Item and Selection.GetSelection methods) and then process through this selection and import everything into Project. Having said that, from my point of view it would make more sense to write an "Export to Project" sub in Outlook. Outlook is activated, the user selected something and the code would then export the selection to a Microsoft Project file specified by the user.
1 reply
Gabrielle, sure. Just send me an email with a short description of what you need (you find the email link at the top of the blog) and I will have a look and come back to you as soon as possible.
Toggle Commented May 2, 2015 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Image
Plotting preprocessed polygon data as Cartograms in Tableau: the results of the US Presidential Elections since 1900 Continue reading
Posted Apr 29, 2015 at Clearly and Simply
Duc, I knew that you can (and probably should) declare a data type for a constant, but I always thought it would be optional and never encountered any problem if I didn't declare a type. I have no idea why this is necessary in your case. Anyhow, I am glad to hear you found a solution. By the way, I think it should read: Const MARGIN_SHAPE As Single = 0.1 (instead of as Long).
1 reply
Duc, sounds as if there is a problem with the definition of the constant MARGIN_SHAPE. It was set to 0.1 in the template I posted for download. What happens, if you set it to 0 or 1? If you want to, you can also send me your workbook by email and I will have a look.
1 reply
Rob, go to the code and replace the line dblShapeHeight = dblHeight * (varTotalsRows(lngRowCount) / dblTotal) by dblShapeHeight = dblHeight / lngRows for the row headers (step 6 of the algorithm). Do the same for the row total column in step 9 and run the procedure again. This should do the job.
1 reply
Rocio, you have to expand the named range "myChecklist" to cover all topics and items of your checklist. Go to the Name Manager (FORMULAS tab), select myChecklist and expand the "Refers To" range. Furthermore, you have to take care of the "naming convention" in the first column. The items are "numbered" by the topic number followed by a period followed by the number of the item. See the section "How to use this template for your own checklists" of the article. As per your last sentence: I am sorry, I do not understand what you mean by "to add the user is not applying".
1 reply
Cristian, you are right, if you need a one-off solution to create freeform shapes from polygon data, QGIS is probably the easier way. What I needed was a VBA routine in a stand-alone Excel workbook doing this again and again on user input. That's why I created the workbook published above.
1 reply
Cristian, I am not claiming Excel would be the best tool to do it. However, I need the routine as a part of a another Excel project I am working on. Using another tool was not an option.
1 reply
Image
How to transfer polygon data into freeform shapes in Microsoft Excel Continue reading
Posted Apr 17, 2015 at Clearly and Simply
Tara, sure, with some extra VBA code you can export the checklist or parts of the checklist to Word, PowerPoint or another application. Here is a link to get you started: MSDN Export from Excel to Word The code snippet provided there exports an entire range to Word. You could build upon that and insert a loop through the items of the checklist and a condition to export only if the item is checked.
1 reply