I was recently asked to contribute to a Tableau training session at our company. I decided to cover some of the concepts that Chris Love of The Information Lab wrote about in an excellent three-part blog series on Index and Size functions, since I consider those to be two of the more powerful and flexible functions inside of the Tableau tool-set. Once I got comfortable with those (especially Index), my canvas of possibilities inside of Tableau grew exponentially.
Chris has an example where you can use these two functions to create a dynamic hierarchy, whereby when you click on a state in a bar chart, all of the associated cities display, and when you de-select the state, the cities “disappear”.
Chart with “hidden” city values
A pretty cool trick. But what I found myself wanting to do was rank the data at either stage of the hierarchy. In other words, when the chart just showed states, I wanted the states sorted in descending order of sales, and when the chart showed cities inside of a selected state, I wanted those cities to be sorted in descending order of sales as well. You can sort the State field using the standard sort menu item, but since the Cities field is based on a table calculation (per Chris’s example), that sorting option isn’t available.
Sorting State by Sum of Sales (Good!)
Sorting Cities (Bad!)
Creating the Dynamic Rank
In order to create a rank that works on both levels of the hierarchy, there are a few additional fields you will need to derive (beyond the fields that Chris already walked through to enable the dynamic hierarchy):
1. Total sales by state
This is just a window sum calculation.
2. Rank of State
This leverages the rank_dense function and the Total Sales by state that we just created. The rank_dense function is one I’ve only recently discovered (and wrote about here), but have found it to be a very powerful tool for these types of use cases.
3. Rank of City
This is a simple index function.
4. Dynamic Rank
Using the # of States field Chris already showed us how to create (a field that uses the Size() function to count the number of states in the view), we decide which Rank field to use for sorting.
Once you have those fields, then you can place the final Rank field onto the Rows shelf. As you can see from the screen shot below, that single Rank field references four different calculated fields, each of which needs to have its Compute Using set a certain way in order for final sort to happen correctly.
I suspect there are different ways that these table calculations can be set (and I suspect a Zen master would identify the most elegant method), but the settings below worked for me. 🙂
In all four cases, I placed State and Cities in the Addressing column in the Advanced setting of “Compute Using”. But then I set the “At the Level” and “Restarting every” values differently depending on the calculation.
1. # of States
Set “At the level” to State. This essentially counts the unique number of states inside of the view.
2. Rank (City)
Inside of the “Compute Using” section, I designated descending sort on sum of Sales, and then set “Restarting every” to State. This sorts the cities within each state by sales volume and assigns an index value (e.g. 1, 2, 3) to each state based on that sort.
3. Rank (State)
Since the rank_dense function already designates which field to use for the sort, along with whether it should be ascending or descending, all of the defaults were fine.
4. Total Sales (State)
Set “Restarting every” to State. This aggregate sales for all cities within a state.
The final view is below. Click it to go to the interactive version. Feel free to download it. I’ve broken out many of the steps above into individual tabs and story-pointed them together.