topic Count of NULL Values in QlikView

Count of NULL Values

Mon, 27 Dec 2010 18:55:47 GMT

I am trying to count NOT NULL values using Set Analysis. I tried using the following expressions.

=Count({1-$<Sales = {'NULL'}>} ID)

=Count({1-$<Sales = {"NULL"}>} ID)

=Count({1-$<Sales = {$(=null())}>} ID)

None of these gives me the correct result. It is counting all the records, instead of the ones that are just NOT NULL.

With the same expressions, if I use a value like '10000.00', it gives me a correct result.

How do I correct count for the NOT NULL values?

Count of NULL Values

Mon, 27 Dec 2010 19:15:29 GMT

Hi there, first you have to be sure that you have actual null values and not empty strings or default values, once you have checked on that, you can use a similar expression to the following:

Count({$<Sales = {"*"}>} ID)

However the most efficient way, is adding counting flags in the script, like this:

Load SalesID, Sales, ..., if(isnull(Sales), 0,1) as SalesFlagCount

from Sales;

Then you can actually "sum" the sales instead of counting the values:

sum(SalesFlagCount)

Regards

Count of NULL Values

Mon, 27 Dec 2010 20:42:29 GMT

I do have actual NULL values and Count({$<Sales = {"*"}>} ID) is working.

Thank you very much