January 24, 2014

Tableau Tip: Showing all dates on a date filter after a Server refresh

As I was preparing for my trip to Israel, the team posed an excellent question: Why doesn’t the date slider quick filter automatically show the latest date when the data is refreshed on Server?

I saw on this thread that it’s still an unsolved problem.  In a nutshell, here’s a summary of the problem:

  1. You created a dashboard that has a date slider quick filter and you’ve told Tableau to use all dates.
  2. The next day when the data source refreshes, the quick filter doesn’t automatically include the new date(s).  The slider has to be manually moved to the right to accommodate for the new data.

I didn’t have an immediate solution, so I did what I always do when I’m stuck…I asked for help!  That’s right, Zen Masters don’t know everything.  In fact, I don’t know anywhere near everything about Tableau.  I contacted my good friend Joe Mako and he graciously hopped on a join.me with me.  What I truly love about Joe is that he’s incredibly patient and wants to make sure that we work through problems TOGETHER.  Sure, he could have simply sent me a solution, but he wanted to make sure I understood completely.

Let me first explain the problem in a bit more detail.  Assume that you have a set of sales data for the first few days of the year.

image

And you built a cool dashboard with a date slider like this:

image

You publish the dashboard to Tableau Server and set the data source to refresh each night.  But when you look at it a couple of days later, you notice that the chart hasn’t changed.

image

You can clearly see that four more days should be included, but the slider is still stopped at January 10th.  Tableau does not automatically adjust the date slider; it stays at whatever dates were picked when you published the workbook.

That leaves you with two options, both of which are not satisfactory:

  1. Republish the workbook every day.
  2. Tell your users that they have to adjust it themselves every day.

Or maybe there is a workaround.  This is where Joe stepped in.

Step 1: Create a parameter that allows two options and show the parameter control.

image

Note from Joe: This allows us to default the view to including all dates, while enabling the interactor to decided to apply a date filter.

Step 2: Duplicate the data source (or alternatively create a data source that has only the dates since that’s all you need to make this work, or you could use custom SQL like “SELECT DISTINCT Date FROM Table”) 

Note from Joe: This will give us a data source with a valid date range, a kind of dynamically populated parameter.

Step 3: Unlink the Date field in the secondary data source.  Ignore any warnings. 

image

Note from Joe: This way there is no Group By clause sent in the query to the secondary date list data source.

Step 4: Create a calculated field that checks for matching dates.

image

Step 5: Replace the Date filter on the worksheet from the primary data source with the Date field from the secondary data source and show the quick filter.  Again, ignore any warnings. 

image

Note from Joe: The combination of this calculated field in the primary data source, and a filter on the Date field directly from the secondary data source, is where the magic happens. The filter on Date that we just placed will happen in the query to the secondary data source, then the aggregations to get the MIN and MAX Date in the secondary, and then the comparisons that happening the formula will happen after the results are returned from each data source. This is effectively enabling us to get the selection of the filter available to use in the calculated field.

Step 6: Add the date match calculated field that you created in Step 4 to the Filters shelf and set it to At Least 1. 

image

Note from Joe: The INT() in the formula turns the Boolean value into an integer because a Boolean cannot be Continuous and Tableau cannot filter on a Discrete Measure if it is not a table calculation.  So by changing the data type, we can make the pill a Continuous Measure and filter on it.

Step 7: Clean up the dashboard – Remove the quick filter that is showing, add the quick filter for the Date slider and show the parameter control.

When you publish the dashboard, be sure to pick All Dates from the parameter created in Step 1 above.  The Date slider will still not move, but at least we are now defaulting the dashboard to always show all dates and then the user can filter if they wish.

You can download the sample workbook here.