Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Mauqlikhelp
Contributor
Contributor

Two if's condition and a count

Hi,

I need to count a yield percentage but only under a certain condition.

Right now, without the added condition, I am using this formula to calculate the yield percentage, which works good:

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

I now need to calculate a new yield, but only for Jobnumber that starts with WB.

I tried this:

1-(if([Job number] like 'WB*',Count(distinct(if(isnull(Reason)=0,Upper([Serial number])))))
/ if([Job number] like 'WB*',Count(distinct(Upper([Serial number])))))

But it's not working.

Any ideas? 🙂

Thank you!

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Glad you found the solution. Please mark correct answer to close this thread

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)

View solution in original post

4 Replies
Anil_Babu_Samineni

Perhaps this?

Count(DISTINCT {<Reason={"=Len(Reason)=0"}, [Job number]={'WB*'}>} [Serial number])/
Count(DISTINCT {<[Job number]={'WB*'}>} [Serial number])

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)
Mauqlikhelp
Contributor
Contributor
Author

Thank you for your help!

It is not quite working though, I am getting 100%, 100%, 100%, when I should be getting 93.1%, 95.5%, 100%.

Maybe it has something to do with the Len(Reason)?

The "Reason" condition was there so that, if a serial number has a Reason entered (a defect), then it counts it, and divides it by the total number of serial numbers.

Mauqlikhelp
Contributor
Contributor
Author

Found it!

Only changed a bit your formula:

1-(
Count(DISTINCT {<Reason={"=Len(Reason)<>0"}, [Job number]={'WB*'}>} [Serial number])/
Count(DISTINCT {<[Job number]={'WB*'}>} [Serial number])
)

Solved, thanks!

Anil_Babu_Samineni

Glad you found the solution. Please mark correct answer to close this thread

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)