topic set analysis with multiple fields and/or in QlikView

set analysis with multiple fields and/or

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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]

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

AND….

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!

Re: set analysis with multiple fields and/or

Anonymous — Wed, 17 Jan 2018 17:22:36 GMT

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

Re: set analysis with multiple fields and/or

sunny_talwar — Wed, 17 Jan 2018 17:23:23 GMT

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.

Re: set analysis with multiple fields and/or

pradosh_thakur — Wed, 17 Jan 2018 17:45:37 GMT

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

Re: set analysis with multiple fields and/or

swuehl — Wed, 17 Jan 2018 17:51:44 GMT

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

Re: set analysis with multiple fields and/or

sunny_talwar — Wed, 17 Jan 2018 17:53:42 GMT

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

Re: set analysis with multiple fields and/or

swuehl — Wed, 17 Jan 2018 18:09:41 GMT

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

Re: set analysis with multiple fields and/or

Anonymous — Sat, 20 Jan 2018 15:37:01 GMT

Thank you, very helpful.