Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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]))))))
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!
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!