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: 
Anonymous
Not applicable

set analysis with multiple fields and/or

Im trying to come up with a formula in an Expression and I think it can be done with Set Analysis.

What I am trying to do is do a Distinct Count of [Case Number]

  1. when [Case Complaint] OR [WO Complaint] is ‘Yes’  

         AND….

  1. when [Case CLT] OR [WO CLT] is Not ‘Yes*’

So far I have come up with this formula:

count(DISTINCT
{$

<
[Case Complaint]={'yes'}> +
<
[WO Complaint]={'yes'}> -
<
[Case CLT]={'yes','yes from work order','yes from case'}> -
<
[WO CLT]={'yes','Yes from work order','yes from case'}>

}
[Case Number])

But I think it is wrong.  Can someone help me correct this?

Thanks!

7 Replies
Anonymous
Not applicable
Author

sunny_talwar

But I think it is wrong.

You think this is not right? I mean do you get the number you expect to see? If you are then this might be correct.

pradosh_thakur
Master II
Master II

Are you getting the expected values ? IF not and you are not really sure if it is 'YES' or 'Yes' or 'yes'   than can you please try enclosing them in "(double quotes) which will act as a search string and will be case insensitive .

for more

https://community.qlik.com/blogs/qlikviewdesignblog/2017/09/11/quotes-in-set-analysis

Learning never stops.
swuehl
MVP
MVP

Try

count(DISTINCT
{$

<
[Case Complaint]={'yes'},[Case CLT]={'yes','yes from work order','yes from case'}>

+

<[Case Complaint]={'yes'},[WO CLT]={'yes','yes from work order','yes from case'}>

+

<[WO Complaint]={'yes'}, [Case CLT]={'yes','yes from work order','yes from case'}>

+
<[WO Complaint]={'yes'},
[WO CLT]={'yes','Yes from work order','yes from case'}>

}
[Case Number])

sunny_talwar

swuehl‌ the second condition is not.... do we use [Case CLT] -= {.....?

swuehl
MVP
MVP

Ok, missed that.

count(DISTINCT
{$

<
[Case Complaint]={'yes'},[Case CLT] -={"yes*"}>

+

<[Case Complaint]={'yes'},[WO CLT] -={"yes*"}>

+

<[WO Complaint]={'yes'}, [Case CLT] -={"yes*"}>

+
<[WO Complaint]={'yes'},
[WO CLT] -={"yes*"}>

}
[Case Number])

Anonymous
Not applicable
Author

Thank you, very helpful.