Clearly and Simply

Drill Up and Down on Choropleth Maps in Excel

Interactive drill up and down geographical hierarchies on a Choropleth Map in Microsoft Excel

The post Faster Choropleth Maps with Microsoft Excel provided a faster version to update a Choropleth Map in Microsoft Excel. The approach made it possible to use Choropleth Maps with several thousand regions on an interactive Microsoft Excel dashboard in production.

This also opened up new possibilities to enhance the maps with additional features. Leonid Koyfman contributed a couple of great enhancements in the follow-up article Fast Choropleth Map with Enhanced Features like filtering the data by value bin, showing tooltips and letting the user decide whether the map shall be colored by state or by county.

Very soon after this follow-up article was published, Leonid came up with another great idea. He suggested to take the user selection of how to color the map to the next level: let the user easily drill up and down the geographical hierarchy by simply clicking on the map. One click toggles from coloring the entire state to the counties in that state and vice versa. I have to admit, I am sitting on this nugget for one and half years already and never found (well, more precisely never took) the time to publish it. But finally the time has come. Here it is.

Today’s article explains Leonid’s idea and implementation how to drill up and down geographical hierarchies on a Microsoft Excel Choropleth Map.

The article includes two example workbooks for free download: the USA by states and counties and Germany by the two common ZIP-code levels PLZ2 and PLZ5 (first two digits of the ZIP-code and the entire five digits ZIP-code).

String Calculations in Tableau

Concatenation, Conversion, Analysis and Extraction -
44 Formulas to work with Strings in Tableau’s Calculated Fields

“String Calculations” is a somehow weird expression. Calculations on texts sounds like a contradiction in terms.

Of course you do not really calculate strings. You manipulate and analyze them like concatenating texts, changing texts (e.g. to upper, lower or proper case), converting texts or parts of texts to numbers or dates, extracting parts or analyzing them (e.g. how many words or do they contain a number), etc.

If you do not have the option to do this type of things directly in your database, you will use Calculated Fields in Tableau Software to get what you want from the text dimensions in your data source. That’s why I called this post String Calculations in Tableau.

Today’s post contains a set of 44 more or less practical examples of concatenation, conversion, analysis and extraction of texts. I will not go into the basic string functions of Tableau, like LEFT, FIND, LEN, REPLACE, etc. You can easily look up how they work in the manual or read the explanations directly in the Calculated Field editor.

I rather tried to pull together a small library of 44 more complex formulas you may find useful when you have to work with strings in Tableau, like concatenate strings and a date, convert a string to a date, reverse words in a string, extract parts of a string, remove line feeds, check if a string contains a number, count the number of words in a string and many more.

The article lists and explains all 44 formulas. I do not delude myself into believing anyone would read today’s article from start to finish. It is more a reference type of post and this is on purpose.

However, I recommend having a brief look inside, even if you are not looking for a certain string calculation in Tableau at the moment. I am starting the article with a little text visualization example and I am also providing a Tableau packaged workbook (on Tableau Public ) including all examples for free download at the end of the post.