Last year I did the big workbook on conditional formatting to answer some really common questions on the Tableau Community Forums. One of my projects lately has been to do the same for table calculations, which are incredibly powerful, sometimes incredibly complicated, and I believe underutilized. Tableau put together a set of Top 10 Table Calculations, here’s a list I’ve compiled of the next N most-commonly useful table calculations, based on volume of questions on the forums and relative ease of construction (there’s no densification, domain padding, domain completion, or any of that stuff in this batch):
- Filter Without Affecting Results
- Filter Top N Without Affecting Results
- Filter 1st Time Period from Difference from Prior
- Sorting by a Table Calc
- Comparing Selected to Group
- Aggregating at Different Levels…
- …And Returning Fewer Results
- Filtering Out Extra Marks by Using a Duplicate on the Filters Shelf
- Nesting Table Calculations to Aggregate in Different Directions
- Performance – One Computation to Return Same Result to All Rows
- How Many of X Did How Much of Y
- Title Showing Date Range
- Jittering a Scatterplot
- Extending an Axis with an Invisible Reference Line
- Making a String List
And of course, there’s a workbook with instructions! Click to view and download the next N table calculations workbook on Tableau Public or click the image below:
If you have any other really common uses for table calculations, leave a comment!
Wow – Jonathan. Just going through this now and it’s such a terrific reference. Thank-you for compiling it all and your clear instructions.
I’m glad it’s useful to you!
Pingback: Want to Learn Table Calculations? Here’s How! | Drawing with Numbers
Hi Jonathan,
I’m just coming around to study up on these & as usual, you’ve gone above and beyond. Thank you!
A few observations, corrections, and questions:
Sheet #3
————-
I find a green order date pill on the filters shelf and the worksheet doesn’t do anything at all when switching the parameter from OFF to ON.
It seems your intention was to publish the workbook without that green order date pill in the filters area? Perhaps you’ll want to remove it & publish again.
Sheet #4
————–
Love the technique for sorting a dimension from the primary data source by a discrete measure from the secondary data source when blending!
Sheet #5
—————
I’m confused by what’s happening here. I would expect for the brown “5. Avg Sales per Category” line to remain constant, regardless of which Category is being compared via the radio button quick filter. Yet, when I choose new Categories for the quick filter, the values for the brown “5. Avg Sales per Category” line change, also.
Perhaps I’m missing the point of what the view is meant to do and I’m expecting the wrong behavior?
Sheet #8
—————
Small typo. Change the title to read: “If you open up the prior worksheet 7. …And Returning Fewer Results”
This must be leftover from when the “…And Returning Fewer Results” sheet was originally #5
Sheet #12
————–
Curious, why is it necessary to wrap the date calcs in a TOTAL() wrapper? When I remove the TOTAL() wrapper, I can see the value goes to nonsense, returning the word “ALL”. But I don’t yet understand the reason, or the function the TOTAL() wrapper is providing here?
One observation is that, without the TOTAL() wrapper, the MIN([Order Date]) calculation isn’t a Table Calc. So this means, without the TOTAL() wrapper, the MIN() calculation is performed against the entire data set. And the purpose of the TOTAL() wrapper is to get the MIN([Order Date]) calculation to run against the scope of values left in view after all other filtering occurs.
Did I answer my own question correctly ?
Sheet #14
————–
Small Typo: this one uses 1.4 (should be: this one uses 14.)
Again, thanks, this is great material!
Hi Keith,
You’re welcome, and I appreciate the feedback! I’ve posted an updated workbook and have responses below:
#3 – Thanks for catching that. The problem was that I’d only wanted the view to have two years in it, so I’d set up the continuous Order Date pill as a range filter with a relative filter for the last two years. That worked fine in the data set during calendar 2013, so the filter pulled 2012 and 2013, however once the calendar flipped to 2014 then it failed because there wasn’t any 2014 data. I’ve adjusted the dates in the data to reflect the current date using DATE(DATEADD(‘year’,YEAR(TODAY())-2013,[Order Date])) so the view works as desired and will continue to work in 2015 and beyond.
#4 – I like it too! And I wish it wasn’t necessary, vote for http://community.tableausoftware.com/ideas/2773 if you’d like to see that need go away in a future release.
#5 – Sorry I wasn’t more clear in the description. The blue line is the sales for the selected Category. The orange/brown line is the average sales for each Category _within the Department_ that Category belongs to. One way to see this is by putting Department on the Rows Shelf, then dragging the table calc filter onto the Rows Shelf just to the right of the Department pill. This reveals that the orange/brown lines for all the Categories for a given Department are the same.
#8 – Thanks for the catch, I’ve corrected it.
#12 – You wrote, “So this means, without the TOTAL() wrapper, the MIN() calculation is performed against the entire data set.” This is not correct. All regular aggregates such as MIN(), MAX(), SUM(), MEDIAN(), etc. are ultimately performed at the level of granularity of the view (there are additional complications if the linking fields in a data blend are different than the view that are normally invisible to the user). Year(Order Date) and Department are dimensions in the view that define the granularity, so MIN(Order Date) returns a variety of values – put MIN(Order Date) on the Label Shelf to see this. When there are more than a certain number of values returned by a measure or dimension in a view and that measure or dimension is used in a Title or Caption, Tableau will then show “All” for that measure or dimension in the Title or Caption. The TOTAL() calculations were set up with a Compute Using of Table (Across) to return a result for every Department, which returns the same value and the Title shows that value. I’ve adjusted the TOTAL() calc in the view to have a Compute Using of both dimensions, which guarantees that it will always return the minimum result across the data that is returned to the view (which in this view is limited by the Order Date filter). An alternative to using TOTAL() would be to use WINDOW_MIN() or WINDOW_MAX(), which could be faster in some circumstances.
#14 – Actually, that wasn’t a typo, I can see how you’d think that since the worksheet number and the value were 14 and 1.4. The invisible reference line calc 14. WMax Sales * 1.4 has the formula WINDOW_MAX(SUM([Sales]))*1.4.
Thanks again for your feedback and questions, please keep them coming!
Jonathan