Looking for rolling average for prior 12 values

[Anonymous]

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.

[Digvijay_Singh · Accepted solution]

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)

[Anonymous]

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.

[Anonymous]

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