3 Replies Latest reply on Feb 6, 2013 3:51 PM by Dimitri.B

# Rolling date calculations

Hey Guys,

I have been working with some survey data and the next step is getting it to Rolling 3, R6, and R12 M calculations.  Ideally I want it set up as a trendline going from month to month with a percentage of yes answers Rolling X months back.  I can get the Rolling X calculation using this formula:

IF  DATEDIFF('month', [Month Of Interview], now())< X+1 then [Respondent Number] END

I do a COUNTD on that and get the number of answers for a certain question for the last X months which is great but I'm not sure how to get a reference date in for the now().  I can attach a book if necessary.

Thanks,

Kevin

• ###### 1. Re: Rolling date calculations

Kevin,

do you want to be able to specify any date instead of current date? If yes, then you can use a date parameter instead of NOW() in your formula.

• ###### 2. Re: Rolling date calculations

Dimitri,

Thanks for the help.  A parameter wouldn't be the solution I don't think I'll try and explain it a little better.  I'm working on mocking up some data because it is sensitive I can't use the original data set.  I want to have the Months across the columns and have a trend line where each value is a sum of the last X months.  So for R3 this is what I'd want:

• ###### 3. Re: Rolling date calculations

The easiest way is to use a table calculation, see attached workbook.

The formula here is: WINDOW_SUM(SUM([Sales]), -[X prev months], 0)

where[X prev months] is a parameter determining how many months (not counting current at the data point) are to be included in the sum. You will need to partition it appropriately for your data (Compute using...).