Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeRadem
Contributor
Contributor

Set analysis if Null with distinct

Hi,

I need some help with set analysis if the value is missing.

Here is what I want to do:

I need to count the number of audits if a certain value ("Actie status specifiek" in this case) is missing.
The result needs to be a distinct.

This is the expression I use:

Count(distinct{$<[Audit item nummer]={"=Len(Trim([Actie status specifiek]))=0"},
Status={"Open"},
Impact={$(vImpact)}
>}[Audit item nummer])

It seems that the distinct works partially.
If I don't use distinct the result turns out te be 14 (which is correct without the distinct).
However with the distinct I expect the result to be 5, instead I get 7.
(See table in image, result gives 7 while it should be 5).

Can anyone help me out with this one?

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

Count(DISTINCT {$<[Audit item nummer] = {"=Len(Trim(Concat([Actie status specifiek]))) = 0"}, Status={"Open"}, Impact={$(vImpact)}>} [Audit item nummer])

View solution in original post

4 Replies
OmarBenSalem
Partner
Partner

Can u try :

 

NullCount(distinct{$<Status={"Open"},Impact={$(vImpact)}>}[Audit item nummer])

MikeRadem
Contributor
Contributor
Author

The field [Audit item nummer] does not have missing values.

I want to count the missing values in [Actie status specifiek], and it has to be a distinct on [Audit item nummer].

So your code is usefull if I don't need a distinct (thank you for that!).
That would be:

NullCount({$<Status={"Open"},
Impact={$(vImpact)}
>}[Actie status specifiek])

But the distinct problem is not solved, because adding distinct to the above code is not usefull (need it on [Actie status specifiek]).

sunny_talwar

Try this

Count(DISTINCT {$<[Audit item nummer] = {"=Len(Trim(Concat([Actie status specifiek]))) = 0"}, Status={"Open"}, Impact={$(vImpact)}>} [Audit item nummer])

View solution in original post

MikeRadem
Contributor
Contributor
Author

Thank you Sunny, this works perfectly.