## Dynamic Visualisations with SIZE() and INDEX()

This is part 3, the final part, of my series on Table Calculations. If you haven’t seen them I suggest you check out Part 1 and Part 2.

In this part I want to return to the “dynamic” small multiple / trellis chart  I showed at the end of Part 1. Here it is again (or you can go a new window to see a larger version here):

[tableau server=”public.tableausoftware.com” workbook=”DynamicTrellis” view=”DynamicTrellis” tabs=”no” toolbar=”no” revert=”” refresh=”yes” linktarget=”” width=”600px” height=”600px”][/tableau]

This is based on a chart Marc Rueter showed in his talk (below) but I recreated it in my own way, so there’s a few special differences (I hesitate to call them enhancements, judge for yourself).

There’s a lot going on here so I want to break it down and help make it accessible to newer users.

1. Creating Dynamic Charts / Dimensions

Creating Dynamic Charts – i.e. ones with a dimension / measure that is chosen by the user at run time – is one of the key aspects of this chart. It’s simple once you know how – there’s a great knowledge base article on doing this here.

In my case I have created a parameter called Dimension

and an associated Calculated Field called [Dynamic Dimension]:

This calculation is just showing the associated dimension when needed.

2. Splitting the Dimension over Different Axis

Once I’d built my chart I needed to create a way of determining where in my trellis each item was going to sit, to do this I needed to (a) find out the total number of dimension members there were and (b) find out the order of those dimensions so i could allocate them across the trellis. Hopefully if you’ve been playing attention to the previous posts then you’ll recognise that the two calculations I need are SIZE() for (a) and INDEX() for (b).

So now I need to calculate which row / column I want my dimension to fall in, if I have [m] members then I want them to be distributed across √ ( [m] ) rows and columns roughly. Here’s the two calculations I chose.

First the X axis, which determines the column:

If you haven’t come across the % sign then it is a modulus operator. Modulus is an operation that gives the remainder following division, i.e. 8 modulus 6 = 2 because 8/ 6 = 1 remainder 2. Another example, 15 modulus 7 = 1.

So here we are calculating the INDEX() – 1, this gives us a zero-based position of our dimensions, then we take modulus with respect the square-root of the number of dimensions.

So let’s imagine we have 15 members, then our the square-root of 15 (to the nearest integer is 3) so:

Item 1: (1-1) modulus 3 = 0

Item 2: (2-1) modulus 3 = 1

Item 3: (3-1) modulus 3 = 2

Item 4: (4-1) modulus 3 = 0

Item 5: (5-1) modulus 3 = 1

Item 6: (6-1) modulus 3 = 2

Item 7: (7-1) modulus 3 = 3

Item 8: (8-1) modulus 3 = 0

etc

This splits the columns up as we need to.

next the rows, the Y Axis:

This is a

Again with our example:

Item 1: int( (2-1) /3 ) = 0

Item 2: int( (2-1) /3 )= int(1 / 3)= 0

Item 3: int( (3-1) /3 )= int(2 / 3)= 0

Item 4: int( (4-1) /3 )= int(3 / 3)= 1

Item 5: int( (5-1) /3 )= int(4/ 3)= 1

Item 6: int( (6-1) /3 )= int(5 / 3)= 1

Item 7: int( (7-1) /3 )= int(6 / 3)= 2

Item 8: int( (8-1) /3 )= int(7 / 3)= 2

etc

Item 15:int( (15-1)/3)= int(14/3)=4

So note that we’ll have 5 rows and 3 columns. This is slightly different to Marc’s example (and a latter example by Dan Montgomery) where the columns and rows were more evenly distributed. For me this gives the line chart more room to “breathe” but with the effect it squashes the y axis of the line chart, so there are trade-offs.

3. Compute Using Problems

I initially had problems when dropping on the X and Y Axis “splitter” dimensions I had just built, the Compute Using I’d expect to work (Compute Using [Order Date]) just wasn’t working. This led to some investigation, it led me to understanding a whole lot more about Table Calculations and it led to the previous two posts in this series – what a spin off from just this one visualisation! [Incidentally this is why I love blogging – it makes me understand what I’m writing].

What was the problem? Well the [Order Date] variable consists of a different number of dates for each dimension member, i.e. some States didn’t sell on a given date. This throws off the INDEX() calculation and affects the whole calculation of the X and Y splitters. How do you get round it? We explored it in detail in Part 2 of this series, but the “At the level of” will ensure we get a kind of crosstab effect among the dimensions and those missing values will be populated for all dimension members.

If you play around with this example and try to build it yourself, and I heartily recommend you do, you will also note the difference between using a discrete and a continuous variable in the table calculation. I need to do more experimentation here but it seems the Continuous variable doesn’t let itself be “padded” in the same way as the discrete version, so calculating using the Discrete version and using an ATTR on the continuous was a nice way to work around this – thanks to lots of older Tableau forum posts from Joe Mako for helping me figure out that one.

At this stage we’re done, and we can hide the headers for [X Axis] and [Y Axis]

4. Adding a (optional) Title to each small multiple

Now we have our Viz working we need to add a title, and boy this isn’t as easy as I thought it might be. If we just add  a label mark on [Dynamic Deminsion] Tableau will label the ends of the chart, but we want a nicer version in the middle of the chart don’t we.

Here’s where I used a neat trick I’ve used many times before. A dummy variable called [One]:

Not too complicated huh? I intend to do an entire blog post on the power of this calculated field but for now we just add it to the Viz as a new Row Dimension and we get this – the flat lines are the result of our new dimension.

So now you’re wondering where I’m going with this?

Well I turned down the transparency to hide the line and added a new calculation:

This calculates the point half way along each [Order Date] (by taking the number of [Dates], but wait…

Hmm, not satisfactory. That INDEX() value sometimes occurs at different points making the titles wonky across the Viz – we need some uber calculation if we want to improve it. There are many solutions, here’s mine (you’ll need to click for a larger version)

Not for the faint hearted! but it calculated the exact middle of each viz find the difference between the start and end dates, then finds the value that is closest to the date halfway between the two.

That’s it, a final touch if you wanted is to make the [One] variable dual axis to reduce the space taken by the header, as I have done on this second version.

Nottingham, UK

### 10 thoughts on “Dynamic Visualisations with SIZE() and INDEX()”

1. Jay says:

I used your technique this week to create a very similar item. Thank you for posting these step-by-step directions. The only caveat I have to add is that the One field you use to do the dynamic label may need to be modified when using it in dual-axis mode. I am allowing the user to switch between a count variable and a percent variable. If I don’t adjust the One appropriately, I can end up having the vertical axis skew more than I wanted.

Also, for some reason I can’t get your rather complex midpoint determining formula to work for me and I can’t figure out why. Part of my problem is that it’s tricky to see the whole thing at once in a format that allows me to figure out exactly what is going on.

But I never would have gotten as far as I have without this tutorial, so thank you so much!

2. Alan says:

Great series of posts.

Is it possible to add another dimension to trellis chart without messing up the format? I tried adding MY(Order Date) to the details as I want to create an action filter that uses only the month and year. However the format completely changes. I adjusted all the table calculations, but I can seem to retain the original format.

1. Chris Love says:

Hi Alan, adding a time dimensions does make the table calcs tricky – does changing it to a attribute help? If I find time I will take a look myself and see whats involved.