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?
Please kindly help.
Best Regards,
Nick
