Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

sassoonj
New Contributor III

Looking for rolling average for prior 12 values

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.

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: Looking for rolling average for prior 12 values

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)

3 Replies
Digvijay_Singh
Honored Contributor III

Re: Looking for rolling average for prior 12 values

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)

sassoonj
New Contributor III

Re: Looking for rolling average for prior 12 values

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.

sassoonj
New Contributor III

Re: Looking for rolling average for prior 12 values

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

Community Browser