Rolling Avg expression not working

[raghavsurya]

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

[#2]

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]

See attached for one solution.

Regards,

Vlad

[raghavsurya]

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

Regards,

Raghav

[raghavsurya]

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]

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]

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

[johnw]

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]

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

[raghavsurya · Accepted solution]

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