The twittersphere & inter-webs are abuzz with help understanding Tableau Table Calcs. Among the more recent, these are my favorites:
- Chris Love's in-depth, three part series:
- Jonathan Drummey's Think Data Thursday:
- Joe Mako & Matt Lutton breaking it down IRT:
And fully two years ago now, Jonathan started to compile this list of resources for all skill levels:
With such a trove of useful information available, this post of mine is brief & tactical: The Anatomy of a Nested Table Calc, i.e. what does it look like?
If data is our paint, then Table Calcs are the Tableau artist's wooden palette: the place where we can "mix & match our pigments".
For more detail on the difference between Table Calcs & Calculated Fields, see the Master Tableau Approach.
The Nested Table Calc
To annotate the Anatomy of a Nested Table Calc, I'll piggy-back upon the example recently narrated by Joe & Matthew in their 12 minute TRL video Ratio of Ratios.
First, a run-down of their example. While it occupies only a single pill on the canvas, the Ratio of Ratios calculation is actually a nesting of three separate table calculations, each computing in a different direction.
The Sum of Sales for Category partitions by Category, and must therefore compute using the alternate dimension(s), which here is Order Date (Weekday).
The Sum of Sales for Weekday partitions by Order Date, and must therefore compute by addressing the alternate dimension(s), which here is Category.
The Sum of Sales Overall has NO partition, and must therefore compute addressing both Category and Weekday.
Here's the Good Stuff
OK! With a complimentary re-hash of Joe & Matthew's video behind us, here is your quick reference guide. Do you have any points to add to this list? Please let me know!
- Using the drop-down via the Edit Table Calculation dialog, Addressing & Partitioning instructions can be set individually for each sub-component of a nested table calc.
- If a table calc pill is already on the canvas, then each subsequent use of that pill on the canvas will inherit the existing addressing & partitioning instructions by default
- You are, of course, free to change them!
- These instructions must be set by hand when the pill used for the first time
- Relative compute usings (like "Table Down" / "Pane Across") should be avoided.
- Because they will break when you rearrange the view!
- The simple "Compute Using" dialog on the top level calculation pill should also be avoided
- It will hard code all of the sub-components, which probably isn't what you want
- Always use the advanced "Edit Table Calculation" dialog instead
- Table Calculations can be nested up to 128 levels deep
- Thanks, Noah!
- One's mastery of nested table calcs enables some pretty amazing things
- Like 8 out of the Next N Table Calcs
- .. and Multi-Select Filters: INTERSECTION instead of UNION
( *More help on finding the intersection between multi-select action filters in a future post :)
As your view becomes more complex, with ever more dimensions partitioning the canvas table, these are a few helpful tips for simplifying your approach to table calc solutions:
- Think in terms of "Partitioning"
- always use the advanced edit table calculation dialog
- if it not a "Partitioning" dimension, then it's "Addressing"
- To prevent a dimension from partitioning the canvas table
- change it to an Attribute
- attribute dimensions are ignored by table calcs
- Sequence matters in the "Addressing" space
- mimic the sequence defined in your workout view
- Begin with a workout crosstab
- all the dimensions on the rows shelf
- verify the calculations & number of marks as you go
- Build up to complex calculations in small pieces
- As needed, bring in the "big guns"
- At the Level Of
- Restart Every
- Sort Order in the Adressing Fields
- After you've reached the correct values in your workout view
- consolidate components with similar compute using instructions
- rearrange the pills & adjust the mark types to build your vis
Word Count: 690
- "Understanding Tableau Table Calculations using INDEX() and SIZE() – Part 1", Chris Love, The Information Lab, September 29, 2014:
- "Understanding Tableau Table Calculations using INDEX() and SIZE() – Part 2", Chris Love, The Information Lab, October 1, 2014:
- "Dynamic Visualisations with SIZE() and INDEX()", Chris Love, The Information Lab, October 6, 2014:
- "Setting up for Table Calculation Success", Jonathan Drummey, Think Data Thursday, July 10, 2014:
- "Tableau Request Live - Ratio of Ratios with Nested Table Calculations", Joe Mako & Matthew Lutton, October 4, 2014:
- "Want to Learn Table Calculations? Here’s How!", Jonathan Drummey, Drawing with Numbers, September 10, 2012:
- "Master Tableau Approach", Keith Helfrich, Red Headed Step Data, September 19, 2014:
- "The Next N Table Calcs", Jonathan Drummey, Drawing with Numbers, July 11, 2013:
- "Multi-Select Filters: INTERSECTION instead of UNION", Tableau Ideas, May 14, 2014: