Set Analysis using AND+OR

[Anonymous]

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

[nicolett_yuri]

Try,

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

[jduenyas]

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]

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

[#5 · Accepted solution]

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]

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!