Data Viz Done Right

September 5, 2013

Tableau Tip: Default a date filter to the last N days

22 comments
A common question that I see on the forums and on our Facebook group is “How can I default my date quick filter to always show the last N days?”  This is a relatively simple problem to address using parameters instead of quick filters.  This solution also works because you’re not dependent on leveraging the today() function.  This works because it looks at the last date in your view, not your computer’s clock.
If you use quick filters, when you publish your workbook to Tableau Server, the latest view that you see in Desktop is what gets published.  So, if you use a date range quick filter like this

image

and your data source refreshes overnight, the slider does not automatically shift to the right.  In addition, the slider also shows dates all the way up to today, even if your latest date is one month ago.  Hence, if you filter to the last two weeks you could get no results, leaving your viewers a bit perplexed.
Here’s the method I use that always defaults to the latest N days.
Step 1 – Create a parameter that allows the user to input the last N of days they want to view.  Then show the parameter control.

image

Step 2 – Parameter controls don’t do anything in Tableau unless you created a calculated field to leverage them.  In this case, let’s create a calculated field that flags days that are within the range entered in the parameter.

image

Let’s check if this works by creating a simple table.
image[66]

Perfect!  We can see that our “Days to Show” field shows false when the date is more than 7 days from the last date.

Step 3 – We don’t need our “Days to Show” field in the view and we only care about the “True” results, so let’s move it to the Filters shelf. Select True, then change the Computing using to Order Date.  Yes, I could use Table (Across), but I prefer to have more control.

image

The filter selection will appear again. Choose True.

Step 4 – Build your visualization.  First, let’s build a line chart.
 
 
Next, let’s build a bar chart for sales by category and sub-category.  This is where it gets a bit trickier.  In order for your “Days to Show” filter to work, you must keep your Order Date field in the view.  Move it to the level of detail.  The result looks like this, with stacked bars for each date.  I then sorted sub-category by sales descending.

 
Step 5 – Clean up the bar chart by removing the borders.  Click on the Color shelf, then Border and choose None.

image

Your bar chart now looks like this:

image

Step 6 – Put it all together on a dashboard and add some interactivity.



One additional cool benefit is that as you use the action filters (e.g., click on Envelopes), you will see the last N days based on that selection.
 
You can download the workbook used to create this blog post here.  My next blog post will take this one step farther…calculating cumulative totals for the last N days.

