1 Reply Latest reply: Jul 5, 2012 5:11 AM by Nick Tang

# n Month average in chart

Hi all,

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

Here is the dataset:

 Date Region A 3 Month Avg(A) (Explaination) 2011/11 A 217.9 217.9 (0 + 0 + 217.9)/1 2011/12 A 275.6 246.8 (0 +217.9 + 275.6)/2 2012/01 A 457.8 317.1 (217.9 + 275.6 + 457.8)/3 2012/02 A 456.1 396.5 2012/03 A 477.1 463.7 2012/04 A 357.7 430.3 2012/05 A 0 278.3 ( 477.1 + 357.7 + 0)/3 2012/06 A 0 119.2 ( 357.7 + 0 + 0)/3

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?