Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
wcamargo
Contributor III
Contributor III

Count when 2 columns are empty

 

Hi all,

I need some help to count the number of line empty in 2 columns.

In the example, when the column "Item" is filled, I would like to count the number of lines which have both "Info 1" and "Info 2" empty.

In this case it should be 2 (Line B and Line E).

Item Info 1 Info 2
A X  
B    
C X X
D   X
E    
F X  
G   X

 

Does anybody know how to do this formula?

 

Many thanks,

Wil

Labels (4)
1 Solution

Accepted Solutions
RafaelBarrios
Partner - Specialist
Partner - Specialist

It’s not nice but should do the trick

using item as dimension in a chart

Count(if(len(trim(field1))>0 and len(trim(field2))>0,1 ))

or

as text or kpi object

Count({< Item={"*"}>}if(len(trim(field1))>0 and len(trim(field2))>0,1 ))

could have some syntax error as I couldnt tried

best,

View solution in original post

3 Replies
RafaelBarrios
Partner - Specialist
Partner - Specialist

It’s not nice but should do the trick

using item as dimension in a chart

Count(if(len(trim(field1))>0 and len(trim(field2))>0,1 ))

or

as text or kpi object

Count({< Item={"*"}>}if(len(trim(field1))>0 and len(trim(field2))>0,1 ))

could have some syntax error as I couldnt tried

best,

wcamargo
Contributor III
Contributor III
Author

Amazing @RafaelBarrios ! It worked for me the 2nd formula. I just had to put "=0" instead of ">0".

 

It worked like this:

Count({< Item={"*"}>}if(len(trim(field1))=0 and len(trim(field2))=0,1 ))

 

I appreaciate your help.

Thank you very much.

 

Regards,

 

Wil

RafaelBarrios
Partner - Specialist
Partner - Specialist

upssss
sorry..... well seen 👍

im glad it works for you