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
Brandon, I guess you are talking about the simple version (hard-coded string constant) and you copied and pasted the code from the text above and did not download the PowerPoint files provided at the end of the post. In this case you are right: the quotation marks in the text are curly instead of straight. I assume, my blog editor changed the quotation marks automatically and I didn't notice. A quick look into the files provided for download would have probably saved you some time. Anyway: sorry for the confusion.
1 reply
Marc, I am sorry, I do not know a way to access the metadata / county names from a WMF file. This does not mean it is impossible, it is just above my head. I can think of the following 3 options: 1. Assign the shape names manually. A lot of work for 83 counties, but you have to do this only once. 2. D-Maps offers also SVG files for download and I tried, but those files do not contain the names of the counties. With a quick research on Wikimedia Commons I found a map of Michigan by counties in SVG file format which includes the county names: Map of Michigan 3. Final option would be to download the map USA by counties provided in the article Faster Choropleth Maps with Excel and delete everything except Michigan. I guess, this would be the easiest and fastest way.
1 reply
Jasmina, you have to ungroup the shapes first and delete any freeforms except for the regions, e.g. the labels and the frame around the shapes before you run the macro (see step 3 above). I will send you the workbook in a minute.
1 reply
Lucas, thanks for your comment and your kind words. I had a quick look. It seems to be a data problem. Compare the values of Lat and Long in your data with Latitude (generated) and Longitude (generated) Tableau provides with the geographical dimension "Zip". Some are the same, some are different. You are using filled maps and for filled maps Tableau uses the geographical information provided in Latitude (generated) and Longitude (generated). The calculated field distance, however, uses Lat and Long from your database. I am not sure, but I guess this is the root cause of the issue.
1 reply
Jasmina, it depends on the SVG file you are using, i.e. the names of the regions will not always be in column id3. In your case, the names are in column ns1:tspan (column AG after you opened the SVG with Excel). I will send you the workbook by email in a minute. Please be advised that sometimes the SVG file does not contain the names at all. As I said, it depends on the SVG file you are using. Having said that: in your case with only 12 regions on the map, you would probably be faster if you rename the freeform shapes in Excel manually. The tutorial above was meant for a map with a couple of hundreds or even thousands of regions like the USA by county.
1 reply
Guillaume, I think Richard answered this question in his VizTalk thread. Have a look here: VizTalk Thread TabGeoHack Comments 146-147 The key is to include the latitudes and longitudes of your points of interests in the custom geocoding even though you do not have and do not need shapes for them. Just create a CSV file with the latitudes and longitudes of the POIs and copy it to the folder "Custom Geocoding Files" after step 8 and before step 9. This should do the trick. I did not try, so no promise, though.
1 reply
Mary, I agree, coloring charts can be a very tedious task. The most flexible way of coloring charts is probably writing your own VBA routine. As always, VBA gives you full control over what you want to do and how you want to do it. However, it requires VBA knowledge and writing and testing the routine require some one-time extra effort and time. Having said that, there are a few options how to make coloring charts faster without VBA. Here are some ideas: 1. Use the chart style templates. You can add your own chart style template by formatting a chart and then right click on it and save it as a template. You can then apply the same formatting to every new chart with only 2 or 3 mouse clicks 2. If you have Excel 2010 or 2013, you can also use the predfined color scales (Change Colors on the Design tab) 3. You can also select an existing chart (which already looks the way you want it to look), copy it, select a new chart and transfer the formatting by clicking Paste|Paste Special|Formats. I guess there are many more tricks for chart formatting, but I think these three should already save some time.
1 reply
Marc, hmm, the error message looks as if the "Local Data" folder in your Tableau Repository would be missing, but you already checked and the .FDB and .TDS files are there (although there are usually more .TDS files if you purge roles). Can you send me an email with your YML file and a link where you got the shapes from? I will then try it on my machine.
1 reply
Laurant, as for your "secret second goal": if you want to have a bubble chart on top of a choropleth map, you would need an Excel chart sitting perfectly aligned on top of the shapes and the fast algorithm wouldn't help you, because it just updates the shape colors. The bubbles would be an Excel chart and they would be updated by Excel itself. As for the 2 level of details map: I agree, you can keep the maps on separate worksheets, but you would not have to delete and copy the map. As I suggested in my reply to your first comment, you could use the camera object to display the map selected by the user. Of course, you could also use some VBA to delete and copy the shapes, but I think the camera object would probably be the easier way to go.
1 reply
Mike, I can't reproduce this. It is perfectly aligned in the version I posted for download. Having said that, there is the issue in Excel 2003 described in the article and the extra Excel 2003 workbook with the workaround. Maybe you are using Excel 2003, but you downloaded the Excel 2007/2010 version?
1 reply
Laurant, 1. Performance You are absolutely right: for a world map with ca. 200 shapes, performance is no problem. You do not even need the faster algorithm described in this and the previous blog post. Tushar's original algorithm is fast enough to update a world map in a reasonable time. Have a look at my first post on Choropleth Maps here: Choropleth Maps with Excel 2. Different level of details / zooming You can change the workbook and VBA to display a user selected part of the region by making the relevant shapes visible or invisible. This shouldn't be a big deal. However, if you do not zoom, you will have an ugly effect: the shapes are always in the same position on your worksheet, i.e. if you select South America, for one, you will see South America in the bottom left of the map and a lot of white space above and to the right of it. 3. Additional information by clicking on a shape Have a look at this article: Spice up your Choropleth Maps with Excel The details of a region are shown in a message box after cklicking on the shape, but you could easily change this and display the metrics in a cell somewhere on your worksheet or – as you suggested – show a triangle next to the legend.
1 reply
Laurent, thanks for your comment. It is possible to display different level of details of the map (i.e. state and county) by using different maps on top of each other and quickly let the user toggle from one level to the other by changing the visibility of the map shapes via VBA. Actually, I do have a template for this already. It is still on my list of blog posts waiting to be finalized and published (hopefully soon). Having said that, it would not really speed up performance if you change the metric to be displayed or the color scale. On the contrary: both maps have to be updated (in order to allow a quick toggling from one level to the other) and this will need even more time, As per your second question: zooming in and out of a map is more complex. It is not impossible, though. One idea would be to have all maps (World, North America, South America, etc.) on a different sheet and show the selected map using the camera object. There are some disadvantages coming with this, of course: it requires a lot of manual set-up for each new map, you have to align the sizes of all maps, you would lose the tooltips, etc. As I said, it is not impossible, but it requires a lot of manual set-up.
1 reply
R, LineX, LineY and CircleY are measures in the data source. Have a look at the Excel workbook provided in the article (in the section Data Preparation). Please have also a look at Michael's comment right above yours.
1 reply
Gajanan, it is hard to tell what's going wrong from the text of the YML file pasted into the comment section here. Can you post your YML file somewhere for download? May I also ask why you are doing this for the US? Tableau provides filled maps for all kind of regions of the US natively (states, counties, ZIP-code areas). You do not need TabGeoHack for a US map. Or is it just for testing the technique?
1 reply
Gregory, I never tried to edit the shape files I am using, but you may want to have a look at a free GIS editor like QGis or OpenJump and see if they can help you. As I said, I never tried, so no promise.
1 reply
Gregory, root cause of this issue are indeed the special characters with accents (e.g. é). I am having the same problem with German "Umlaute" (ä, ö, ü). In my example map of Germany shown above, I am not using the names of the regions (Kreise) as the geographic role, but the ID of the regions (Kreisschlüssel). If there is something similar for the Belgian districts, I suggest you import this into your custom geocoding, too and use it to assign the geographic role in your Tableau workbook. Otherwise, you have to create your own ID and map it to your data source, see also step 11 of the tutorial. I can't tell you why Saint-Nicolas isn't working. Hyphens shouldn't be a problem. Maybe the district is called Sint-Niklaas either in the shape files or in your source data? As I said, I have no clue, this is just a wild guess.
1 reply
Yoh, are you sure you enabled the macros? This is the only issue I can think of. Thousands of people downloaded this file and had no problem with it. I guess your security level is set to "disable all macros without warning" and without enabling macros the whole check list can't work.
1 reply
John, absolutely no need to apologize. This article has 138 comments so far and it is very easy to overlook something in this avalanche of messages. Truth be told, I had to search for several minutes, too, before I found the question of Bee and my example timestamp workbook. So, no worries please.
1 reply
Emily, it depends. If you know the HTML string providing the user name and the password to directly open the page, it might be possible. Not really sure, though, I never tried.
1 reply
John, thanks for your comment and question. Actually, this question has been asked before and my answer and example workbook are buried in the avalanche of comments on this post. Look out for Bee's question (June 21, 2013, 12:53). In my reply to Bee, I am providing a download link to a workbook, which should exactly do what you are looking for.
1 reply
Robert, hard to say without seeing the workbook(s) / data. If you can send me your workbook and list of names by email, I will have a look at it and provide my 2 cents. You'll find "Email Me" links at the top or in the left column of the blog.
1 reply
Alex, what is wrong with d-maps? They provide tons of different maps for free and most of them are already in WMF or EMF, so you can directly use them in Excel without having to transfer them as described above. Anyway, sure, I am interested in seeing your maps.
1 reply
Alex, d-maps.com provides a map of France by regions in different formats: http://d-maps.com/pays.php?num_pay=240&lang=en Wikimedia Commons has a map of the World by continents only, i.e. without state borders in SVG format: http://commons.wikimedia.org/wiki/File:Europe_location_blue.svg You can group shapes manually in Excel (select the shapes and click on Group on the Format Tab) or you can group shapes using VBA with the Group method of the ShapeRange collection.
1 reply
.ReminderSet = False
1 reply
Movie, thanks for the clarification. True, German Umlaute and other additional characters are not included. For this, the file would have to be in UTF-8 format, but unfortunately, I do not have the database in UTF-8 (as mentioned in my reply to Jiri's comment above).
Toggle Commented Jan 17, 2014 on Geocoding Databases for Europe at Clearly and Simply
1 reply