Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
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

Re: Various IF conditions within a Count Distinct

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)
Highlighted
Contributor
Contributor

Re: Various IF conditions within a Count Distinct

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

Re: Various IF conditions within a Count Distinct

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!