topic Re: Calculate Multiple IF Statements in QlikView

Calculate Multiple IF Statements

danielnevitt — Mon, 09 Dec 2013 18:05:51 GMT

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

Re: Calculate Multiple IF Statements

jerem1234 — Mon, 09 Dec 2013 18:10:07 GMT

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!

Re: Calculate Multiple IF Statements

Anonymous — Mon, 09 Dec 2013 18:17:15 GMT

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

Re: Calculate Multiple IF Statements

danielnevitt — Wed, 11 Dec 2013 10:31:56 GMT

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

Re: Calculate Multiple IF Statements

Anonymous — Wed, 11 Dec 2013 13:36:11 GMT

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".

Re: Calculate Multiple IF Statements

danielnevitt — Mon, 16 Dec 2013 15:50:21 GMT

Thank you very much for your help Michael