topic Re: Set Analysis using AND+OR in QlikView

Set Analysis using AND+OR

Anonymous — Tue, 27 Aug 2013 20:15:39 GMT

I'm having problems making this statement work (In a chart as an expression):

sum(if(([Group Id]) = 'HA' OR [Id] = 'R-950011' AND [Workflow Status Text] <> 'None', [Sales Lines - Net Sales Amount]))

I'm trying to sum the sales lines if either Group ID is = HA or Id is equal to 'R-950011', but at the same time all lines with workflow status 'None' should be exluded.

I think the solution is to use a set analysis, but I can't make my "OR" statment work in my set analysis formula:

Any ideas are appreciated

Re: Set Analysis using AND+OR

nicolett_yuri — Tue, 27 Aug 2013 21:02:09 GMT

Try,

Sum({<[Group Id]={'HA'}>+<Id={'R-950011'}>} [Sales Lines - Net Sales Amount])

Re: Set Analysis using AND+OR

jduenyas — Tue, 27 Aug 2013 21:45:12 GMT

Is [Sales Lines - Net Sales Amount] the name of your field or is it an expression

[Sales Lines] - [Net Sales Amount]

And note the following

You may still use the If statement with the correct grouping:

sum(if( ( ([Group Id]) = 'HA' OR [Id] = 'R-950011' ) AND [Workflow Status Text] <> 'None', [Sales Lines - Net Sales Amount]))

sum(if( ([Group Id]) = 'HA' OR ( [Id] = 'R-950011' AND [Workflow Status Text] <> 'None' ) , [Sales Lines - Net Sales Amount]))

sum(if( [Id] = 'R-950011' OR ( [Group Id]) = 'HA AND [Workflow Status Text] <> 'None' ) , [Sales Lines - Net Sales Amount]))

All of these statement will yield different results so you need to be careful when using AND and OR

Re: Set Analysis using AND+OR

Sokkorn — Wed, 28 Aug 2013 03:36:15 GMT

Hi Claus,

Did you try this yet:

Sum({$<[Group Id]={'HA'},[Workflow Status Text]-={'None'}> + $<[Id]={'R-950011'}>} [Sales Lines - Net Sales Amount])

Regards,

Sokkorn

Re: Set Analysis using AND+OR

Wed, 28 Aug 2013 19:26:23 GMT

Your statement looks correct, you are just missing a couple parentheses...

sum(if(( ([Group Id]) = 'HA' OR [Id] = 'R-950011' ) AND ( [Workflow Status Text] <> 'None'), [Sales Lines - Net Sales Amount]))

Re: Set Analysis using AND+OR

Anonymous — Tue, 03 Sep 2013 07:39:01 GMT

Hi everyone

Thank you for all your replies. I hadn't even considered the use of parentheses when using sum and if function. Also great input onh the set analysis function.

Thanks!