n Month average in chart

Thu, 05 Jul 2012 08:51:36 GMT

Hi all,

I need to make a line char that calcalate the 3 months average with 'Date' as diminsion.

Here is the dataset:

By using the below formula, I can get the correct answer in table,

if(PAYROLL_PERIOD >= Payroll_CalendarStart.CalendarYearMonth and PAYROLL_PERIOD <=Payroll_CalendarEnd.CalendarYearMonth,

Rangeavg(above(TOTAL sum(OTHER_ALLOWANCE),0,$(vMovingMonth))))

However, as I want to show specified month, I need to use the suppress 0 function..then.. i changed my formula to below:

aggr(if(PAYROLL_PERIOD >= Payroll_CalendarStart.CalendarYearMonth and PAYROLL_PERIOD <=Payroll_CalendarEnd.CalendarYearMonth,

Rangeavg(above(TOTAL sum(OTHER_ALLOWANCE),0,$(vMovingMonth)))

),PAYROLL_PERIOD)

After I added aggr(), I cannot get the correct answer for 2011/11 -2012/12. Anyone have the same problem?

Please kindly help.

Best Regards,

Nick

Re: n Month average in chart

Thu, 05 Jul 2012 09:11:37 GMT

By using calcuation dimension, it work. but have performance problem.

Is there any way to do it in expression?