2 Replies Latest reply on Feb 12, 2019 2:58 PM by Kelly Kirkpatrick

    Creating Calculating Fields for Last 28 days, 13 weeks etc w/ end user selecting their date from a parameter

    Kelly Kirkpatrick

      My end users are able to select their dates by using a parameter control.  This has been created by: Last Month, Month to Date or Custom Date - if they select Custom Date, they enter a range from another parameter.

       

      I'm trying to create a new view that will show (based on the time selected) what the data was for the Last 7 days, Last 28 days, Last 4 weeks and Last 13 weeks.  I'm running into the issue if a user selects "Month to Date" (Feb 1 - Feb 10) - the Last 7 days works, but last 28 Days doesn't - it stops at Feb 1st.

       

      Here are my calculations:

       

      This the calculation for them to select what days they want to look at:

       

      SUM(IF [Select Time Frame] = 0 AND DATEPART('week',[Filter Date]) = {FIXED : MAX(DATEPART('week',TODAY()))}

       

       

      AND DATEPART('month',[Filter Date]) = {FIXED : MAX(DATEPART('month',TODAY()))}

       

       

      AND DATEPART('year',[Filter Date]) = {FIXED : MAX(DATEPART('year',TODAY()))}

       

       

      THEN 1

       

       

      ELSEIF  [Select Time Frame] = 1 AND DATEPART('month',[Filter Date]) = {FIXED : MAX(DATEPART('month',TODAY()))}

       

       

      AND DATEPART('year',[Filter Date]) = {FIXED : MAX(DATEPART('year',TODAY()))}

       

       

      THEN 1

       

       

      ELSEIF [Select Time Frame] = 2 AND  DATEPART('week',[Filter Date]) = {FIXED : MAX(DATEPART('week',TODAY()))} - 1

       

       

      AND DATEPART('month',[Filter Date]) = {FIXED : MAX(DATEPART('month',TODAY()))}

       

       

      AND DATEPART('year',[Filter Date]) = {FIXED : MAX(DATEPART('year',TODAY()))}

       

       

      THEN 1

       

       

      ELSEIF  [Select Time Frame] = 3 AND DATEPART('month',[Filter Date]) = {FIXED : MAX(DATEPART('month',TODAY()))} - 1

       

       

      AND DATEPART('year',[Filter Date]) = {FIXED : MAX(DATEPART('year',TODAY()))}

       

       

      THEN 1

       

       

      ELSEIF [Select Time Frame] = 4 AND [Filter Date] >= [Start Date] AND [Filter Date]<= [End Date] THEN 1

       

       

      END)

       

      Here is what i have created to get Last 28 days (remember I want to have this all in one view - similar to an excel chart)

       

      DATEDIFF('day', [Filter Date] , {MAX([Filter Date])} ) <= 27 - this gives me true/false

       

      IF [Trailing 28 days] = True THEN [Transient Rooms] END

       

      I put this in the view, as I mentioned - this only gives me data through Feb - it doesn't go back to January to really give me the last 28 days

       

      The same thing happens if I selected "Last Month" - it starts the 28 days from today vs. Jan 31.

       

      Any ideas?  I'll try and copy a workbook, but i need to clean it up first.