4 Replies Latest reply: May 7, 2018 4:38 AM by celine xu

# KPI min () and avg() avoid '0' items, count() how many of the items having '0´value

Hi,

I am creating KPI using avg( ) or min()

want to exclude the item have 0 in it. Could I do it? or I have to change 0 to  NULL then do it?

and Also want to also have another KPI show number of t within column do not have 0 value,'

if (A!=0, count(district t) )

how could I wright the funtion?

any suggestion ? Thank you!

• ###### Re: KPI min () and avg() avoid '0' items, count() how many of the items having '0´value

You don't have to change the 0 to NULL. It might be simpler to do so - but it depends what it really means in your data - does a NULL and a zero mean exactly the same thing?

You can avoid changing it in your data by using a set expression in your aggregations to only consider NON-NULL and NON-ZERO values by for instance having a count like this:

Count( { < MyField -= {0} > } MyField )     // Eliminates all zeros from the set of values that will be counted

• ###### Re: KPI min () and avg() avoid '0' items, count() how many of the items having '0´value

Hi Petter,

I had 2 questions: 1. is do the calculation but exclude the record = 0, and I chose the expression avg()

so it is:

Avg( { < MyField -= {0} > } MyField )

right?

2. how could I count how many field has the record 0

I guess it is

Count( { < MyField = {0} > } MyField )

right?

• ###### Re: KPI min () and avg() avoid '0' items, count() how many of the items having '0´value

Yes - both are correct

• ###### Re: KPI min () and avg() avoid '0' items, count() how many of the items having '0´value

Thank you so much!