Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

MikeRadem
New 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

Re: Set analysis if Null with distinct

Try this

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

Re: Set analysis if Null with distinct

Can u try :

 

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

MikeRadem
New Contributor

Re: Set analysis if Null with distinct

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

Re: Set analysis if Null with distinct

Try this

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

Re: Set analysis if Null with distinct

Thank you Sunny, this works perfectly.