Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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)
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.
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))