topic Various IF conditions within a Count Distinct in QlikView

Various IF conditions within a Count Distinct

Mauqlikhelp — Wed, 29 Apr 2020 15:09:20 GMT

Hi,

I have this expression to start with: It gives a percentage value of the serial numbers that have a Reason issue divided by all serial numbers.

1 - (Count (distinct(if(isnull(Reason)=0,Upper([Serial number])))) / Count(distinct(Upper([Serial number]))))

I need to add to this expression several conditions:

For the serial numbers that have a Reason issue, I need to count only the ones with specific conditions:

- If [Job Number] starts with WR, then count only the ones that are NPI =-{"=isnull(NPI)"}

- But we also need to count [Job Number] that starts with WB and WP but that are NOT NPI...

Hope it's clear enough 🙂 Any possible help? Thank you!

Re: Various IF conditions within a Count Distinct

Anil_Babu_Samineni — Wed, 29 Apr 2020 15:13:59 GMT

Perhaps this?

If(Match([Job Number],'WR*'), 1 - (Count ({<NPI ={"=isnull(NPI)"}>} distinct(if(isnull(Reason)=0,Upper([Serial number])))) / Count({<NPI ={"=isnull(NPI)"}>} distinct(Upper([Serial number])))),
If(Match([Job Number],'WR*', 'WP*'), 1 - (Count (distinct(if(isnull(Reason)=0,Upper([Serial number])))) / Count(distinct(Upper([Serial number]))))))

Re: Various IF conditions within a Count Distinct

Mauqlikhelp — Wed, 29 Apr 2020 15:25:55 GMT

But is it possible to have my conditions inside a single Count?

The WR (with NPI) AND WB/WP (without NPI) needs to be calculated inside the same count.

The conditions of the serial number also needs to be applied to the number I divide by:

1 - count distinct (if job=WR and is NPI) AND (job=WB,WP and is NOT npi) AND isnull(reason)=0 then we divide by count (if job=WR and is NPI) or (job=WB,WP and is NOT NPI)

So when I divide, I just remove the isnull(reason) condition

Thanks for your time!

Re: Various IF conditions within a Count Distinct

Mauqlikhelp — Mon, 04 May 2020 17:02:36 GMT

Hi again,

I tried to make it more simple:

NPI field: This field marks the part numbers that have a released date.

What makes it difficult here is that it's possible that a WB or WP work order does not have a released date yet, but some does. That's why I want to calculate the NPI products for all work orders W*, and then I need to add the WB and WP work orders that are not NPI. But this below is not working... Any ideas what I am doing wrong?

if(isnull(NPI)=0,(Count (Distinct{<[Job number] = {'W*'}>} [Job number])),'')
+
if(isnull(NPI)<>0,(Count (Distinct{<[Job number] = {'WB*', 'WP*'}>} [Job number])),'')

Thank you!