January 24, 2014
Tableau Tip: Showing all dates on a date filter after a Server refresh
calculation
,
dates
,
filter
,
Joe Mako
,
joemako
,
parameters
,
primary
,
query
,
secondary
,
tableau
,
tips
,
tricks
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:
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.
And you built a cool dashboard with a date slider like this:

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.

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:
Step 1: Create a parameter that allows two options and show the parameter control.

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.

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.

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.

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.

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.
I saw on this thread that it’s still an unsolved problem. In a nutshell, here’s a summary of the problem:
- You created a dashboard that has a date slider quick filter and you’ve told Tableau to use all dates.
- 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.
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.
And you built a cool dashboard with a date slider like this:
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.
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:
- Republish the workbook every day.
- Tell your users that they have to adjust it themselves every day.
Step 1: Create a parameter that allows two options and show the parameter control.
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.
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.
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.
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.
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.
Subscribe to:
Post Comments
(
Atom
)
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.
ReplyDeleteJon, you are right, that is another option. The steps to create that are:
Delete1. 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.
We were just playing with this and found that it only works when the Date field is Continuous as opposed to Discrete.
DeleteJoe,
DeleteI 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
Here is another alternative: http://community.tableausoftware.com/docs/DOC-5234
ReplyDeleteFantastic!!!!
ReplyDeleteSweet! I've been waiting for a solution to this...thanks!
ReplyDeleteIs 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@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?
ReplyDeleteThanks.
I am having a similar issue where this solution will not work on server. I am getting the below error:
DeleteTableauException: Cannot blend the secondary data source because one or more fields use an unsupported aggregation.
We were facing a similar issue today and a simple workaround worked for us.
ReplyDeleteEdit 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.
@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..
DeleteI tried some variations of the above design and found an oddity.
ReplyDeletehttp://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.
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
ReplyDeleteOpen the filter, choose the last tab, and pick All Values.
ReplyDeleteHow you know what problem I am going to face and write a blog about it.
ReplyDeleteGreat work.
Thanks! :)
Hi Andy,
ReplyDeleteCan you please explain discrete Date quick filter dynamically update and change to the most recent value when new data is added.
Thanks
Do we know if there will be a fix for this in Tableau 9?
ReplyDeleteNot 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.
DeleteThanks Andy! I'm looking around for the video but cant seem to find it. Do you have a link?
DeleteGina, 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.
DeleteAwesome. Thank you so much!
DeleteThat 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.
ReplyDeleteAndy, 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?
ReplyDeleteBrian, 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.
DeleteThe other blog post you referenced uses a parameter instead of actions. It depends on which behavior you prefer.
Hi,
ReplyDeleteI 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.
Nice work Jenny! Your method is similar to what I showed during the Hack Jeopardy presentation at TC14.
DeleteI 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
ReplyDeleteSri, it's very difficult to understand your situation without a more specific example.
DeleteI never expected to have a reply this immediate. thank you so much and greatly appreciate your help.
ReplyDeleteI 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.
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.
ReplyDeleteThanks 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.
ReplyDeleteThere's no solution yet for discrete dates that I'm aware of.
DeleteThanks for responding.
Delete