A few weeks ago, we learned how to use quick table calcs in Tableau, this feature allows a user to quickly explore their measures and dimensions in a different way, or to quickly apply a commonly used calculation to your selected measure. They are fantastic for instantly giving additional meaning to a visualization.
However, they can be a little tricky to get your head around. This blog will take you through a good way to think of what is happening to your data in the background, so you might be able to use these quick table calcs with ease. I am currently preparing for my Tableau Certification test, and Q.T.Cs have popped up numerous times in the practice tests. Why not write a blog as part of my revision?
Giving your charts meaning
The example below shows a line chart of all the public schools at a primary level in my local government area (Sutherland Shire), and the number of enrollments they have had at each year.
So what? Most people in my area could probably tell you where the bigger schools are, what about growth over time?
Here you can see using a quick table calc can create a much more meaningful visualization, relative to the first value in the data set (easily configurable), we can see the 2 schools have doubled in size in 14 years, and 2 schools have halved in size.
What is the best way to go about understanding how things are being calculated? In the ‘’edit table calc” configuration window, it is not very intuitive when you are working on a chart that isn’t a table. I personally like Andy Kriebel’s way of breaking down how the calculations are done, into and easily understandable sentence. Here’s a link to his video on Quick Table Calculations.
Using a Cross-tab (a table)
When using Q.T.Cs I like to see what is happening in a cross-tab view, I usually start out like this and then come back to it whenever I get confused. It’s also a great way of learning how to configure the calculations to be as intended.
The example I will use is the rank calculation, but it behaves in a very similar way to other calculation. Below you can see a table of the number enrollments at 8 schools in the years 2010-2018. It also shows the rank of each school within this view, for each year, by number of enrollments.
What if I instead wanted the ranking of each year within this view, for each school?
It is really important to understand the difference between these two calculations, especially when it is harder to see in a visual chart, and when it involves more than two dimensions. When editing the calculation, there is a tick box to ‘show calculation assistance’, this highlights the direction and part of the view the calculation is being applied to. I highly recommend using this assistance.
Moving Average
Since this has popped up a few times in Tableau Certification practice exams, I thought I would revise this one especially. Rather than averaging every relevant value, a moving average includes only a small selection of values that sit adjacent to each value. This is a great tool to smooth out volatile line graphs (e.g. stocks data), or to provide an average that has a more limited context. For example, we have schools on this data set that have become completely different places.
For me to say that Cronulla Public has an average student base of 216.5 students is misleading, from the chart you can see that overall the number of students has dramatically increased, and I shouldn’t include older historical information in my average calc.
Here you can see the average value of attendance, calculated at each point by averaging the values from the previous 2 years, and the next 2 years. This gives us a good indicator of what the expected enrollments could be for a future year, as it only includes the context of relevant years.
I hope this blog has provided some introductory information about Quick Table Calculations in Tableau. They can become far more complex than the examples in this blog, and they can be unclear in how they compute vs human intuition. Thankfully, there are many resources on the internet available for this subject!
Data Set sourced from:
