2 Replies Latest reply on Jan 13, 2016 10:54 AM by philip.thornton

    Create Custom Date Filter

    philip.thornton

      In my DB view (Windows SQL Server) I created a field (inCurrentFiscalYear) that flags each row with if it's inside the current fiscal year or not. 1 if yes, 0 if no.

       

      In Tableau I want to create a date filter that restricts the end user to 4 options.

       

      Last 3 Days

      Last 30 Days

      Last 140 Days

      Fiscal Year

       

      I created a dimension called "Custom Dates" that looks like this:

       

      IF [date_field] >= TODAY()-3 and [date_field] <= TODAY() then 'Last 3 Days'

      ELSEIF [date_field] >= TODAY()-30 and [date_field] <= TODAY() then 'Last 30 Days'

      ELSEIF [inCurrentFiscalYear] = 1 then 'Fiscal Year'

      ELSEIF [date_field] >= TODAY()-140 and [date_field] <= TODAY() then 'Last 140 Days'

      END

       

      Notice the expected order is not followed. I created it like this because if I put fiscal year at the end, it won't show up. Why this happens, you got me.

       

      So then I made "Custom Dates" a filter.

       

      At first glace it works as I had hoped however after looking deeper, the dates pulled are not correct.

       

      "last 3 days" works

      "last 30 days" is displaying today-33 to today-3

      "fiscal year" looks to be returning today-140 to today-31

      "last 140 days" looks to be returning everything <today-140

       

      ?!?

       

      So I've been looking online for other solutions and not finding how to build a custom date range like the above. Any assistance would be much appreciated.

       

      Philip

        • 1. Re: Create Custom Date Filter
          Carl Slifer

          Howdy Phillip,

           

          Good news Tableau has not broken. It's doing exactly what its told to do. Programming languages read through an if/then statement and the first time they have a match they exit. In this case things exit because they met a criteria. This sucks for us because we need things that overlap.

           

          We get around this by creating a parameter that references individual calculations, just like this link: Swapping Measures Using Parameters | Tableau Software Or... what should be a step by step below.

           

          [Last 3 Days]

          IF [date_field] >= TODAY()-3 and [date_field] <= TODAY()  THEN 'Include' Else 'Exclude' END

           

          [Last 30 Days]

          IF [date_field] >= TODAY()-30 and [date_field] <= TODAY()  THEN 'Include' Else 'Exclude' END

           

          [Fiscal Year]

          IF [inCurrentFiscalYear] = 1 then 'Fiscal Year' THEN 'Include' Else 'Exclude' END

           

          [Last 140 Days]

          IF [date_field] >= TODAY()-140 and [date_field] <= TODAY() THEN ''Include' Else 'Exclude' END

           

          Now we create a parameter [Date Filter]

          Change the parameter type to string and list then allow for the categories

          Last 3 Days, Last 30 Days, Fiscal Year, Last 140 Days

          Right click the now created parameter and select 'Show Parameter Control' -wa-la filter in the top right corner.

           

           

          And the calculated field that does it all

          CASE [Date Filter]

          WHEN 'Last 3 Days' THEN [Last 3 Days]

          WHEN 'Last 30 Days' THEN [Last 30 Days]

          WHEN 'Fiscal Year' THEN [Fiscal Year]

          WHEN 'Last 140 Days' THEN [Last 140 Days]

          END

           

          Drag this field to the filters shelf and select 'Include'

           

           

          Cheers

          Carl Slifer

          InterWorks

          • 2. Re: Create Custom Date Filter
            philip.thornton

            Perfect. Followed your instructions and was able to set it up in minutes. Thanks for the help!

             

            Philip