VizWiz

Launch, grow, and unlock your career in data

January 24, 2014

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

34 comments
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.



34 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
  11. Hi Andy,

    Can you please explain discrete Date quick filter dynamically update and change to the most recent value when new data is added.

    Thanks

    ReplyDelete
  12. Do we know if there will be a fix for this in Tableau 9?

    ReplyDelete
    Replies
    1. Not that I'm aware of. We did find an easier work around though which we demoed at our TCC talk. The video is on the blog.

      Delete
    2. Thanks Andy! I'm looking around for the video but cant seem to find it. Do you have a link?

      Delete
    3. Gina, go to the 23:15 mark in this video http://vizwiz.blogspot.com/2014/09/facebook-jeopardy.html. I just realized I haven't written this one up individually yet.

      Delete
  13. That does deserve an individual write up. Buried in an hour long video, is not good for search engine to index and for people to find.

    ReplyDelete
  14. Andy, do you know how to make this filter into an action on a dashboard? I've tried making an action with the filter made from a similar worksheet, but it won't commit the action to filter other worksheets on a dashboard. Would your other post here (http://vizwiz.blogspot.com/2012/06/create-global-filter-in-tableau-across.html) be applicable?

    ReplyDelete
    Replies
    1. Brian, when you need to pass an action filter to sheets that are from another data source, you need to specify the fields that are in common when you edit the action if the dimensions do not have the same name. If the dimensions DO have the same name, then note that Tableau filters the secondary data source based on the aliased names, so the names within the dimenions have to match exactly, just like they do in blending.

      The other blog post you referenced uses a parameter instead of actions. It depends on which behavior you prefer.

      Delete
  15. Hi,

    I am using Tableau dekstop and Tableau online. I did this way:

    1. Created a calculated field with [Date] < Today() ( in my case, I need the filer to be up to Today but you can change it to Max ([Date]) from the copied data source).
    2. Added the calculated field to the filter shelf and choose filter to be true
    3. Drag the [Date] to filter shelf and choose Range of dates. Do not change the range of dates and choose show only relevant values.
    4. In the dashboard, choose show quick filter - use the range of dates filter. Do not show the calculated field as quick filter.
    5. Publish the workbook to Tableau online.
    5. The dashboards in tableau online will have the rang of dates quick filter up to today, even though the dashboards in your tableau desktop will still show the range of dates as the value that you set it to be.For example, I created my dashboards on May 11 and published ti Tableau online on May 11. On May 12, my dashboards in Tableau online will show the range of dates filter up to May 12.But when I open my workbook in Tableau desktop, the range of dates filter will still up to May 11.

    ReplyDelete
    Replies
    1. Nice work Jenny! Your method is similar to what I showed during the Hack Jeopardy presentation at TC14.

      Delete
  16. I have been using this method in variations and pleasing the customers with their needs but something recently that's pulling me in is I'm not able to display the number of records or similar summary counts in the worksheet any idea or workaround is greatly appreciated. Thanks

    ReplyDelete
    Replies
    1. Sri, it's very difficult to understand your situation without a more specific example.

      Delete
  17. I never expected to have a reply this immediate. thank you so much and greatly appreciate your help.

    I have a "date filter?" calculated field that allows the worksheet to filter the records based on the parameter

    int(case [Select Date Field to filter ]
    when 1 then (
    MIN([Date Range (SERVICE_DESK)].[OpenDate_Range]) <= MIN(DATE([Open Date]) )
    and
    MIN(DATE([Open Date]) ) <= MAX([Date Range (SERVICE_DESK)].[OpenDate_Range])
    )
    when 0 then (
    MIN([Date Range (SERVICE_DESK)].[OpenDate_Range]) <= MIN(DATE([Last Modified Date]) )
    and
    MIN(DATE([Last Modified Date]) ) <= MAX([Date Range (SERVICE_DESK)].[OpenDate_Range])
    )
    end
    )

    the records gets displayed

    but in another sheet I wanted to display the count that's where the puzzle is the count gets displayed only when the date slider is in the beginning date of the range, any slider you slide away from the beginning range the count disappears.

    ReplyDelete
  18. I'll jump in with my own issue, if you guys don't mind. I have a sheet that shows data 14 days(rows) at a time. Each segment is a pay period. The filter has only pay period end dates and as time goes by, new dates get added. However, every time the sheet defaults to the date when the workbook was last saved. I'd like to have it so that the most recent date is selected by default.

    ReplyDelete
  19. Thanks for this blog. I see that a lot of people are asking about discrete dates with no response. Is it possible to create a quick filter that will default to the most current date, while allowing users to choose other dates.

    ReplyDelete
    Replies
    1. There's no solution yet for discrete dates that I'm aware of.

      Delete