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

    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:

      DateRegionA3 Month Avg(A)(Explaination)
      2011/11A217.9217.9(0 + 0 + 217.9)/1
      2011/12A275.6246.8(0 +217.9 + 275.6)/2
      2012/01A457.8317.1(217.9 + 275.6 + 457.8)/3
      2012/02A456.1396.5
      2012/03A477.1463.7
      2012/04A357.7430.3
      2012/05A0278.3( 477.1 + 357.7 + 0)/3
      2012/06A0119.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