## 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

SIZE()

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.

Nottingham, UK

### 6 thoughts on “Understanding Tableau Table Calculations using INDEX() and SIZE() – Part 2”

1. Hi Chris,

Thanks for posting this!

Personally I found the use of Size on the Size Shelf to be a little confusing for this demo for a few reasons. The first is that it’s using larger mark count=>smaller marks. Also, the change in Mark Size isn’t as apparent because most are in a narrow band whereas the Size legend is almost an order of magnitude larger. And the changes are only visible when we change the filter, so we can’t do a side-by-side comparison. A small multiple view or zoomed-in view of a few states with State as a dimension (so Size is the # of postal codes in a state) would make this more obvious.

Also, whenever using table calcs I like to think about performance. The Size Calculation worksheet is causing Tableau to issue a query that returns up to 1697 records (for the 1697 Postal Codes) because Postal Code is a dimension in the view. In many cases, a faster query result would be to leave the Postal Code out of the view and just use COUNTD(Postal Code) that will only return one record. (This might get faster in v9 with query caching).

One thing I noticed here that I’d sort of noticed before but this makes it really obvious is how Size and Color legends & assignments are done at different times in the Tableau pipeline. The Size legend includes all the sizes no matter the State filter (so it’s apparently computed before the filter), while if you put that same calc on Color it only has the one value (so it’s computed after the State Filter).

Jonathan

2. Chris Love says:

Hi Jonathan – it’s always a pleasure to see attract interest, thanks for taking the time to comment and add your perspective.

I did think long and hard about the first example. It was intended to simply increase the size of the points as there were less on the screen, not as a comparison between different States. The Size wasn’t meant to convey anything, hence my warning in the blog post about this. perhaps I should have explained the example more because it was tenuous – but finding a simple SIZE() example is tough.

I agree performance considerations are imperative when performing any table calculations – you are right to bring this up and it is something I should have mentioned during the blog.

Your final comment has left me puzzled, as it’s not what I’ve experienced. I’d love to experiment more and will connect with you offline about this.

3. Gretta says:

4. Hi Chris,

I’ve really enjoyed this series of posts…very informative and useful. I actually used some of this content for an internal training call. In preparing for that call, I modified one of your examples a little, expanding on the lessons you wrote about to add a dynamic ranking to the view. You can see my update here:

https://public.tableausoftware.com/profile/mtmixon3894#!/vizhome/IndexandSize/InteractiveHierarchy

I’d be curious to know if you would do any of this differently. It obviously works, but in my experience with Tableau, there is often a more elegant solution just around the corner. 🙂

-Mike

1. Chris Love says:

Hi Michael, looks perfect to me I can’t think how I’d do it any better. Many thanks for sharing and for your feedback on my posts, I really appreciate it.

5. Piyush Jain says:

Hi Chris,

I get the error message “Function returns 1 value while 8 values were returned”. This is because in one of the filter, only 1 row (value=37) is selected while function expected to get 8 rows from the filter. Is there any way to return the multiple values from the script_str() functions in tableau.

Thanks
Piyush Jain