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