Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
danielnevitt
Creator
Creator

Calculate Multiple IF Statements

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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

View solution in original post

5 Replies
jerem1234
Specialist II
Specialist II

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
Not applicable

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
Creator
Creator
Author

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
Not applicable

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
Creator
Creator
Author

Thank you very much for your help Michael