Counting Nulls and Non-Null Value

[#1]

I need to Count the Number of Null values and number of non-null values. I am able to count the non-null values using count(if([KNOX  L3Queue]<>'Null',[KNOX  L3Queue])). I am not able to count the null values. The total number of null values should come 12.

Note: Edited by Community Moderator for spelling and syntax.

[sinanozdemir]

I had to edit it.

Hi,

You can use set analysis:

Count({<Type={"=Len(Trim(Type))=0"}>}Month) ---- Null

Count({<Type={"=Len(Trim(Type))>0"}>}Month) ---- Non-null

Hope this helps

[sinanozdemir]

Here is the sample app:

Hope it helps

[#4]

Thanks,It was helpful ,Can you please explain the working of the condition?

[#5]

I am not sure the code is not working on a bar graph?

[swuehl]

If you are coping with real NULL and empty values or values with spaces, potentially, you should be able to use

=Count( [KNOX  L3Queue] )

=NullCount( [KNOX  L3Queue] )

[#7]

Hi Stefan,

Thanks for the reply the count function is working fine but the NullCount is not working.

I am able to count the nulls using this function count(if([KNOX  L3Queue]='',[KNOX  L3Queue])),but not through =NullCount( [KNOX  L3Queue] ) .Could you please explain?

With =NullCount( [KNOX  L3Queue] ) it gives me 0 value

[swuehl]

Then you probable don't have NULL in your records, but empty field values, which is different from NULL (i.e. an empty value is still a value, NULL is not, it's not part of the symbol table).

If

Count(If(FIELD='', FIELD))

or

Count(If(Len(Trim(FIELD))=0,FIELD))

is working, you should be fine, right?

[#9]

Yes, It's working fine.

Could you please let me know how can I check if the values are empty values or null values by looking at it?Is there a way o differentiate ?

Thanks,

Ankit

[swuehl]

In most places, NULL will be indicated if needed with a dash symbol '-', like in the table viewer or chart dimensions by default, though it could be also a text value '-', of course.

So you may need to test with IsNull() or NullCount() to be sure.