topic Re: Suppress 0 with 'above' function in QlikView

Suppress 0 with 'above' function

Tue, 03 Jul 2012 14:12:59 GMT

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

swuehl — Tue, 03 Jul 2012 15:16:56 GMT

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.

Re: Suppress 0 with 'above' function

Wed, 04 Jul 2012 00:24:53 GMT

It works after adding aggr().

Thanks!!