Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
It works after adding aggr().
Thanks!!