FAQ Series - Show - Hide Filters

Show - Hide Filters

Often we will see a Forum question on how to "Filter the view without filtering the underlying data?"  Really?  

What's really 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 its own 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 form 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 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 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 really 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 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" 

Adding a filter based on the last month date in the data base makes the viz dynamic for the YoY calculation

Then create the viz

The 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 - obviously there can be many more.

It is important to remember that table calculations 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