4 Replies Latest reply on Apr 2, 2018 2:51 PM by Vandana Samtani

    Date Range Question

    Vandana Samtani

      Hi guys,

       

      I am using a date range filter on my dashboard  and the data is not always current.

      Sometimes the data is delayed by 2 or 3 days and  I have to update my calculations by adjusting the days below in "Date Conversion Start"

       

      Is there a solution where this can be dynamic?  Any suggestions would be helpful.

      Just a note the dashboard is always rolling 30 day. All the metrics have the rolling 30 day filter set to 1.

       

      Date Conversion Start 

      DATE(if [Select Date]="Select Start Date" THEN (DATEADD('day',-33,TODAY()))

      ELSE DATE(dateparse("MM/dd/yyyy",[Select Date]))

      END)

       

      Date Conversion Today

       

      IF [End Date] = "Today" THEN TODAY()

        ELSE DATE(dateparse("MM/dd/yyyy",[End Date]))

       

       

      Rolling 30 Day

      if [Date]>= [Date Conversion Start] and [Date] <= [Date Conversion Today] then 1 else 0 END

       

      Below screen shot is only having 27 days. Since the last date of data is 3/26 the start date should be 3/326 -30 = 2/24

       

       

       

      END

       

      Thanks,

      Vandana

        • 1. Re: Date Range Question
          Jim Dehner

          Hi

           

          I think I understand that you want to look at 30 days prior a date - it sounds like you have 3 end dates in play - 1 is Today(), the second it the Max date on data in the data source and the third is a user input date via a parameter - given the 3 dates is seems like there are 2 conditions - the first is to use the end date as the

                              MIN(today(),{fixed :Max(date)}) it is either today or the max date in the data set - whichever is less - that is the end date and you roll back 30 days from it

           

          The other condition is when the user manually puts in a different end data and you can check that to make sure it is less than the max data is the data set and if it is just look back 30 days - if it is not then roll back 30 days from the max date in the data set

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Date Range Question
            Vandana Samtani

            Thanks Jim! Can you please send me a sample work book of how it works? OR Please update the attached workbook. Thanks, Vandana

            • 3. Re: Date Range Question
              Jim Dehner

              give it a try and send me what you get - I''' look at it then

              Jim

              • 4. Re: Date Range Question
                Vandana Samtani

                Hi Jim, I got the answer.

                 

                DATE(if [Select Date]="Select Start Date" THEN (DATEADD('day',-29,[Min Max Date]))

                ELSE DATE(dateparse("MM/dd/yyyy",[Select Date]))

                END)

                 

                IF [End Date] = "Today" THEN [Min Max Date]

                  ELSE DATE(dateparse("MM/dd/yyyy",[End Date]))

                END

                Thank you very much for your help,

                Vandana