Count of NULL Values

[#1]

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

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

or

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

or

=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?

[#2 · Accepted solution]

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

[#3]

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

Thank you very much