Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcement: We have made updates to resolve a Page Not Found error. If you still get this error, please let us know in the Community Corner.
Highlighted
yangcelinexu
Contributor

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!

4 Replies
MVP
MVP

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

yangcelinexu
Contributor

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

Hi Petter,

Thank you for your response

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?

MVP
MVP

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

Yes - both are correct

yangcelinexu
Contributor

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

Thank you so much!