Set analysis : Count empty values in one column

[ClementByr]

Hello,

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

Machine_ID	Date	Num_Incident
1	2016	101452400
1	2016	101452406
1	2016	101452000
1	2016	101452447
1		101452451
1	2016	101452452
1	2016	101452496
1	2017	101452000
1	2017	101452591
1		101452597
1	2018	101452601
1	2018	101452614
1	2019	101452621
2	2017	101452000
2	2017	101452682
2	2017	101452690
2	2017	101452829
2		101452835
2	2017	101452842
2	2017	101452843
2	2018	101452000
2	2018	101452907
2	2019	101452911
2	2019	101452915
2	2019	101452916
2	2019	101452918
2		101452000
2	2019	101452922
2	2019	101452923
2	2019	101452924
2	2019	101452928
3	2018	101452940
3	2018	101452941
3	2018	101452944
3		101452000
3	2018	101452961
3	2018	101453035
3	2018	101453000
3	2018	101453042
3	2019	101453048

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.

[sunny_talwar · Accepted solution]

Have you tried NullCount() function?

NullCount(Date)

[Anil_Babu_Samineni]

Try one of the below

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

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

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

NullCount(Date)

[Vegar · Accepted solution]

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...

[Vegar]

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

😄😄😄

[ClementByr]

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.

[sunny_talwar]

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