How to Calculate 3 month moving average in set analysis

[Anonymous]

Hi All,

I Need help to get a 3 month Moving Average in set analysis, below is the source data for reference, i am using the below expression to get the ConversionRate%

count(DISTINCT{$<  %ProcessingType={'Disbursement'},PROCESS,[Rework Group]= ,[Month Num]>} CASE_NUM)/

count(DISTINCT{$<  %ProcessingType={'Creation'},PROCESS,[Rework Group]=,[Month Num] >} CASE_NUM)

Month	#Disb	#New	ConversionRate%
Jan-16	1,799	2,049	88%
Feb-16	1,187	1,977	60%
Mar-16	1,587	1,673	95%
Apr-16	1,102	1,224	90%
May-16	1,074	1,742	62%
Jun-16	1,091	1,922	57%
Jul-16	1,066	1,309	81%
Aug-16	1,197	1,777	67%
Sep-16	201	461	44%

Now i need the 3 month Moving average as below (i,e For March value=March+Feb+Jan)

Month	#Disb (3month)	#New(3Month)	ConversionRate%
Jan-16	1,799	2,049	88%
Feb-16	2,986	4,026	74%
Mar-16	4,573	5,699	80%
Apr-16	3,876	4,874	80%
May-16	3,763	4,639	81%
Jun-16	3,267	4,888	67%
Jul-16	3,231	4,973	65%
Aug-16	3,354	5,008	67%
Sep-16	2,464	3,547	69%

I tried using "ACCUMULATE 3 Steps Back in Expression" it applied only for Disb & New but not for the Conersion rate%, Can anyone help me to write a set analysis to achive the 3 months moving conversionRate% as in the 2nd table.

Thanks In Advance.

Arjin.

[arulsettu]

maybe like this

count(DISTINCT{$<  %ProcessingType={'Disbursement'},PROCESS,[Rework Group]= ,[Month Num],date={">=$(=MonthStart(Max(date), -3))<=$(=Max(date))"}>} CASE_NUM)/

count(DISTINCT{$<  %ProcessingType={'Creation'},PROCESS,[Rework Group]=,[Month Num],,date={">=$(=MonthStart(Max(date), -3))<=$(=Max(date))"} >} CASE_NUM)

date is your date field

[Anil_Babu_Samineni]

Arul, How about this?? Please correct me if i am wrong, Because he might looking current stage Date for last 3 months

count({$<  %ProcessingType={'Disbursement'},PROCESS,[Rework Group]= ,[Month Num],date={">=$(=AddMonths(Max(date), -3))<=$(=Max(date))"}>} CASE_NUM)/

count({$<  %ProcessingType={'Creation'},PROCESS,[Rework Group]=,[Month Num],,date={">=$(=AddMonths(Max(date), -3))<=$(=Max(date))"} >} CASE_NUM)

[Anonymous]

Thanks for your time Arul & Anil,

i used the below expression and it worked fine for me.

sum( aggr( rangesum( above( Count( {$<Month,%ProcessingType={'Disbursement'},PROCESS,[Rework Group]=,[Month Num]>}  CASE_NUM),0,3) ),Month))/

sum( aggr( rangesum( above( Count( {$<Month,%ProcessingType={'Creation'},PROCESS,[Rework Group]=,[Month Num]>}  CASE_NUM),0,3) ),Month))

Thanks to Elifs post Accumulative Sums.

Cheers,

Arjin.

[sunny_talwar]

Try this:

RangeSum(Above(Count(DISTINCT{$<%ProcessingType ={'Disbursement'}, PROCESS, [Rework Group], [Month Num]>} CASE_NUM), 0, 3))

/

RangeSum(Above(Count(DISTINCT{$<%ProcessingType = {'Creation'}, PROCESS, [Rework Group], [Month Num]>} CASE_NUM), 0, 3))