For this blog post I will be deconstructing the solution to this #WorkoutWednesday to demonstrate how you can achieve the same outcome using both Level of Detail (LOD) calculations and Table calculations.
The aim of the challenge is to achieve the below look, one side using level of detail and the other using table calculations.
Level of Detail (LOD)
Let’s start by checking out the dataset. It’s a pretty simple one – a list of the total points of each Premier League soccer team each match day of the season. We want to construct a Viz to show how many times each team finished a day having scored the most points.
With anything to do with LODs (or table calculations for that matter) I like to create a crosstab to help me visualise exactly what’s going on. As discrete dimensions, drag Match Day, Team Name and Points Total onto the Rows shelf and sort Team Name as per the below.
We can now see which teams had the highest number of points for each day of the season.
Using an LOD calculation we need to find a way of identifying only the top team(s) for each day. Let’s look at this calculation:
Using Pris Lam’s scripting, what we’re saying here is, “for every Match Day, give me the maximum Points Total”.
We can incorporate this into a wider calculation that says, “if the number of Total Points equals this maximum, return a 1, and if it doesn’t, return a 0:
Note, the benefit of using numbers rather than TRUE/FALSE means we can more easily perform aggregations on the 1s later.
Drag this calculation into the Rows shelf as discrete as well, and you can see it has returned a 1 every time a team has the most points for each match day and a 0 everywhere else.
On a new sheet we can now start to build the left hand side of the chart up by dragging ‘Team Name’ onto the Rows shelf and the sum of our LOD calculation onto the Columns, sorting the teams by this sum in descending order.
Let’s park that one for now and move on to the Table Calculation version.
To better understand what we need to do, create a new crosstab less the LOD calculation. As before, we want to identify the maximum Points Total for each match day. Let’s try dropping a MAX function onto the rows shelf:
Hmmm… that hasn’t given us what we were looking for. It has returned the maximum Points Total by team for each match day, which is essentially the same as returning the Points Total value.
To combat this, we can use a Window table calculation. If we now drop a ‘window maximum’ of that maximum onto the Rows shelf we can set it to return the a maximum in respect to specific fields by going to [Edit Table Calculation] from the blue pill dropdown.
The way to read the configuration of the red box is to say, “for every Match Day, return the maximum Points Total across the teams.” Tableau will use the yellow highlighter to indicate what it’s doing. For more on how to read Table Calculations, see Andy Kriebel’s blog post.
Now we have the ‘window maximum’, we can build this into a wider calculation as we did for the LOD.
What we want to say is, if this window maximum of the Points Total equals the maximum of the Points total, then return a 1, and if it doesn’t, return a 0. You can double check this by popping it into the crosstab again if you’d like (making sure you edit the table calculation accordingly).
Switching back to the chart, add our new table calculation to the Columns shelf and you will get the below:
At the moment it’s returning a 1 for the overall maximum Points Total as we haven’t defined what ‘for each’ we want it to return it for, like we had in the crosstab. I have put a crosstab underneath to help see what’s going on.
To remedy this, we just need to drag the ‘for each’, in this case the Match Day, onto the Detail property of the ‘TC’ marks card… and voila!
Finally, to filter out the teams who were never top of the league, just add the sum of the LOD calculation to the filter shelf and change the bottom of the range from 0 to 1.
To complete the #WorkoutWednesday challenge and replicate the original fully, you will need to finish it off by playing around with the formatting. You will need a dual axis to move the column labels to the top of the chart, for example. This can be achieved using a dummy dual axis with a calculation such as AVG(1) or SUM(0) etc. You’ll need to turn the border colour off in the Color property for the table calculation side. To get the labels at the end of the bars on the LOD calculation, you just need to turn on Mark Labels, whereas for the table calculation side you will need to add an aggregate of the TC calculation as a reference line.
We’ve explored how we can achieve the same result through both level of detail and table calculations. Which one you pick will depend on the nature of what you’re doing, but this exercise demonstrates that cases exist where you can use either. In this example you could argue that the table calculation method is superior as the bars are partitioned up into the specific instances where that team came top. You might be able to utilise the Tooltips more easily to provide extra detail which could require more work to introduce to the LOD aggregation.
Table calculations are also preferred in situations where performance is a concern as, unlike LODs, the table calculations do not alter the query on the data, rather just perform calculations on the data that has already been returned and therefore not adding to the background processing.
For more information on which method to choose, Tableau have put together a guide which is definitely worth a read.