2 Replies Latest reply: Dec 27, 2010 3:42 PM by SwethaSolipuram RSS

    Count of NULL Values


      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

          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: