DEV Community

Sneha Ashok Naik
Sneha Ashok Naik

Posted on

Tableau Date Filtering Using LOOKUP Shows No Data

Date Filters, is that spread on your sandwich. You've gotta have it. Occassionally, you dont!

If you happen to use LOOKUP() in your report along with a date filter, you are going to observe that the data goes null when you select Dates other than ALL . The solution for this by Tableau is available at this link.

However, I'd like to elaborate with an example.

Let's take YoY %Diff of Sales v/s MMM/YYYY. Pretty straight forward with tableau giving you a one click option. (Right click Sales > Quick Table Calculations > YoY Growth). The layout would look like below , along with the year filter (with All selected)
Alt text of image

Now, try selecting one of the years, and the layout just vanishes. See below:

Alt text of image

So, why does this happen you ask ? Table calculations - in our case : LOOKUP() ,needs to have all of the data in the view to work because it is referencing that data in other cells to make its calculation. But, when you apply the date filter the reference isn't available anymore.

The Workaround : Dynamic Hiding
Create a table calculation to replicate the data and then filter on this new Table Calculation.
Formula : LOOKUP(YEAR(MIN([Order Date])),0)
Turn this to discrete and create your filter. Remove the thousandths place by changing number format.To remove the 2010, add filter Sales<>Null in the filter shelf.

The Final layout is as below:

Alt text of image

Reference : Brandon Shonk

Top comments (0)