Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Highlighted

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
Highlighted

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