Count distinct where not Exist

[MohammedHassan]

Hello Everyone – I was hoping you can help me with how to get the below accomplished in Qlik Sense, I would really appreciate it, here is the example in its simplest form:

I’m getting a table from SQL that looks like this in Qlik:

FileNumber // ShipmentStatus

9500001 // Delivered

9500001// Tendered

9500001// Order_Released

9500002// Delivered

9500002// Tendered

9500002// Order_Released

9500003// Tendered

Now, if I try to count distinct FileNumber where ShipmentStatus = Tendered, I will get 3, great no issue, and also no issue if I account distinct any other ShipmentStatus.

But the issue is when I try to count distinct FileNumber where ShipmentStatus <> Tendered, I will get 2, although the right result should be zero. Same if I try to any other status but use the function <>. Basically Qlik count all the other FileNumber.

I hope I was able to explain.

Any feedback is highly appreciated.

Thanks in advance.

[HugoRomeira_PT · Accepted solution]

Hello,

For frontend/visualization you can use the following logic:

count(distinct FileNumber) - Count(Distinct {<ShipmentStatus ={'Tendered'}>} FileNumber)

Basically you count all and then subtract the ones that have the status to be removed.

Hope it helps!

[MohammedHassan]

Thanks Hugo, that seems logical, will try. I appreciate  it.

[vinieme12]

Modified

Try below

=Count( {<Filenumber=e({<ShipmentStatus ={'Tendered'}>} FileNumber) >} Distinct Filenumber)