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

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.

• ###### 1. Re: Creating Calculating Fields for Last 28 days, 13 weeks etc w/ end user selecting their date from a parameter

Hi, Kelly

What's [Filter Date], is it a calculation or field? and how the parameter works as interactive? It will be easier to help with a sample workbook.

ZZ

• ###### 2. Re: Creating Calculating Fields for Last 28 days, 13 weeks etc w/ end user selecting their date from a parameter

Hi ZZ -

I think I figured it out, I had to re add some of the metrics and then when I changed to ADD to Context it seemed to worked.  Thank you!