How to add dynamic, interactive hierarchies to your Tableau Dashboard
The second article of a guest post series on “Expand your Reach in Tableau with Parameters”
This is the very long expected continuation of a guest post series by Sheel Bhatiani, Lead Software Developer at Cheyne Capital. We started Sheel’s series already back in November last year, but ever since I left you waiting. Please blame it on me, it is not Sheel’s fault. It is my bad. When starting the series, I have simply bitten off more than I could chew and I didn’t find the time to do the final editing and to publish the next article.
However, better late than never. The wait is over and here is Sheel’s second post. Enjoy.
My first post Dynamic Sorting with Tableau introduced a technique to expand your reach in Tableau. One Parameter combined with one Calculated Field enables you to add an interesting additional feature to an interactive dashboard: let the user decide the sort criterion and the sort order for a selected view.
Today’s second article of my guest post series “Expand your Reach in Tableau with Parameters“ will use a similar technique for adding a similar interactive feature: let the user decide which hierarchy shall be shown in a view. Again, the post includes the background, a detailed step-by-step tutorial and the dashboard for direct interaction here or the option to download the workbook via Tableau Public.
The Background – Hierarchies in Tableau
First things first: What is a hierarchy in Tableau?
Even if you have never heard of hierarchies in Tableau before, I bet you already used them. Tableau automatically creates hierarchies for all date fields in your source data. This way, you can easily break the dates down into year, quarter, month, etc. On top of this default Tableau behavior, you can also create your own hierarchies for other dimensions.
Let’s have a look at the Superstore Sales sample data coming with each Tableau installation. You find the following dimensions in this example: customer state, region and ZIP code. All of them are geographical fields and they have an inherent order. State first, region second, ZIP code last. Thus, it definitely makes sense to create a geographical hierarchy containing these three fields and to allow an easy drill-down through this order. The following image shows step by step how to do this:
As an alternative to this standard way of creating hierarchies, you can also simply drag one or several dimensions on top of another one and the Create Hierarchy dialogue shown in step 2 will automatically pop up.
Easy, isn’t it? A few mouse clicks and you created a hierarchy with all the features you already know from the date hierarchy Tableau automatically provides. Dragging the hierarchy itself or one of its members e.g. on the row shelf lets you drill down and up by simply clicking on plus and minus signs shown on the pills:
There is a less known additional functionality. This feature also exists in the view itself: the plus and minus signs are available in the row headers, too.
That’s a very quick way to interact with your view, isn’t it?
Of course you can create hierarchies for all fields in your data as long as it makes sense. In the Superstore Sales data, for one, you may also create a product hierarchy containing product category, product sub-category and product name.
The Idea – Parameters to Expand your Reach with Hierarchies
Hierarchies are a great Tableau feature to structure your data and to improve the way you interact with your views. However, even if you want to show the same measure (let’s say SUM ([Sales])) by different hierarchies (let’s say geography and product), you need two separate views:
Wouldn’t it be nice to have only one view (i.e. one bar chart) and let the user decide whether he wants to see the data by geography or by product? Something like this:
Only one bar chart and a simple drop down is giving the user the choice to decide how to slice the data. Is this possible?
Yes it is and it is pretty simple, too. The key to this are again Parameters in combination with Calculated Fields.
The Step-by-Step How-to
Step 1: Create Hierarchies
See the first section of today’s article how to create a hierarchy and define one for the geographical data and one for the product, as already used in the views above.
Step 2: Create a Parameter
Create a new Parameter, give it a meaningful name like “Hierarchy Chooser”, select string as the data type, choose list as the allowable values and enter texts to name the 2 hierarchies like “Region ► Customer State ► Zip Code“ and “Category ► Sub-Category ► Product Name”. Similar to the Parameter we used in the basic approach of Dynamic Sorting with Tableau, we are using horizontal triangles within the strings to indicate that those texts are representing hierarchies. Here is a screenshot of the Edit Parameter dialog window:
Step 3: Create Calculated Fields
This step is a bit tedious. We need one generic Calculated Field for each hierarchy level. Let’s call the first one “Hierarchy Level 1”. The calculation is a simple CASE WHEN statement, assigning the first level of the existing two hierarchies according to the Parameter defined in Step 2. In other words, “Hierarchy Level 1” will be [Region] if the Parameter equals “Region ► Customer State ► Zip Code“ and [Product Category] otherwise. The Calculated Field dialog looks like this:
We repeat this step and create a Calculated Field called “Hierarchy Level 2” and assign either [Customer State] or [Sub-Category] and a third one (“Hierarchy Level 3”) for the third level of the hierarchies.
Step 4: Create a generic Hierarchy
Combine the three Calculated Fields created in Step 3 to a new generic hierarchy called “Dynamic Hierarchy”.
Step 5: Use the generic Hierarchy on the Row Shelf
Create only one bar chart (instead of two) with SUM ([Sales]) on the column shelf, but this time drag [Dynamic Hierarchy] to the row shelf and drill down using the plus sign of the pills.
Step 6: Show Parameter Control
Last step is to show the Parameter Control on the view for direct user interaction.
That’s it.
Here is the interactive version on Tableau Public:
Where is the beef?
Sounds like a nifty little workaround, but where is the beef? What is the big whoop? It is about saving real estate.
Real estate is the most limited resource on a dashboard, no doubt about it. If you are building an analytical visualization your users are interacting with, the described technique helps to save a lot of space. Room you can use for other views and thereby enable your users to get more insights from the dashboard.
The Drawbacks
The technique comes with a few drawbacks:
- Additional time needed to set up the hierarchies, the Parameter and the Calculated Field
- If you are using hierarchies with a different number of levels, e.g. the first hierarchy has three levels and the second one has only two, you have to use an empty string or Null for the third level of the second hierarchy. You can still drill down to the third level, but there would be no data if the second hierarchy is selected.
- Increased efforts for maintaining your workbook if e.g. dimension names are changed or additional levels shall be added to the hierarchies.
From my point of view only minor drawbacks, but please decide for yourself.
What’s next?
It goes without saying that the next post will continue this series. The next article will describe how to use parameters to let the user select the mark types of a view.
Stay tuned.
Leave a Reply