LOD Rework 1 – Relative Date Filter

The other day I read a great post from Andy Kreibel (Tableau Tip Tuesday: Using a Set to Create a Relative Date Filter) where he showed a clever technique for simulating a relative date filter. His solution is based on sets and produces a filter that is anchored off the last observed date rather than the current system date. Very cool.

I’m sooo in love with LOD expressions at the moment, to the point where there is danger of me becoming “the LOD guy” as opposed to my previous reputation of “the guy who drinks too much at company parties”. Consequently I looked at this problem through my Tableau 9 glasses and realised this was a case where LOD expressions make the solution much simpler.

Here is the dashboard I created where the user can select the range of data they want to see:

And here is the underlying solution – with Tableau 9 we can express the filter logic in a single calculation:

Check out the last part of the calculation (click to see a bigger view). Notice the { } around the max() function?

{max([Order Date])}

This make it a LOD expression – but a special case of the FIXED type. Wrapping an expression in curly braces without setting a scope means calculate it for the entire dataset, ignoring all dimension and measure filters. So in this example the expression returns the last observed date in the entire data set.

Neat, huh?

I think this is a great example of how LOD expressions will help Tableau users remain focused on the question they are asking rather than having to break flow and think about how to manipulate Tableau’s mechanisms to achieve an outcome. The filter is now direct and explicit – return only those records where the Order Date is greater than the last observed Order Date, minus the required number of intervals.

And that – right there – is why I’m in love.

Hi. I'm Alan. By day I manage the APAC sales engineering team for Snowflake Computing. By night, I'm a caped crusader. Or sleeping. Most often it's sleeping.
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to LOD Rework 1 – Relative Date Filter

1. PS: Here’s the TWBX of the solution if you are interested.
https://dl.dropboxusercontent.com/u/3987438/For%20Blog/Example.twbx

Of course, you’ll need to be on the Tableau 9 beta program if you’re reading this now. Or maybe you’re a time-traveller come back after the launch. Tell me, how was it? Let’s talk about the Lotto numbers…

2. vizpainter says:

Love it! One thing to note is that {max([Order Date])} is the max date for the data source — but it is context sensitive (just like computed Sets), so it will be limited to the set of data defined by context filters. This really opens up some possibilities in analysis.