Let's talk about our love of Table Calculations
Comparing two periods in a single column
Table Calcs: A love story
It’s a new year and we’ve got new “set” of workouts for #WorkoutWednesday. Our first one for 2021 comes from one of the new coaches this year, Candra McCrae. She’s got a great kickoff challenge of a simple trend line, a BAN, and the ability to choose both a reference period and a comparison period. Of the many requirements Candra set, the biggest one is NO LODs. So that means we need to test out our knowledge of table calcs. I urge Don’t be afraid of table calcs, friends because when you learn table calcs, only then do you truly learn Tableau software.
Table Calcs
Before we begin, let’s review our Tableau order of operations. It has changed throughout the years. Let’s take a look at the most current version.
As you can see here, Table calcs happen way down at the bottom. There’s another version out there – on the ideas forum is currently 4 years old with currently 520 votes, as of 1/2021 – that I think does a great job further explaining the split between who’s doing the work. Check this out:
Isn’t this great!? It separates calculations from filters AND most importantly draws a line separating what happens in the database and what happens in Tableau (provided its a live connection, of course). And yes, ALL table calcs happen in Tableau while all LODs happen in the database. So learning and mastering table calcs means learning and mastering Tableau. Its as simple as that. So let’s go through this challenge and learn just a little bit more about table calcs.
It starts with a crosstab
My first tip with learning table calcs is to use a crosstab to validate every move make. It’s the best way to ensure what you’re doing is correct. So to start off we know that we should set up our crosstab with [Year] on rows and [Food Insecurity Includes Low And Very Low Food Security] on the text shelf of the marks card. The view should look like this.
Great! Now let’s set up our parameters, we’ll need two of them. The first will set up our year selector and the other will be our comparison selector.
The [Food Insecurity Includes Low And Very Low Food Security] field will act as our trend line so now we need to figure out a way to isolate BOTH our year selected and the comparison year.
The year selector parameter is fairly straight forward
//Year = Parameter
[Year] = [Year Parameter]
Since we have a parameter selector for the comparison year, I like to create separate fields for each and the another field to bring them all together. So I need to create fields for:
- First Year
- Most Recent Year
- Previous Year
//First
First() = 0
//Most Recent
Last() = 0
//Previous
attr( [Year] ) + 1 = LOOKUP(
sum ( if [year = parameter] then [Year] end ) , 1 )
Woah, let’s unpack this last one. There are probably a lot of different ways to solve this overall challenge. However, I decided that I wanted to use boolean calcs for the First and Last calcs which meant that I needed to figure out a way to make the previous calc a boolean as well. So let’s break this down and take a look.
The lookup function will return a column dimension value (that’s the right side of the calc) see below. See how it returns the selected year on the previous row.
and in order to turn into a boolean we need to compare that value with something. And we can’t compare it to the year value on the row because they don’t match. Which is why the left side of the calc adds one unit to the year for each row.
See how now there’s a matching Year value? That’s what we want!
Now that we have our three boolean calcs, we’re ready to incorporate our comparison parameters. And to do that we’ll do a simple case statement
//Comparison Year
CASE [Lookback]
when 1 then [First]
when 2 then [Last]
when 3 then [Previous]
END
Now we’re ready to write one calculation to bring them all together.
//Line Dots
if [Comparison Year]
or max ( [year = parameter] )
then min ( [Food Insecurity Includes Low And Very Low Food Security] )
END
The OR operator allow to us to return multiple values in one column provided they meet the criteria. And our criteria with this calculation is to return the value when the year equals the year selector or the comparison boolean is true. Let’s take one last look at our crosstab with our parameterbefore we start building our trend line.
- SUM ( [Food Insecurity…] )
- [Line Dots]
Columns:
- [Year]
Then format the Food Insecurity mark as a line and the Line dots as…yep, dots. Then to get the dot colors, you can just drop [Comparison Year] on color and select the appropriate colors.
The last section is the title which has the selected year value and a delta value from the comparison value.
To do that will require a couple additional calculations. This time we’ll be using the window function. The window function is necessary because tableau can only doing math between two values on the same row. And the way that we built this our values are not on the same row. So let’s do that.
//Selected Year Value
WINDOW_MIN ( SUM ( if [year = parameter] then [Food Insecurity Includes Low And Very Low Food Security]
END ) )
//Comparison Year Value
WINDOW_MIN ( if [Comparison Year] then min ( [Food Insecurity Includes Low And Very Low Food Security] ) end )
The window function, again, returns the value and puts that single value on every row of the determined partition. Now we take the difference. *It should be noted that I deviated from the requirements. I find it more intuitive to do a simple difference when already dealing with percentages. Its a bit easier for people to understand. Both approaches are good and have merit. Choosing one over the other should be a conversation with your stakeholder in the real world.*
//Delta
[Selected Year Value] – [Comparison Year Value]
Now you’re ready to put it all together and do some formatting and don’t forget the tooltips!
Hopefully, after reading this and stepping through each calculation of the process you have a better understanding of Tableau table calcs and aren’t as afraid to use in your next project.
GO FORTH AND VIZ!
Check out the interactive visualization below
