Distinct count for null fields

[Anoop91]

Hi Everyone ,

Im trying to get the distinct count of null values , For the below data set , I want to get the Count of Case ID where Standardized Issue is Blank or Null 

I used "NullCount([Standardized Issue:]) " and it throws the count as 4 , but the unique count is only "2"

I tried "NullCount(distinct([Standardized Issue:]))" and it throws the count as "1"

can someone please help me with this formulation 

Case ID	Standardized Issue:	SKU
CASE-19647	MATERIAL SHORTAGE	ABCD1234
CASE-19647	MATERIAL SHORTAGE	ABCD2345
CASE-19647	MATERIAL SHORTAGE	ABCD3456
CASE-19647	MATERIAL SHORTAGE	ABCD4567
CASE-19647	MATERIAL SHORTAGE	ABCD5678
CASE-19647	MATERIAL SHORTAGE	ABCD6789
CASE-19647	MATERIAL SHORTAGE	ABCD7900
CASE-19603		ABCD9011
CASE-19603		ABCD10122
CASE-19647	TOOLING DOWN	ABCD11233
CASE-19647	TOOLING DOWN	ABCD12344
CASE-19567	COSTING TO BE WORKED	ABCD13455
CASE-19561		ABCD14566
CASE-19561		ABCD15677

[Chanty4u]

May be this ?

Count(DISTINCT {< [Standardized Issue:] = {'', null} >} [Case ID])

[Anoop91]

I tried this and it returns the value as 0 😞 

[Chanty4u]

try this   i have given space in between ' '

Count(DISTINCT {< [Standardized Issue:] = {' ', null} >} [Case ID])

or this

Count(DISTINCT {< [Standardized Issue:] -= {' '}>} [Case ID])

[Anoop91]

Thanks @Chanty4u  , however  , both these didnt work . They are throwing a higher value than what's null  . Could there be an issue with spaces or something within that cell that Qliksense is not considering as null ?

[Chanty4u]

yes may be with the data  it cause an issue can you post sample qlikview or qliksense file.

[Anoop91]

the input is actually from Excel and I unfortunately cannot share the QVF file as it is a company laptop 😞 

However , when i try to download the information from the Qliksense dashboard , the null values are indicated as "-" 

[Chanty4u]

Try this 

=Count(DISTINCT{< [Standardized Issue:] = {" ", null, "-"} >} [Case ID])

[krishna_2644 · Accepted solution]

Try as Below:

Data:
Load *,
len(trim([Standardized Issue:])) as len;
Load * inline [
Case ID, Standardized Issue:, SKU
CASE-19647, MATERIAL SHORTAGE, ABCD1234
CASE-19647, MATERIAL SHORTAGE, ABCD2345
CASE-19647, MATERIAL SHORTAGE, ABCD3456
CASE-19647, MATERIAL SHORTAGE, ABCD4567
CASE-19647, MATERIAL SHORTAGE, ABCD5678
CASE-19647, MATERIAL SHORTAGE, ABCD6789
CASE-19647, MATERIAL SHORTAGE, ABCD7900
CASE-19603, , ABCD9011
CASE-19603, , ABCD10122
CASE-19647, TOOLING DOWN, ABCD11233
CASE-19647, TOOLING DOWN, ABCD12344
CASE-19567, COSTING TO BE WORKED,ABCD13455
CASE-19561, , ABCD14566
CASE-19561, , ABCD15677
];

On UI:
= Count(distinct {$<len = {0}>}[Case ID])

[BrunPierre]

Hi, as below.

=Count({$< [Standardized Issue:] = {"=Len(Trim([Standardized Issue:]))=0"}>} DISTINCT [Case ID])