Suppress 0 with 'above' function

[#1]

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

[swuehl · Accepted solution]

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.

[#3]

It works after adding aggr().

Thanks!!