n Month average in chart

[#1]

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

[#2]

http://community.qlik.com/message/27946#27946

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

Is there any way to do it in expression?