Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Set Analysis using AND+OR


Hi

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

5 Replies
nicolett_yuri
Partner Ambassador/MVP
Partner Ambassador/MVP

Try,


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

jduenyas
Specialist
Specialist

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

Sokkorn
Master
Master

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

Not applicable
Author

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

Anonymous
Not applicable
Author

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!