Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Bump!
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!
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!
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,