Select Latest Date by Default in Discrete Date Filter

Version 1

    Many people have had the problem with being unable to have dynamic defaults set for filters (e.g. select latest date). There is, however, a roundabout way to arrive at this solution. After encountering this exact need, I found that it is caused by the value being 'set,' with any changes to it's current value resulting in an error, and new values are not automatically selected. I figured out a way through renaming the most recent value in the dataset and keeping it constant in the filter. Dates need to be converted to a string so that the selected value can be kept constant, with the underlying data changing instead. This works around the filter kink currently present in Tableau.

     

    A discussion on this topic can be found here: Use most recent date (max date) on discrete Quick Filter after extract update (if only one date is selected by default)

     

    Strings Attached

    - Converts dates into a string in calculated field

         - i.e. converts to discrete

    - Date format needs to be UNIVERSAL as the order needs to be sorted (because it's a string)

    - Only works with discrete filters (e.g. Multiple Values (List)), not continuous such as the 'Range Filter' (although a slider is available)

    - Only works if you want the filter to only be defaulted to the most previous time period - not defaulted to multiple selected periods.

     

    Please see the attached Packaged Workbook.

     

     

    Step by Step Guide

    1. Created a calculated field called DateFilter

    max_date_calc_2.PNG

     

    Copy Paste:

    IF

        [Order Date] = {MAX([Order Date])} //If the order date is the most recent date in data set

    THEN

        "Latest Date"  //Then set as "Latest" for quick filter.

    ELSE //else show the Order Date (need to convert to strings to get around the fact that the

    //data type for this calculated field is a string. Still works fine, though.

     

     

    //date in universal format so sorting works properly

        str(datepart('year',[Order Date])) //day

         + "-" +  //concatenating

        str(datepart('month',[Order Date])) //month

         + "-" + //concatenating

        str(datepart('day',[Order Date])) //day

     

     

        + " (" + //add pretty date in brackets after for user experience

        str(datepart('day',[Order Date])) //day number

        + " " +  //concatenating

        str(datename('month',[Order Date])) //month name

        + " " +

        str(datepart('year',[Order Date])) //year

        + ")"

    END

     

    2. Add to Filters Shelf and Select 'Latest Date' as default filter

    filter_maxdate_2.PNG

     

    3. Right click on DateFilter in the Dimensions pane, then go to Default Properties > Sort > Descending > Data Source Order

    3.1 Note that sorting Alphabetic won't work, as it will put December (12) just above January (1). Sorting via Data Source Order works for me, but may not work for you.

     

    BEFORE EXTRACT REFRESH

    before.PNG

     

    AFTER EXTRACT REFRESH

    Notice the latest date is still selected, and the dates below the selected filter have changed.

    after.PNG