topic Re: AND OR combination condition in set analysis in QlikView

AND OR combination condition in set analysis

Fri, 07 Nov 2014 05:15:45 GMT

Hi All,

I have a requirement where I need to use OR condition in set analysis.

This is my exact requirement what i am looking for.

[GIDFLAG={'0'} and BS ={'A','B'} and region={'US'} and [Channel ={'A','B','C'} or ( Channel ={'D'} and custno={'1','2,'3'} )] and

vDeli={'LCD'} and PSI='S11]

Every time I have been failing in underlined condition.

how we can use above condition in set analysis function,Any suggestions should appreciates.

Thanks

Re: AND OR combination condition in set analysis

vardhancse — Fri, 07 Nov 2014 05:19:40 GMT

Even me also have same requirement, in set analysis we can get AND condition but OR condition I don't think so.

Re: AND OR combination condition in set analysis

jonathandienst — Fri, 07 Nov 2014 05:27:09 GMT

If I understand you right you want a set expression to filter as follows:

GIDFLA={'0'} and BS={'A','B'} and Region={'US'} and Channel={'A','B','C'}

Channel={'D'} and custno={'1','2,'3'}

Then use:

{<GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'A','B','C'}> + <Channel={'D'}, custno={'1','2,'3'}>}

HTH

Jonathan

Re: AND OR combination condition in set analysis

jonathandienst — Fri, 07 Nov 2014 05:30:25 GMT

If you want the first 3 terms to apply to the whole filter:

GIDFLA={'0'} and BS={'A','B'} and Region={'US'} and Channel={'A','B','C'}

GIDFLA={'0'} and BS={'A','B'} and Region={'US'} and Channel={'D'} and custno={'1','2,'3'}

Then:

{<GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'A','B','C'}> + <GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'D'}, custno={'1','2,'3'}>}

Jonathan

Re: AND OR combination condition in set analysis

vardhancse — Fri, 07 Nov 2014 05:35:32 GMT

Looking for OR condition in set analysis

Re: AND OR combination condition in set analysis

Fri, 07 Nov 2014 05:45:24 GMT

try like this swathiRaj,

sum({<GIDFLAG = {'0'},BS={'A','B'},region={'US'},channel={'A','B','C'},custno={'1','2','3'},vDeli={'LCD'},PSI={'s11'}>

+<GIDFLAG = {'0'},BS={'A','B'},regions={'US'},channel={'D'},custnp={'1','2','3'},vDeli={'LCD'},PSI={'s11'}>} sales)

Re: AND OR combination condition in set analysis

jyothish8807 — Fri, 07 Nov 2014 05:49:36 GMT

HI Swathi,

AS Jonathan mentioned above in set analysis if you want to use AND condition then write expression seperated by Comma(,)

eg:

sum({<GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'A','B','C'}>}sales)

For OR condition use(+) sign in set analysis:

sum({<GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'A','B','C'}> + <GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'D'}, custno={'1','2,'3'}>}sales)

Regards

Re: AND OR combination condition in set analysis

jonathandienst — Fri, 07 Nov 2014 06:22:06 GMT

Did you try what I suggested?

Re: AND OR combination condition in set analysis

Fri, 07 Nov 2014 06:31:29 GMT

Thanks for your replies.But In this below condition custno is depenpds on channel D only ,not on whole set.

GIDFLA={'0'} and BS={'A','B'} and Region={'US'} and

[Channel={'A','B','C'}

[Channel={'D'} and custno={'1','2,'3'}]]----this is separate set depends on CHannel D only not on whole set.

thanks

Re: AND OR combination condition in set analysis

jyothish8807 — Fri, 07 Nov 2014 06:37:05 GMT

Try this:

sum({<GIDFLA={'0'},BS={'A','B'},Region={'US'},[Channel={'A','B','C'}>+<Channel={'D'},custno={'1','2,'3'}>}sales)

Regards

Re: AND OR combination condition in set analysis

andrejfreitas — Fri, 06 Apr 2018 13:05:08 GMT

Hello,

I have a question:

Is there a way to add a fixed AND selection that will impact all the or's selection in the set analysis ?

For instance, using your example, let's say I would like to add Channel={'D'} in both part of the set analysis.

As a result, we would have something like:

sum({<GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'A','B','C'},Channel={'D'}> + <GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'D'}, custno={'1','2,'3'}>}sales); (this should work)

However, since Channel={'D'} is a common element in both part of the expressions, I was wondering if there is a way to write it once; Something like that:

sum({<Channel={'D'}>, <GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'A','B','C'}> + <GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, custno={'1','2,'3'}>}sales); (this shouldn't work)

That would mean, Channel={'D'} would impact in both sides of the expression. That would save a lot of time.

Any ideas?

Thank you!

Re: AND OR combination condition in set analysis

jyothish8807 — Mon, 09 Apr 2018 11:15:19 GMT

Try this once:

sum({<sales=p({<GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, Channel={'A','B','C'}>})+ p({<GIDFLA={'0'}, BS={'A','B'}, Region={'US'}, custno={'1','2,'3'}>}),Channel={'D'}>}sales)

Br,

Re: AND OR combination condition in set analysis

andrejfreitas — Tue, 10 Apr 2018 13:22:52 GMT

Thanks for your response.

It didn't work for me in this way. See my expression below.

sum({< BOCount=

p({<[Reporting Year]={'$(vCurrentReportingYear)'}>})+

p({<[Reporting Year]={'$(vPreviousReportingYear)'}>})+

p({<[Reporting Year]={'$(vPenultimateReportingYear)'}>}), Base_Example={1}>} BOCount)