Show – Hide Filters
Often we will see a Forum question on how to "Filter the view without filtering the underlying data?" Really?
What's going on?
Load a data set into Tableau, open the Data Source tab, and look at the data – It is the complete data set
Now create a new worksheet with a simple chart
Open the Analysis drop-down and select View Data
The window that opens is the "Underlying Table" for the worksheet
The table is a subset of the full data set –
In Tableau, each worksheet has a unique underlying table. As you bring dimensions and measures to the rows, columns, and marks card they "Filter" the full data set – what is left is the underlay table for the worksheet and it is the only data from the data set you loaded available for that sheet.
So what –
When you add filters to the viz it does affect the underlying table so it is not possible to "Filter the view without filtering the underlying data" but it is possible to use a table calculation to Hide data from the viz.
I like to think of it as creating a viewing window. Data that is in the window can be seen – that outside the window is hidden
Lookup() Function
This example uses Superstore data and presents the running total of Sales by Category over all the Months-Years in the data set – It looks like this
Now you only want to show the running total for 2019 but you want to include the data from 2016-2018 ("Filter the view without filtering the underlying data").
Using Lookup() we will create a window based on the custom format at the year level of the Order Date
Create a custom date on Year – we want to "Filter" by year –
Open the Date pill then Create and then Custom Date
When the window opens Select Year from the Drop Down and the Date Part button (make the date discrete)
Now use Lookup – a table calculation – to read the year of each date in the table individually – This works much like a Lookup in excel – in words it says " from the Order Date Years measure – return the value in the current cell" Note – the attr() all table calculation must be an aggregate
Then we add a conditional statement that will look at each value of the actual Order Date (Years) either assign it as Show or Hide
Create the viz.
The real order date goes on columns and the running sum of sales on rows
Add the Lookup Filter to the filter shelf and set it to "Show" and calculate across (or by Order Date)
and this is the result
The "window" is hiding January 2016 through December 2018 but allowing the 2019 data to be seen. The date is not filtered out – the lookup filter window just hides the value you don't want to see
Last() Function
Frequently a user wants to compare the latest sales to the prior year's same period – but they only want to see the last few weeks (or months). A solution is to use Last() – another table calculation – to create a dynamic viewing window –
To show only the last 6 weeks create a "Last Show/Hide Filter"
Then create the viz
They can be any level of discrete date parts – add a year filter to only include current and last year and set the Show/Hide filter to Show and the window will include the last 6 data part values – here weeks
The viz will dynamically adjust to the last 6 weeks when the database is updated
These are just 2 examples there can be many more.
It is important to remember that table calculation are used to create the viewing window. They are at the bottom of the Order of Operation (a topic for another day) and work on the final fully filtered table that underlays the specific worksheet
Hope this helps
Jim