4 Replies Latest reply on Jun 5, 2018 7:01 AM by john white

    Making Relative Date Filters More Dynamic

    john white
    john white May 17, 2018 2:44 PM

      Issue is i want to use relative date filters as a way to filter data and keep the dashboard up-to-date.

       

      The issue is that my data is not real time and is always today() -1.

      My clients want to see the dashboard change ever month; however, with the data being today()-1 there will not be any data for the new month until the 2nd.

      Is there anyway to control the relative date filter and make it look at the max data date oppose to using a standard calendar?

       

      I have seen post about creating parameters to control the anchor but that still leaves the issue of the updating the dashboard. I have also seen where there is a slider/type in filter that allows the user to select ' last n days' which will not work due to the length of time my data stretches.

       

      Any solutions?

       

      Thank you

      • 137 Views
      • Categories: Actions & Filters, Calculations, Dates & Times, Views & Dashboards
      • Tags:
        • 1. Re: Making Relative Date Filters More Dynamic
          Bryce Larsen
          Bryce Larsen May 17, 2018 7:23 PM (in response to john white)

          Hi john white!

          You are correct - you can look to use a parameter where the user can first select from here: Most Recent or Custom, and after click Custom they can then use a Date Picker.

           

          I first find the Max Date in the data using a LOD expression. If they select Most Recent, I use this field, otherwise I use the Date Picker (which I compared to Max Date to ensure it's not more recent than the max date - if so, I "snap" it back to the Max Date.

           

          Screen Shot 2018-05-17 at 10.20.09 PM.png

          I've attached an old date template workbook for reference. I'm sure I've learned new/better ways since doing this, but figured it might help!

           

          Best,

          Bryce

          • 2. Re: Making Relative Date Filters More Dynamic
            john white
            john white Jun 4, 2018 2:32 PM (in response to Bryce Larsen)

            Bryce, Thank you this is a solution but would not fit quite what i am looking for due to the fact my clients like to be able to still have the ability to fully customize their data and not have it set on the most current looking back. For example, they may still only want to see 2 months ago data and nothing else. This did spark my interest and I came up with a solution to use the current month and parameters together

             

            The below will allow the user to select whatever date they want to use to get the current month whether their data is today () -1 or any lag. This calculation tells the tableau based on the selection which calculation to use and whether to activate the parameters or not. When Current Month is selected, the parameters are not active and only when the custom is selected they become active.

             

            IF [Date Selection] = 'CM' THEN

            IF [Date]>=DATETRUNC('month',today()-1) and [Date]<Today()

            THEN 'True' END

            ELSEIF [Date Selection] = 'Custom' THEN

            IF [Date]>= [Start Date] AND [Date]<= [End Date]

            THEN 'True'

            END

            END

             

            Thanks Bryce for putting together solid logic to build on. Hope this helps if you want to use this also.

            • 3. Re: Making Relative Date Filters More Dynamic
              Bryce Larsen
              Bryce Larsen Jun 4, 2018 5:24 PM (in response to john white)

              Glad to hear it!

              Might add one suggestion that is to make the date filter just be a boolean rather than returning a string - this coulD help slightly improve performance. All the best!

              1 of 1 people found this helpful
              • 4. Re: Making Relative Date Filters More Dynamic
                john white
                john white Jun 5, 2018 7:01 AM (in response to Bryce Larsen)

                That is a great suggestion and i honestly did not think i could do it that way. Thanks again