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.

16 comments:

  1. If you never set the quick filter it will automatically default to all days. It will update each day to include the full range. The problem is once you set it, even if you set it to all days, it's frozen on that date range. You have to remove it and re-add it, or you can "clear the filter" I think.

    ReplyDelete
    Replies
    1. Jon, you are right, that is another option. The steps to create that are:
      1. make the worksheet view with the filter on Date.
      2. Place the worksheet on a Dashboard, Tableau will auto place the filter control for Date.
      3. Go back to the worksheet, and remove the Date pill from the Filter shelf.
      Now when you go back to the dashboard, the Date filter there will always be the full range of data in the data source.

      Delete
    2. We were just playing with this and found that it only works when the Date field is Continuous as opposed to Discrete.

      Delete
    3. Joe,
      I followed all 3 steps and, as you said, when I went back to the dashboard, the Date filter showed the full range of data in the data source.
      But when I refreshed my data source the Filter instead of the slider displayed "Not available".
      What am I missing?
      ~Leonid

      Delete
  2. Here is another alternative: http://community.tableausoftware.com/docs/DOC-5234

    ReplyDelete
  3. Sweet! I've been waiting for a solution to this...thanks!

    ReplyDelete
  4. Is there a way to auto-refresh a workbook, using a discrete Filter that only displays one "Term Week" at a time, rather than All Term Weeks? I would love to find a way to have a discrete quick filter auto update to ONLY the most recent date/Term Week.

    ReplyDelete
  5. @jon @joemako, I really like the thought of the simple workaround that Jon proposed. Problem is, I can't get it to work in 8.1.2 server. Does this trick work on desktop only, or have you seen it work on server?

    Thanks.

    ReplyDelete
    Replies
    1. I am having a similar issue where this solution will not work on server. I am getting the below error:

      TableauException: Cannot blend the secondary data source because one or more fields use an unsupported aggregation.

      Delete
  6. We were facing a similar issue today and a simple workaround worked for us.

    Edit Date/Day filter and select "Special" and then "All Dates".

    Now when workbook is refreshed when there is new data for a day then time slider moves ahead and selects all days.

    ReplyDelete
    Replies
    1. @Jasmeet: That will not help the current issue.. your solution will surely provide all the values however the slider will not set it self on the latest date available in the database..

      Delete
  7. I tried some variations of the above design and found an oddity.
    http://vizdiff.blogspot.com/2014/07/date-dimension-design-of-rolling.html

    My data set is from an Oracle database and is updated every day. As is told in your blog, I created a 2nd data source as the reference date and unlinked it from the main data source. The date slider is then entirely dependent on the 2nd data source.

    After server refresh though, the first option of “All Dates” is updating with the latest new dates. However the 2nd option is not. The right date-range boundary for the slider is not updating and remains the same. I understand that the pointer of the slider is not supposed to move even if the boundary changes.

    I wonder if the technique you described only works with static data source like Excel?
    Also I wonder if anybody here tried it with a dynamic data source.

    Thanks.

    ReplyDelete
  8. We have a similar problem, here we have "Number of Students" and number of students value changes after certain amount of time. We want slider to adjust automatically,tried above logic but not working. Please guide

    ReplyDelete
  9. Open the filter, choose the last tab, and pick All Values.

    ReplyDelete
  10. How you know what problem I am going to face and write a blog about it.
    Great work.
    Thanks! :)

    ReplyDelete