Moving day average chart - Allocated memory exceeded

[#1]

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..

[#2]

Bump!

[Oleg_Troyansky]

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!

[#4]

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!

[Oleg_Troyansky]

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,