Two separate IF statements in one expression.

[neena123]

I have two separate IF statements I am trying to put into one expression in a straight table. It is only giving me back -1 and nothing else.

My expression is:

=IF([Payment Terms]='12',[Extended Amount]*0.02)

OR

IF([Bill To]='24973618' OR '24973619',[Extended Amount]*0.12)

How can I make this expression work without having to make two separate columns for it. Any help is much appreciated.

[sunny_talwar]

Use a nested if may be. But I need to know the logic before anything can be suggested.

Best,

Sunny

[rubenmarin]

Hi, you can do a nested if, be aware that if the first 'if' is true, the second is not executed:

=If([Payment Terms]='12',[Extended Amount]*0.02,

If([Bill To]='24973618' OR '24973619',[Extended Amount]*0.12))

In case of a [Bill To]='24973618' and also [Payment Terms]='12' it will do "[Extended Amount]*0.02" (the first 'If' it finds as true)

If you want both you can sum them:

=If([Payment Terms]='12',[Extended Amount]*0.02, 0)

+ If([Bill To]='24973618' OR '24973619',[Extended Amount]*0.12, 0)

[robert_mika]

Try:

=IF([Payment Terms]='12' OR [Bill To]='24973618' OR [Bill To]='24973619',[Extended Amount]*0.12)

[#5]

in order to put 2 inside you have to do it like this:

=

IF(

[Payment Terms]='12',     [Extended Amount]*0.02)

,

IF([Bill To]='24973618' OR '24973619',     [Extended Amount]*0.12)

,

'nothing' ))

[mato32188]

Hi,

I am not sure, if there are any other "limitations"...

in your load script create two flags.

....

IF(PaymentTerms=12, 0.02, 1) as @FlagA,

IF(BillTo = 24973618 OR BillTo = 24973619, 0.12, 1) as @FlagB

...  

then in expression calculate sum(Extended Amount * @FlagA * @FlagB).

Hope helps

M

[neena123]

Thanks guys for the fast reply! Marc I tried your approach however now it is calculating that the Payment Terms is true for all the data under the Payment Terms field not just when it equals 12. 

[neena123]

I applied your first expression however it is now calculating everything that doesn't match either Payment Terms=12 and Bill To=24973618' OR '24973619'. It works but I don't need for everything else that doesn't match to be calculated. 

[sunny_talwar]

You need to tell us what exactly the logic before we can solve this:

Is it Condition1 or Condition2 or Condition3? Condition1 and (Condition2 or Condition3)?

[Peter_Cammaert]

Martin has a point here. Expressions usually do some kind of aggregation, but I don't see any in your example. Maybe [Extended Amount] is the label of another (hidden) expression column? If not, is this a calculated dimension? If not, this can only work if there is only one data row per combination of dimension values...