topic Moving day average chart - Allocated memory exceeded in QlikView

Moving day average chart - Allocated memory exceeded

Tue, 06 Apr 2010 17:12:48 GMT

I am preparing 10 days/20 days moving average chart which is a combination of bar and lines

added this dimension -

=if(RollingThreeMonthsFlag=1 and weekday(REPORTING_DATE)<5, Date(REPORTING_DATE,'DD-MMM'))

and these expressions -

Last3Months=sum(RollingThreeMonthsFlag*AM_NET_CVA_ASGN_DELTA) //Invisible

Daily CV=Column(1) //Bar

10 Day Mov Avg=if(rowno()>9, rangeavg(above(Column(1),1,10))) //line

20 Day Mov Avg=if(rowno()>19, rangeavg(above(Column(1),1,20))) //line

It was working till last week (although refresh time was slow) and now it gives memory error.

It seems that since I am using rangeavg(), selecting Suppress Zero-Values has no effect. Otherwise I would have just used REPORTING_DATE as dimension and restricted the data for last 3 months using expressions.

Would appreciate if someone can provide a better way to handle this situation!

Thanks..

Moving day average chart - Allocated memory exceeded

Thu, 08 Apr 2010 15:35:51 GMT

Bump!

Moving day average chart - Allocated memory exceeded

Oleg_Troyansky — Fri, 09 Apr 2010 05:09:49 GMT

Too many IFs, too many heavy calculations... Those experts that advise people to use IF formulas, are really creating a lot of confusion...

One simplification that I would advise is this:

Since you already have your "Three rolling months" flag calculated, you don't need to use Calculated Dimension. Simply use your DATE as a dimension and only limit the "3 months" within the expression. You can also check for the weekday to be <=5 within the expression, using Set Analysis. Can't guarantee the syntax, but something along the following lines:

sum( {< DATE = {"=weekday(DATE)<=5"}, MyFlag = {1} >} Sales)

good luck!

Moving day average chart - Allocated memory exceeded

Fri, 09 Apr 2010 18:58:07 GMT

Unfortunately we are still using QV 8.2 [:'(]

Recently upgraded QV server to 9 and new plugin roll out is still in progress...

But thank you for the suggestion! This would help me update the chart once we are completely in QV 9!

Moving day average chart - Allocated memory exceeded

Oleg_Troyansky — Fri, 09 Apr 2010 21:20:59 GMT

If you can't use Set Analysis, you can still improve your chart performance with calculated flags as opposed to IF statements. You already have a 3 Months Flag. Now, create another flag for Weekday < 5, and your expression could look like:

sum(Revenue * [3 Months Flag] * Weekday_Flag)

With this expression, you don't need your heavy calculated dimension anymore.

cheers,