What you will learn
By the end of this posting you will have gained an idea of how we can make the “hide” option in Tableau dynamic which is essential when you are not only using table calculations but also applying totals from the analytics pane.
The challenge
Assume you want to show a 12 months rolling sum of sales in a table for the last twelve months. This means you will need to use a table calculation like this:
//Sales LTM
window_sum(sum([Sales]), -11,0)
//side note: this calculates for the preceding 11 months including the month you are at so twelve months in totalIn order for this to work you will need to have at least 23 months of data in the view so that your first month of the time you are interested in has sufficient preceding months that the table calculation can be applied to. Something like this:
In this example, the year we actually want to show is only 2021 (purple) but in order to calculate the Last Twelve Month running total we need also the values starting February 2020 so that as per January 2021 we got twelve rows of data (including January 2021) at hand. January 2021 LTM is the sum of February 2020 through Jan 2021 included.
So, how do we get rid of the 2020 values?
Idea 1: Dimension filtering
Sadly, not an option. Since dimensions come before table calculations, filtering out 2020 would remove the rows needed for our table calculation thus obstructing it.
Idea 2: Using HIDE
We can mark the rows we want to hide, then right click and select “hide”
Seems to do the trick, right?
Idea 3: Using table calculations
Another idea is to use table calculations which do not really filter but rather hide the data. Something like this
//filter_last
last() < 12Put this on the filter, set to true and you will get exactly the same result as in the previous screenshot.
CAVEATS
1. The problem with hiding
Hiding has it’s limitations in that it is usually static. In the screenshot above, we manually hid February 2020 to December 2020.
Now let’s see what happens if we keep that but move our assumed “today” one month forward, i.e. we would now want to see February 2021 to January 2022 in our table with their appropriate LTM values
Too bad. We added January 2022 but January 2021 is still on the view because the hiding is static.
But we had another trick up our sleeve, right?
2. Table calc based hiding and totals
We get what we want by just keeping the last filter:
But if we add totals from our analytics pane we get empty fields for those months that should actually be hidden but we see their totals.
Clearly not what we want!
This effect does not happen when using “hide” but as we saw previously, hiding leaves us with other problems.
So, our solution needs to make use of the “hide” function since this works with totals but also be dynamic as table calculations are.
Creating a dynamic “hide” Option
UPDATE:
Previously, I had shared my own method here which worked but was a bit more of a hassle. The following solution was shown to me by Tableau Visionary and fellow Ambassador Diego Martinez and is much (!) easier and straight forward.
To achieve our goal all we need to do is to put our [filter_last] field that we created on colors.
As can be seen, this gives us true and false and our table looks like this (extract):
Right click “FALSE” and hide it.
Then you can change the filter from color to details or simply adjust the color as you see fit.
Note that you cannot move the filter to the rows and do the same as this will remove our totals due to this error message:
Let’s add totals. Still looking good!
And if we play with our parameter? Let’s set it to April 2022
WORKS! We still only see twelve rows and we got the totals! Heureka!
For comparison, here are the values without hiding or table-calc-filtering:
Exactly the same values.
Find my workbook on Tableau Public.
And that is is for today. I hope you found that useful.
As always appreciate your feedback.
Until next time.
Steffen
Tableau Adventurer 