Jim Dehner 

 Tableau Visionary and Ambassador

Tableau How to’s,  Use Cases, and Forums Questions

Hey #DataFam

Let’s Talk

Open for

Post Links

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" 

 

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

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

Leave a Reply

Your email address will not be published.