Base Relative Date Filter on Latest Date in Data

Version 5



    So Tableau has this great thing called "Relative Date Filter" that is loaded with options for the end user.....But it doesn't really work unless you have data that is relative to TODAY.


    Here is an Example with Superstore (Max Date December 31st 2014)


    11-20-2015 3-54-18 PM.png


    Wouldn't it be great if we could do this instead


    11-20-2015 3-55-04 PM.png



    Example Calculation:


    DATEADD('day', DATEDIFF('day', {FIXED : MAX([Order Date])}, TODAY()), [Order Date])


    Since the Relative Date Filter works based on TODAY, we can simply adjust our data to make it relative to TODAY, via an LoD Expression.


    Essentially all we are doing is finding the difference in Days between the MAX Date in our Data Set and TODAY, Then we add that difference to all dates so they adjust equally across the board.


    The thing to remember is that we are Filtering on a Calculated Field, but we are displaying the Actual Date Field.


    Idea inspired from this thread "Click to show all values" for relative date filter?



         This method is NOT perfect by any means. And you can possibly run into problems depending on the level of Truncation of the Date being displayed, vs the level of Truncation of the Date being filtered on. After messing around with it a bit, I found that there are less issues by altering the Calc, and the filter to Anchor on a Date that is off into the future. For example.


    DATEADD('day', DATEDIFF('day', {FIXED : MAX([Order Date])}, #2050-12-31#), [Order Date])


    11-30-2015 8-13-25 AM.png


    This can cause an additional Period to be displayed, depending on the DAY of your MAX Date, because of Difference in Days causing an addition Display Month to appear because of the DATEDIFF.


    This can be further mitigated by Aggregating the Relative Filter so that Truncation of the Date being Filtered happens AFTER the original Date (The date being displayed in the viz). However, this can limit flexibility.


    At the end of the day, the better solution would be a Dynamic Anchor for Relative Filters.



    Hope this helps.