Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

raghavsurya
Valued Contributor

Rolling Avg expression not working

Hi All,

We have a requirement to calculate Rolling average for monthly revenue.  I have tried to calculate the rolling average in  Qlikview with below expression

rangeavg(Above(Sum(Revenue),0,6)) .  At the same time I have also calculated average manually to cross verify the same.  The manual calculation is not

matching with the average being calculated by Expression.  I am attaching Qvw application and supporting excel for your reference.  Can you please suggest where I am missing.

Regards,

Raghav

1 Solution

Accepted Solutions
raghavsurya
Valued Contributor

Re: Rolling Avg expression not working

Hi Vlad,

Finally used this expression to resolve moving average issue.  Thanks for all the help.

Sum({<NumMonthYear={'>=$(=Max(NumMonthYear)-180)<=$(=Max(NumMonthYear))'},FY=,Month=>}Value)

Regards,

Raghav

9 Replies
Not applicable

Re: Rolling Avg expression not working

Hello Raghav,

I have been experimenting with your sample. The difference appears to be attributed to the way the ABOVE function works.

You specified: rangeavg(Above(sum(Revenue), 0,6))

It appears you want the average of the previous 6 months from the expression, so the offset should be 1 not 0.

However, the real problem seems to be that a. you probably need the year or FY dimension in the object, and; b. "the current column segment will include only rows with the same values as the current row in all dimension columns" (from the manual). January 2011 will have a null value; February 2011 will be January; March 2011 will be the average of January and February, and so on...

So, basically the grouping of the dimensions has precedence over the number of rows specified (6).

I know this is not much help as far as determining the solution; this is more of an explanation of what is happening.

vgutkovsky
Honored Contributor II

Re: Rolling Avg expression not working

See attached for one solution.

Regards,

Vlad

raghavsurya
Valued Contributor

Rolling Avg expression not working

Thanks Vlad.... I shall try to use the logic you have provided. 

Regards,

Raghav

raghavsurya
Valued Contributor

Rolling Avg expression not working

Hi Vlad,

When I try the formula on a big volume of data the chart is hanging and not returning any value.  Can you please suggest any other approach.

Regards,

Raghav

vgutkovsky
Honored Contributor II

Re: Rolling Avg expression not working

When you use date islands with big data sets performance does indeed tend to suffer. Does your rolling average need to take into account any user selections or can it just be a fixed monthly value calculated in the script?

raghavsurya
Valued Contributor

Rolling Avg expression not working

Hi Vlad,

We infact thought about fixed monthly average, but the rolling average needs to take into account few user selections into account and also we are not using time period (month or qtr or year) as dimension. 

Can you please help me on this front.

Regards,

Raghav

MVP
MVP

Re: Rolling Avg expression not working

I agree with langaar9.  Your problem is that you're using a month without a year.  I'd personally redefine my Month field to include the year:

date(monthstart(Date,'MMM YYYY')) as Month,

The values look fine to me then.

I have no idea what you're saying when you say "we are not using the time period (month or qtr or year) as dimension".  The example you attached does have month as a dimension.

vgutkovsky
Honored Contributor II

Re: Rolling Avg expression not working

Try the attached instead. The only problem is that I can't get it to suppress nulls because of the 2nd expression...

raghavsurya
Valued Contributor

Re: Rolling Avg expression not working

Hi Vlad,

Finally used this expression to resolve moving average issue.  Thanks for all the help.

Sum({<NumMonthYear={'>=$(=Max(NumMonthYear)-180)<=$(=Max(NumMonthYear))'},FY=,Month=>}Value)

Regards,

Raghav