Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Excluding nulls, blanks and empty fields in counting rows

I am trying to exclude Null, blanks and empty fields from count but without success. I would like to count rows which has its field 'Description' different from null, blank or empty, so I perform:

=Count({$<[Type] = {'A'}, $(=Len($(=Trim(Description))))={'>0'}>} [Description])

but it is not working. Doing:

=Count({$<[Type] = {'A'}>} [Description]) does not work. It includes empties and blanks.


Also I have tried using Sum function with the same set analysis but without success.


for example, from below table:


Order     Type     Description

0001     A            Order type A

0002     B           Order type B

0003     A          

0004     A            Order type A


The result should be 2. Order 0002 is not counted because is of type B and order 0003 is not counted because its description is empty (blank).



1 Solution

Accepted Solutions
jeevays7
Partner - Creator III
Partner - Creator III

Hi,

try this,

Count({$<[Type] = {'A'}, Description-={' ','','Null'}>} [Description])

View solution in original post

11 Replies
vishsaggi
Champion III
Champion III

Which chart you are using this expression in ?

Anonymous
Not applicable
Author

Hi

the following gives count of Numeric count...(In starigt table or Pivot table)

count(TOTAL( RangeNumericCount(  [Description] )))



so may be you can do like this





Aggr (count(TOTAL( RangeNumericCount(  [Description] ))),Type)




Not applicable
Author

I am not using any chart, I only want to show the result in a text object. I expect result to be the number of row of which have field 'Type' set to 'A' and 'Description' field different from blank, empty or null.

jeevays7
Partner - Creator III
Partner - Creator III

Hi,

try this,

Count({$<[Type] = {'A'}, Description-={' ','','Null'}>} [Description])

settu_periasamy
Master III
Master III

Try this

=Count({$<[Type] = {'A'}, Description={"=Len(Trim(Description))>0"}>} [Description])

Not applicable
Author

Working! I tried to impove it by doing this:

Count({$<[Type] = {'A'}, $(=Trim(Description))-={'','Null'}>} [Description])


in order to reduce the exclusion list to '' and 'Null' but it was not working, why?

Not applicable
Author

Your solution also works in case of not nulls! not sure for nulls.... thanks!

sunny_talwar

Are you saying that if you want to count where description is null? You can try this:

=Count({$<[Type] = {'A'}, Order = {"=Len(Trim(Description)) = 0"}>} [Description])

Not applicable
Author

No, I want to exclude nulls as well, but I was trying to improve the solution proposed by jeevays7‌I wanted to reduce the exclude list by performing trim over field first but it was not working.