topic Re: Count of rows with no nulls? in QlikView

Count of rows with no nulls?

qlikuser222 — Mon, 18 Apr 2016 20:55:42 GMT

Hi,

I have a requirement where I need to count the number of rows in a column which has no nulls.

can anyone please help me solving this requirement.

Please find the attached which has example .

Thanks

Re: Count of rows with no nulls?

rwunderlich — Mon, 18 Apr 2016 21:19:52 GMT

If you use the expression:

avg(Days)

Avg will ignore nulls, therefore giving you the correct answer in your example.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Count of rows with no nulls?

swuehl — Mon, 18 Apr 2016 21:46:21 GMT

Nicholas,

and Avg() will also ignore your empty Days values (they are not NULL, they still show a value in your sample, have a look at the list box, there is an empty entry) and also all values that are not a number.

Hence, as Rob suggested, Avg(Days) will do the Job perfectly in your setting.

If you want to keep to your original approach using a division, you could do it like

=sum(Days) / NumericCount(Days)

Re: Count of rows with no nulls?

sunny_talwar — Mon, 18 Apr 2016 22:02:09 GMT

Looking to do this in the text box or the chart itself?

Re: Count of rows with no nulls?

sujeetsingh — Tue, 19 Apr 2016 03:45:34 GMT

Sum(if(LEN(Field)=0,1)