Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Various IF conditions within a Count Distinct

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!

 

3 Replies
Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor
Contributor

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!

Highlighted
Contributor
Contributor

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!