When you’re using Tableau and it is back-ended with a very large data source (an enterprise data warehouse for instance) one of the issues is making the source data that you’re working with manageable. You definitely don’t want to be querying the whole thing every time you refresh a report (probably) as this makes the workbook slower than it needs to be and makes exporting a packaged workbook for others to use nearly pointless (if it will export at all).

One of the simplest things to do is limit the time frame for the report. For instance, maybe the report you’re working on only needs the last 30 days of data or the last 6 months or the last year.

But how do you do this in Tableau?

Beginners, myself included, will just drop the date field of choice onto the filter shelf and manually set the date range required. The problem is that every time you open the report you have to manually adjust your date filter so it’s not helping you as much as it could be.

Enter DATEDIFF. This function – as the name implies – calculates the difference between two dates at a specific granularity. That’s right, it will calculate the difference in dates by number of days, weeks, months, etc. How cool is that?

Ok, so how does that help create a filter that returns only the last N days of data?

It’s so simple you might think the above is a trick question some how.

Before I get started on directions, I’m going to assume that you’re already in your workbook and connected to your data source. For the purpose of this exercise, I’ll be using our friend – SuperStore Data.

1. Start by opening the ‘Create Calculated Field’ dialog box
2. Let’s call this new field ’30day-Filter’
3. Tab down (or click in) the ‘Formula Field
4. Type in DATEDIFF(
5. The first argument in DATEDIFF is the granularity to calculate with (day, week, month, etc). For our 30day-Filter we’ll use ‘day’
6. So far you should have DATEDIFF('day'
7. Arguments in a function are separated by commas so add a comma after ‘day’
8. The next argument is the start date (e.g. the oldest date) which will be the date from your data source record. In this case, the field we want is ‘Order Date’
9. Our new function should look like this so far: DATEDIFF('day',[Order Date],
10. For the end date, we’re going to use another function – TODAY(). TODAY() takes no arguments and returns the current date from your data source. If you’re connected to a remote data server TODAY() will reflect the date on the server. If you’re working with a local data source (text file, excel, etc) then TODAY() will reflect the time on your local machine
11. So far we have DATEDIFF('day',[Order Date],TODAY()) This is a valid function all by itself and will calculate the difference between the two dates with ‘day’ granularity, but it’s not a filter yet
12. In this case we want a filter that evaluates to ‘TRUE’ or ‘FALSE’ and really, it’s very simple. Just add ‘<=30' to the end of the function
13. DATEDIFF('day',[Order Date],TODAY())<=30
14. Save the calculated metric

notice that in the Dimension shelf your new metric has an icon of T/F. Now we're ready to use the filter! Go ahead and add it to your filter shelf.

A couple of last points on using this filter to make your report dynamic in terms of the date range. Make the filter global - remember the date filter should encompass all of the data you need for the report, and making it global makes it apply to all worksheets automatically. Add the filter to your context - this limits the data Tableau is working with on the server side which should improve the speed of all subsequent queries (less rows to traverse for an answer).

Tags: dynamic-time-frame, tableau-101

This entry was posted on Saturday, February 28th, 2009 at 1:31 pm and is filed under Tableau. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.