2 Replies Latest reply: Jul 3, 2012 8:24 PM by Nick Tang RSS

    Suppress 0 with 'above' function

      Hi all,

       

      I got a formula that calculating the average value of inputted month.

       

      However as it contains 'above' function, it cannnot suppress by 0 or null. Does anyone know how can force suppress 0 or how to change this formula?

       

       

      (if(PAYROLL_PERIOD < Payroll_CalendarStart.CalendarYearMonth and PAYROLL_PERIOD >Payroll_CalendarEnd.CalendarYearMonth,
      RangeSum(above(TOTAL sum(OTHER_PROVISION),0,$(vMovingMonth)))/$(vMovingMonth) ,0 ))

       

       

      Thanks

      Regards,

      Nick

        • Re: Suppress 0 with 'above' function
          Stefan Wühl

          You can try enclosing your expression in an advanced aggregation aggr() function, with the aggr() dimensions being your chart dimensions.

          This might work but it is essential that your aggr() dimensions (at least the dimensions relevant to your rangesum) have a load order that matches your sort order needed (e.g. chronological))

           

          If you can upload a small sample, I could check if this idea might work for you.

           

          I remember I have also posted some samples here in the forum that demonstrate the approach, but couldn't found one of them at the moment.