topic Re: Net Calulation Help in QlikView

Net Calulation Help

amit_saini — Tue, 07 Jul 2015 05:31:40 GMT

Hi Folks,

Below is my condition for the Net cal:

Formula:

If MSEG-SHKZG = H, MSEG-DMBTR is negative (i.e. MSEG-DMBTR is 9.207,87 the value to calculate with is –9.207,87)

If MSEG-SHKZG = S, MSEG-DMBTR is positive

Get for each entry selected MSEG-DMBTR and convert to either negative or positive value.

Calculate the converted values to a net total.

I.e. -100 + 50 = -50

I'm trying for something like below for the Calculation :

if(Debit_Credit_Indicator='H',- sum(Amt_Local_Curr), if(Debit_Credit_Indicator='S',sum(Amt_Local_Curr)))

Any suggestions to calculate in the best way???

Thanks,

Re: Net Calulation Help

Kushal_Chawda — Tue, 07 Jul 2015 05:49:39 GMT

Hi,

Please see the attached file.

This is what you are looking for?

Re: Net Calulation Help

Tue, 07 Jul 2015 05:52:06 GMT

can remove 2 nd if right?

if(Debit_Credit_Indicator='H',- sum(Amt_Local_Curr),sum(Amt_Local_Curr))

Re: Net Calulation Help

amit_saini — Tue, 07 Jul 2015 05:55:48 GMT

Kush,

In ur case output should be +500 not -300.

Thanks,

Re: Net Calulation Help

jonathandienst — Tue, 07 Jul 2015 06:01:49 GMT

Avoid calculating the sum twice with:

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

Re: Net Calulation Help

amit_saini — Tue, 07 Jul 2015 06:01:50 GMT

Yes for sure you can do this.

Thanks,
AS

Re: Net Calulation Help

jagan — Tue, 07 Jul 2015 06:03:05 GMT

Hi,

Try like this using set analysis

=-sum({<Debit_Credit_Indicator={'H'}>} Amt_Local_Curr) + sum({<Debit_Credit_Indicator={'S'}>}Amt_Local_Curr)

Hope this helps you.

Regards,

Jagan.

Re: Net Calulation Help

amit_saini — Tue, 07 Jul 2015 06:08:20 GMT

Thanks Jonathan!

looks to me this is right approach.

Thanks,
AS

Re: Net Calulation Help

amit_saini — Tue, 07 Jul 2015 06:08:35 GMT

Thanks Jagan