set analysis with multiple fields and/or

[Anonymous]

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!

[Anonymous]

Try this: Set Analysis Wizard for QlikView | qlikblog.at

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

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

[swuehl]

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]

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]

Thank you, very helpful.