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.