22 comments :

  1. Andy - this is a nice tip and a clever way of doing something but why not just use Relative Date or Browse Periods to do the same thing? Maybe I missed a nuance of something more specific you're trying to acheive.

    ReplyDelete
    Replies
    1. Andy, relative date filters do not work well if your data is old. For example, the superstore sales data set that I have ends on Dec 31, 2012. If I use a relative date filter set to the last two weeks, I don't get any results.

      Delete
    2. You can Anchor dates with a relative date filter. I use them in my twitter vizzes. The feature is visible when you right click on the pill in the filter shelf

      Delete
  2. Andy, I don't think this is giving the previous n days, or what people typically expect from a "show me the previous n days"-type of filter. Rather, what you are getting is the previous n occurrences where there was a sale, and some of those sales my go back weeks or months. To see what I mean, see what happens when you select Rubber Bands from the bottom visualization and notice the dates that appear in the top visualization.

    Given that we are seeing the previous n occurrences I also think displaying these as discrete dates and not continuous makes much more sense.

    Finally, I instead of using last I would instead find whatever the maximum date is within the partition and subtract the number of dates from that.

    Steve

    ReplyDelete
    Replies
    1. Thanks for the comments Steve. Joe and I have been having the same conversation offline. In the case I was writing about, the concern was the last N occurrences so I should have been more explicit.

      Delete
    2. This comment has been removed by the author.

      Delete
  3. Nice one! 'Most Current Month' (even though the most current month isnt always this month) is pretty common.

    ReplyDelete
  4. Andy, thank you. I have a very specific need to show only the MAX date available within the dataset as it changes each day. In my case, based on the MAX Date shown, i bring back data for that day plus data for the previous day. So it's sort of like your example using LAST() < [Last N Days]

    Where [Last N Days] = 2

    I can bring back last 2 days, or last 2 weeks, or last 2 months, depending on the data. This works really efficiently for showing a default view based on a dynamic "Last N". What if, however, the users also need the ability to select their own date?

    ReplyDelete
  5. Hi Andy - I need to select the Last 7 days based on the MAX(date). The date field in my data gets populated with the next day mid-day of the current day. So for instance, Today is Nov 15. If I run my report for last 7 days, I get data for Nov 9 through Nov 15. However, around 1pm, my date field gets populated with data for Nov 16. When I run my report now, I WANT to see Nov 10-Nov 16, but it shows Nov 9 to Nov 15. Just curious if you have seen something like this and perhaps have a solution. I have not been able to find a solution. Thanks

    ReplyDelete
    Replies
    1. This calculation would give you the last 7 days of sales based on a MAX(Date) field.

      if [Order Date]>=DATEADD('day',-6,[Max Date]) THEN [Sales] END

      Delete
    2. How do you mix the aggregate field (MAX(Date)) and non aggregate field [Order Date]?

      Delete
    3. Tom, in this case, the Max Date is a dimension coming from the database so I'm not mixing aggregate and non-aggregate dates.

      Delete
  6. Awesome use of parameters. Thanks a lot

    ReplyDelete
  7. Hi Andy,

    In my case, there are more than one row for the same date (multiple timestamps per day). In that case LAST() < [Last n days] is pulling only the last 7 rows which is sometimes the same date. I would need to see all the rows for the last 7 dates. How do i achieve it ?

    Thanks

    ReplyDelete
    Replies
    1. Can you create a sample of the data and upload a workbook with your situation? That'll make it easier to diagnose.

      Delete
  8. Andy,

    I imported only the required data(due to NDA) and uploaded it here.
    https://public.tableausoftware.com/views/Book4_1252/Sheet1?:embed=y&:display_count=no
    As you can updated_At field contains more than one row for the same date. Please let me know if you have any questions

    Thanks
    Karthik

    ReplyDelete
  9. Hi Andy,

    Great Work.

    Here I have one question.I am doing table calculation to show percentage difference of Sales based on the date field.Here's the calc field which i have used

    (LOOKUP(SUM( [Sales]),LAST()) - SUM([Sales] ) ) / SUM([Sales] )

    I have used the last N days calc.Here my qusetion is I want to hide the last date in the view every time automatically.i.e, For example,Assume that I am showing Sales for june,july,aug,sep,oct for year 2014 and i am using the above table calc to show Percentage difference of sales compared with oct.Then, for oct it will show 0%.I don't want to show oct in the view.For this I have used the calc

    LAST()-1 < [Last n months]

    I had manually hided the oct month then when we type 3 in parameter control it will show last 3 month sales (july,aug,sep).Like in the same way if november sales also added into the view,I had manually hided the nov month and unhided the oct month then when we type 3 in parameter control it will show last 3 month sales (aug,sep,oct).
    So for everytime when new month is added I'am doing hide manually. Is there any automation for hiding the last month sales in the view ?

    ReplyDelete
  10. Hi Andy

    If there a way to choose the last date also?
    In my case my months are called periods for instance January is P1 or period 1
    I have created a parameter control for all the periods (months) to filter different periods across different sheet from different data sources on my dashboard.

    Now i want my graph to show last 13 periods from the selected period in the parameter.

    I used the above explanation but the graph is showing me last 13 periods from the maximum period available.

    ReplyDelete
    Replies
    1. Kanika, without seeing you workbook or data, my guess is that you could use a lookup function or last function and have it keep values based on that.

      Delete
  11. Hi, This example helped me but I am looking to filter last N days on selection on of date in date parameter. For example I have date parameter in a calendar view, if I select today's date it should view me last 7 days, else if I select yesterday's day it should view me last 7 days from the date I have selected. Please help on working on this

    Thank you

    ReplyDelete
  12. Hi Andy,

    Is there a way to re-purpose this formula this formula? I am trying to find a way to create two time periods.

    1st time period - all data in the past 30 days from today

    2nd time period - all data 30 days past the last date of the first time period

    Please let me know.

    Thanks,
    Anna

    ReplyDelete
    Replies
    1. Hi Anna. Try this method. LOD calcs would make this even easier.
      http://www.vizwiz.com/2014/11/kpi-and-sparkline.html

      Delete