Data Viz Done Right

January 27, 2015

Tableau Tip Tuesday: Using a Set to Create a Relative Date Filter

2 comments
One of the most fun experiences I get to have when I run Tableau training classes is when a student comes up with a solution to a problem on their own.  Last week I was in Austin teaching a 2-day Tableau Combo class to the Facebook team there and a student was asking about relative date filters. His problem was pretty common:

  1. His data is not as of today.
  2. When he uses a relative date filter, he doesn't get the expected number of days in the view.

Here's a video of the problem and the solution, which uses a combination of a set and a parameter. The detailed steps are below the video.


The situation looks like this.  You have a set of dates that do not include the current date.


You then add a relative date filter expecting Tableau to give you the last 14 days.


Yet, this is what you get:


This is super frustrating because you told Tableau to give you the last N days, yet it applies the filter based off of the current date.  It's pretty easy to work around this by creating a set and a parameter.

First, right click on the date field in the Dimensions list and choose Create Set.

On the General tab, choose the "Use all" option and on the Top tab, choose the "By field" option, then choose Top, Order Date and Max for the aggregation.

  

In the box where you see the number 10, choose "Create a new parameter".  This will give the user the power to pick the number of days they want to view.  Set the parameter to whatever works for your situation.


Drag the Relative Days set to the filter shelf. Right click on the pill once it's on the filters shelf and choose the "Show In/Out of Set" option. Choose In from the check list.

Next, show the parameter and you now have a pseudo relative days filter.  Test it out in the dashboard below.



Download the workbook used to create this post here.

2 comments :

  1. It looks great! Thanks for your share Andy, by the way, can we change it to "Last N Month"?

    ReplyDelete
  2. Would like to complicate this. Let's say I have 3 data sources. Closed sales (past), Committed sales (current), pipeline (future). These are in 3 data sources. I want the user to be able select/filter Jan-Mar on Closed Sales, which will effect a filter (say April-May) on Committed Sales in view 2, and will filter on Pipeline (June-Dec) for the remainder of the year. I have been thinking about calculated fields that would determine InRange vs OutOfRange, but can I do that across data sources? Thanks!

    ReplyDelete