Understanding Tableau Table Calculations using INDEX() and SIZE() – Part 2
This blog post and its predecessor (Part 1) were brought to you courtesy of the table calculations SIZE() and INDEX().
In Part 1 I explored what table calculations were and how they worked, I explored partitions and how we can use “Compute using” to influence how partitions are created and how they are ordered. Finally I introduced an advanced visualisation that used INDEX() and SIZE() as a lead in to this blog post.
In Part 2 I’ll explore some use cases for INDEX() and SIZE(), from the very simple to the advanced. I encourage you to read through my comments, then take the Superstore Sales data shipped with Tableau and start experimenting for yourself. This is something I regularly do with blog posts and talks and is how this blog post got started; I was fortunate enough to see Marc Rueter give his “Top 10 Tips to becoming a Tableau Jedi” talk at the Tableau Customer Conference in Seattle, he showed a few examples that inspired this post. While I have based my final dynamic visualisation on what I saw Marc produce I have recreated it from scratch from memory, so there will be my own tweaks and “improvements” along the way.
[As an aside I love the way the Tableau community takes each other’s work and improves, changes and develops ideas along the way. Dan Montgomery for instance took some elements from Part 1 of my blog and added his own detail to create his own version of my final workbook, you can see his post here. I encourage you to experiment, improve and question everything that follows. This is what keeps me learning and makes the Tableau community the best software community in the world in my opinion].
Let’s start with SIZE() and on the easy side of the Tableau spectrum. Hopefully this trick is something everyone can make use of no matter what their skill level in Tableau. If you want to follow along then I suggest you use my data source from the workbook, different Superstore Sales datasets exist and so the steps may be different depending on you source (e.g. if you are using the supplied tds with Tableau then you will have Cities and States in a hierarchy) .
Use Case 1: Varying Mark Size using SIZE().
Wow Factor = Subtle but effective. Difficulty to build = Simple.
[tableau server=”public.tableausoftware.com” workbook=”TableCalculationsExplained-Part2″ view=”UsingSizetovaryMarksizes” tabs=”no” toolbar=”no” revert=”” refresh=”yes” linktarget=”https://public.tableausoftware.com/views/TableCalculationsExplained-Part2/UsingSizetovaryMarksizes?:embed=y&:display_count=no” width=”550px” height=”750px”][/tableau]
The dashboard above shows customer zip codes from the Superstore Sales dataset. There’s a filter on the top right to enable users to see the data at a State Level. Notice what happens as you use the filter: the size of the points change as the level of filter changes; in fact I am varying it based on State, depending on the number of data points in the view (I’ve added a floating helper sheet to show how many points are shown at any one time).
The change is necessary as we move from the high level – a full view of the US where large points would overwhelm the viewer – to a low level – where small points would be lost against the background. This is a technique I used in my recent Find your Next Whisky Dashboard when I wanted to show labels on points, but also show a smaller lower-level zoomed out version without labels.
So how is this achieved? Surprisingly easily you’ll be pleased to know.
Obviously the starting point was a map of the zip codes with a quick filter. The first step in creating our dynamic sizing was to create a calculated field called Size using the formula
and drag this onto the “Size” control on the marks card.
[Another aside – there are a few calculations I almost create as a matter of course for every visualisation, SIZE() is one, INDEX() in another and we’ll meet ONE later in this series; the other is ZERO. These latter two will feature in a future blog post on their own as they deserve special praise – but more on that later].
Our next job is to count the number of marks on the map, since we only have one dimension then we want to ensure we compute using that, therefore making our partition the whole dataset (after filtering).
Hopefully you’ve not lost me yet. If you have, go back to Part 1.
The next step is to change the sizing of the marks, we’re currently sizing on SIZE(). SIZE() increases as there are more points, and so currently each point is very large. We want to reverse the order so that points get smaller the larger SIZE() gets. We also get only one SIZE() value when we change the view so we also want to fix an upper and lower limit to the SIZE(); if we don’t then the automatic ranging will simply size the points according to our default size setting (set by clicking the size marker).
The settings I used are shown below:
And you have your dynamic sizing marks. Simple, but effective.
[Word of caution – the size of marks is sometimes used to infer information back to the user, using this technique in certain situations might lead to users inferring the wrong information. Ensure the user isn’t expecting the mark size to convey information]
Use Case 2: Hierarchy Drilldown.
Wow Factor = “Oh that’s cool”. Difficulty to build = Complex.
[tableau server=”public.tableausoftware.com” workbook=”TableCalculationsExplained-Part2″ view=”HierarchyDrill-down” tabs=”no” toolbar=”no” revert=”” refresh=”yes” linktarget=”https://public.tableausoftware.com/views/TableCalculationsExplained-Part2/HierarchyDrill-down?:embed=y&:display_count=no” width=”550px” height=”750px”][/tableau]
The above workbook shows the result we are looking for here, click on a state and the workbook will drill down to the city level showing the number of sales within the state. Now that’s cool, but that level of coolness comes with a price, we’re upping the complexity a notch, so bear with me. Of course we could achieve close to the same effect using a hierarchy but this filters are the same time.
Let’s dive into story mode to pick up the how to one this one – click below to see the rest of the blog.
I promised to get to the Jedi level dynamic trellis in this post but I think I’ve done enough writing for now. Like Peter Jackson before me, what I originally envisaged as one has now become three. Part 3 of this trilogy where we will explore the final use case – I hope you are enjoying it.