topic Re: Expression check ! in QlikView

Expression check !

amit_saini — Fri, 04 Mar 2016 04:54:24 GMT

Hi Folks,

I'm having below case in order to calculate Net Value :

Net Value :

The net value will be calculated as :

Formula:

If Debit_Credit_Indicator = H, Amt_Local_Curr is negative (i.e. Amt_Local_Curr is 9.207,87 the value to calculate with is –9.207,87)

If Debit_Credit_Indicator = S, Amt_Local_Curr is positive

Get for each entry selected Amt_Local_Curr and convert to either negative or positive value.

Calculate the converted values to a net total.

I.e. -100 + 50 = -50

To achieve this , I 'm doing below calculation :

Sum(If(Debit_Credit_Indicator='H', -1) * Amt_Local_Curr)+ Sum(If(Debit_Credit_Indicator='S', 1)* Amt_Local_Curr)

I hope this is correct or suggest what could be the best option.

Thanks,

jonathandienst — Fri, 04 Mar 2016 05:13:27 GMT

If Debit_Credit_Indicator can only be 'H' or 'S', then this will suffice:

=Sum(If(Debit_Credit_Indicator='H', -1, 1) * Amt_Local_Curr)

If there are other possible value that need to be ignored:

=Sum(If(Debit_Credit_Indicator='H', -1, If(Debit_Credit_Indicator='S', 1, 0)) * Amt_Local_Curr)

tresB — Fri, 04 Mar 2016 05:14:13 GMT

Try this:

Sum(Pick(Match(Debit_Credit_Indicator,'H','S'), -Amt_Local_Curr, Amt_Local_Curr))