AnsweredAssumed Answered

Calculating Rolling 12 months

Question asked by sccardais on Feb 6, 2016
Latest reply on Feb 7, 2016 by @ppear

I'm having trouble calculating a value in Table A (MONTHS) that summarizes data in Table B (SURVEYS) if the SurveyDate in SURVEYS falls between 2 dates in MONTHS and if I have at least 12 months of prior data.

 

  • The purpose is to show trends and remove seasonality by showing data in groups of 12 month periods - with each month summarizing the current month and the previous 11 months - sometimes called a Rolling 12 months.

 

SURVEYS contains about 3,500 individual customer surveys with scores (1-10) Each survey has a SurveyDate converted to show all dates from the 1st day of the month. (e.g. 5/15/2014 is converted to 5/1/2014)  For each month in which I have 11 months of previous data, I want to show the average score from SURVEYS and perform other calcs once I get the relationship working.

 

To do this, I created a new table MONTHS as shown below.

 

So far, I haven't been able to create a relationship that does what I want.

 

How can I relate MONTHS to SURVEYS to match records in SURVEYS with a Survey Date between MONTHS:: c_RTM_Start and MONTHS::RTM_End  IF there are at least 12 months of data in SURVEYS prior to MONTHS::RTM_End.

 

 

MONTHS
Recordc_RTM_StartRTM_End
16/1/20125/1/2013
27/1/20126/1/2013
38/1/20127/1/2013

Outcomes