Expression help

[danielnevitt]

Hi,

I have an expression that I wish to calculate on a field.

If TRADE_TYPE = Basis Swap then multiply PAYMENT_QTY*(PRICE1-PRICE2)

If TRADE_TYPE = Swap then multiply PAYMENT_QTY*PRICE1

I have created the following expressions for the above, however I am unsure how to express them as a multiple IF statement:

Sum({<TRADE_TYPE={'Basis Swap'}>}PRICE1-PRICE2)*PAYMENT_QTY

Sum({<TRADE_TYPE={'Swap'}>}PRICE1*PAYMENT_QTY

Any help would be much appreciated.

Thanks,

Daniel

[alexandros17]

the problem is where you place parenthesis:

Sum({<TRADE_TYPE={'Basis Swap'}>} (PRICE1-PRICE2)*PAYMENT_QTY )

Sum({<TRADE_TYPE={'Swap'}>}PRICE1*PAYMENT_QTY )

should have to work (it depends on data)

otherwise try this

Sum({<TRADE_TYPE={'Basis Swap'}>} (PRICE1-PRICE2))*sum(PAYMENT_QTY )

Sum({<TRADE_TYPE={'Swap'}>}PRICE1)*sum(PAYMENT_QTY )

[jerem1234]

Maybe try this:

if(TRADE_TYPE = 'Basis Swap', Sum({<TRADE_TYPE={'Basis Swap'}>}(PRICE1-PRICE2)*PAYMENT_QTY),

Sum({<TRADE_TYPE={'Swap'}>}PRICE1*PAYMENT_QTY))

Hope this helps!

[danielnevitt]

Hi Alessandro,

Thank you for your reply.

I am still receiving an error message when I set the expression in your reply.  Do I need to insert anything after the first expression?

I'm not sure if I was clear in my original message.  I require this calculation on the same expression.  Basically IF Basis Swap calculate, IF not calculate using the Swap expression.

Thanks,

Daniel

[#5]

Hi Daniel, In the above case you need use the if statement like below:

## If you have only 2 values Basis Swap , Swap in the TRADE_TYPE fields ##

SUM( IF(TRADE_TYPE='Bais Swap' , PAYMENT_QTY*(PRICE1-PRICE2) , PAYMENT_QTY*PRICE1 ))

## If you have more than these 2 values Basis Swap , Swap in the TRADE_TYPE fields ##

SUM( IF(TRADE_TYPE='Bais Swap' , PAYMENT_QTY*(PRICE1-PRICE2) , IF(TRADE_TYPE='Bais Swap' , PAYMENT_QTY*PRICE1 ,0 ) ))

I am inserting 0 in the TRADE_TYPE other that Basis Swap & Swap.

[alexandros17]

The expression must be placed EACH in one different expression ...

[danielnevitt]

Hi

Thank you for the replies.

The last question I have.  Is it possible to include in the expression that the COST_REVENUE_IND field must equal Cost for both parts of the expression?

Thanks,

Daniel

[#8 · Accepted solution]

Hi Daniel, You can add the new field with AND like below:

SUM( IF(TRADE_TYPE='Bais Swap' AND COST_REVENUE_IND='Cost', PAYMENT_QTY*(PRICE1-PRICE2) , IF(TRADE_TYPE='Bais Swap' AND COST_REVENUE_IND='Cost' ,PAYMENT_QTY*PRICE1 ,0 ) ))

[danielnevitt]

That works perfectly.  Thank you very much for your help.

[#10]

Your Welcome Daniel.