Calculate Multiple IF Statements

[danielnevitt]

Hi,

I have the following IF statements:

IF(MATCH(COST_REVENUE_IND,('Cost (Payable)')),PRICE2*PAYMENT_QTY)

IF(MATCH(COST_REVENUE_IND,('Revenue (Receivable)')),PRICE1*PAYMENT_QTY)

Is it possible to incorporate both of these statemens in an expression?  Basically I am trying to calculate the Price2xPaymentQty where the COST_REVENUE_IND = Cost and calculate the Price1xPaymentQty where the COST_REVENUE_IND = Revenue

Any help will be much appreciated.

Regards,

Daniel

[jerem1234]

Try this:

IF(MATCH(COST_REVENUE_IND,('Cost (Payable)')),PRICE2*PAYMENT_QTY, IF(MATCH(COST_REVENUE_IND,('Revenue (Receivable)')),PRICE1*PAYMENT_QTY))

if it matches cost, do Price2*Payment_QTY, else if it matches Revenue, then do Price1*Payment_QTY

Hope this helps!

[Anonymous]

Now, let's improve it a little.  This is a perfect candidate for pick(match()):

pick(match(COST_REVENUE_IND,'Cost (Payable)','Revenue (Receivable)'), PRICE2, PRICE1)*PAYMENT_QTY

Regards,

Michael

[danielnevitt]

Hi Michael,

Thank you for your reply, that works perfectly.

The last question I have is, I am trying to restrict the data shown to the following:

INTERNAL_IND=`-` AND STRATEGY_REFERENCE=`trading`,`Trading`,`TRADING`,`DEALING`,`Dealing` AND TRADE_TYPE=`Swap`

Is there a way of incorporation this with the code you provided in Set Expressions?  Alternatively would I be better setting a calculated dimension?  If so, can you explain how I can write multiple rules in calculated dimensions?

Thank you for you help.

Regards,

Daniel

[Anonymous · Accepted solution]

Yes, it can be done with set.  To use set analysis, there must be aggregation function, I assume it is sum() in your case.  So:
sum(
{<INTERNAL_IND={'-'}, STRATEGY_REFERENCE={'trading','dealing'}, TRADE_TYPE={'Swap'} >}
pick(match(COST_REVENUE_IND,'Cost (Payable)','Revenue (Receivable)'), PRICE2, PRICE1)*PAYMENT_QTY
)

Notice that I use 'trading' and 'dealing' only once - it is not case sensitive here.

As for the calculated dimension...  If you want to apply these rules for all expresions in the chart, it is better yo use calculated dimension, because you have to use the rules only once.  It could be something like this:

aggr(only({<INTERNAL_IND={'-'}, STRATEGY_REFERENCE={'trading','dealing'}, TRADE_TYPE={'Swap'} >} DimensionField),DimensionField)

And, you have to check "suppress when value is null".

[danielnevitt]

Thank you very much for your help Michael