Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
ClementByr
New Contributor III

Set analysis : Count empty values in one column

Hello,

I am trying to count how many empty values there in a single column. Here is a data sample :

Machine_IDDateNum_Incident
12016101452400
12016101452406
12016101452000
12016101452447
1 101452451
12016101452452
12016101452496
12017101452000
12017101452591
1 101452597
12018101452601
12018101452614
12019101452621
22017101452000
22017101452682
22017101452690
22017101452829
2 101452835
22017101452842
22017101452843
22018101452000
22018101452907
22019101452911
22019101452915
22019101452916
22019101452918
2 101452000
22019101452922
22019101452923
22019101452924
22019101452928
32018101452940
32018101452941
32018101452944
3 101452000
32018101452961
32018101453035
32018101453000
32018101453042
32019101453048

 

I want to count all the null value in the Date column using set analysis which is supposed to be 5 null values. So far, I have tried these formula  without success:

Count({<Machine_ID={'=Len(Trim(Date))=0'}>} Date)

Count({<Date={'=Len(Trim(Date))=0'}>} Date)

Count({<'=Len(Trim(Date))=0'>} Date)

Can someone help me figure out a solution, please ?

Best regards,

Clément Boyer.

Labels (4)
2 Solutions

Accepted Solutions

Re: Set analysis : Count empty values in one column

Have you tried NullCount() function?

NullCount(Date)

 

View solution in original post

Partner
Partner

Re: Set analysis : Count empty values in one column

If you are interested of counting nulls then you should look into the NullCount() function.

For chart:

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/CounterAg...

For script:

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterAggrega...

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

6 Replies

Re: Set analysis : Count empty values in one column

Have you tried NullCount() function?

NullCount(Date)

 

View solution in original post

Re: Set analysis : Count empty values in one column

Try one of the below

Count(If(IsNull(Date), Date))

Count({<Date={"=Len(Date)=0"}>} Date)

Count({<Date-={"=Len(Date)>0"}>} Date)

NullCount(Date)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Set analysis : Count empty values in one column

If you are interested of counting nulls then you should look into the NullCount() function.

For chart:

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/CounterAg...

For script:

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/CounterAggrega...

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Partner
Partner

Re: Set analysis : Count empty values in one column

Wow all three with the same advise  within a couple of minutes @sunny_talwar @Anil_Babu_Samineni 

😄😄😄

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
ClementByr
New Contributor III

Re: Set analysis : Count empty values in one column

Thank you Vegar, Anil and Sunny,

Following your suggestion, I tried to calculate the number of null values in all the table. So I want to do an addition with the nullcount Machine_ID, Date, Num_Incident.

But if I do the formula : NullCount(Machine_ID) + NullCount(Num_Incident), I get the value of 2 where I am supposed to find 0 null cells. Can you please explain why ?

Best regards.

Re: Set analysis : Count empty values in one column

May be they are not null and just while spaces? Difficult to know without playing around with it