Discussion Board for collaboration on QlikView Scripting.
I'm having below case in order to calculate Net Value :
Net Value :
The net value will be calculated as :
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.
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)
Sum(Pick(Match(Debit_Credit_Indicator,'H','S'), -Amt_Local_Curr, Amt_Local_Curr))