topic Re: Looking for rolling average for prior 12 values in App Development

Looking for rolling average for prior 12 values

Anonymous — Tue, 27 Mar 2018 21:10:17 GMT

Hi,

I have quite the perplexing question. I have an expression that reads as follows:

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))

(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))

(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))

(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))

-(1/10))

/((4/10-(1/10)))*100)

and I want to take the value that is generated from the expression and divide it by the rolling 12 month average beginning with the most recent period.

For example if I have a data point in December 2017, I want to calculate the average of the most recent 12 values from the above expression which would be Jan 2017 to Dec 2017.

Re: Looking for rolling average for prior 12 values

Digvijay_Singh — Tue, 27 Mar 2018 21:19:47 GMT

may be this, if you are trying in table and having month Year as a dimension. -

rangeavg(

((((Sum([Active Clients Low Risk])/sum([Active Clients])*(1/10))

(Sum([Active Clients Medium Risk])/sum([Active Clients])*(2/10))

(Sum([Active Clients Moderate-High Risk])/sum([Active Clients])*(3/10))

(Sum([Active Clients High Risk])/sum([Active Clients])*(4/10)))

-(1/10))

/((4/10-(1/10)))*100)

,0,12)

Re: Looking for rolling average for prior 12 values

Anonymous — Wed, 28 Mar 2018 13:21:35 GMT

No, that didn't quite do it. The average should be around 0.42 but the result with the addition you provided yields 0.10.

Would it help if that the periodicity column is called "Reporting Month-Year"? Perhaps adding that in somehow would help. The Month Year I'm using is customized so maybe that's why your answer didn't work. I'm using "Reporting Month-Year" instead.

Re: Looking for rolling average for prior 12 values

Anonymous — Thu, 29 Mar 2018 13:15:17 GMT

This is correct, I entered the formula incorrectly, thanks!