topic Re: Calculate values on same column in QlikView

Calculate values on same column

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

i am very new to Qlikview and i have a simple question:

I have a table with the following structure:

Type	Quantity
Sale	5
Sale	10
Return	2
Sale	15
Return	5

I want to add all Sales and subtract all Returns. So, for this example, i want to calculate the:

(5+10+15)-(2+5)

Althougth i searched it on the internet, i wasn't able to find something helpful. So, any advice will be approciated.

Thanks in advance and sorry for my bad english,

Michael

Re: Calculate values on same column

shree909 — Wed, 03 Aug 2016 15:38:58 GMT

Try this Sum({<Type={'Sale'}>}Quantity) -Sum({<Type={'Return'}>}Quantity)

Re: Calculate values on same column

vishsaggi — Wed, 03 Aug 2016 15:41:44 GMT

My be this?

= Sum({< Type = {'Sale'} >}Quantity) - Sum({< Type = {'Return'} >}Quantity)

Re: Calculate values on same column

sunny_talwar — Wed, 03 Aug 2016 16:09:13 GMT

I like what the above two have provided, but have lately developed a habit of using RangeSum().

RangeSum(

Sum({< Type = {'Sale'} >}Quantity),

-Sum({< Type = {'Return'} >}Quantity))

Re: Calculate values on same column

MarcoWedel — Wed, 03 Aug 2016 21:48:51 GMT

another solution might be:

Sum(If(Type='Return',-1,1)*Quantity)

hope this helps

regards

Marco

Re: Calculate values on same column

Thu, 04 Aug 2016 06:26:49 GMT

Thank you for your answers! I have one more question. Is it possible to made this calculation from the edit script, so it won't be necessary to make the same calculation again and again. I am wondering if there is something like Marco's solution, for example :

Load:

Type,

Quantity

If(Type=Return)

then Quantity*(-1)

Re: Calculate values on same column

MarcoWedel — Thu, 04 Aug 2016 08:48:58 GMT

maybe

Load Type,

If(Type='Return',-Quantity,Quantity) as Quantity

From YourSource

regards

Marco

Re: Calculate values on same column

susovan — Thu, 04 Aug 2016 09:01:28 GMT

Hi Mich,

You can also try this below expression

=Sum({<Type={'Sale'}>}Quantity)-Sum({<Type-={'Sale'}>}Quantity)

Re: Calculate values on same column

Thu, 04 Aug 2016 09:10:51 GMT

Hi Mich,

Try like this :

= Num(Sum(Sum({< Type = {'Sale'} >}Quantity),-Sum({< Type = {'Return'} >}Quantity)))

=Sum(Sum({< Type = {'Sale'} >}Quantity),-Sum({< Type = {'Return'} >}Quantity))

= rangeSum(Sum({< Type = {'Sale'} >}Quantity),-Sum({< Type = {'Return'} >}Quantity))

You can try this in Load statement:

Example:

Load

Type,

If(Type='Return',-Quantity,Quantity) as Qty

From Source_table.

Thanks,

Nandu

Re: Calculate values on same column

Fri, 05 Aug 2016 07:46:34 GMT

Thanks for your suggestions. It is working fine right now, based on Marco's reply.

Re: Calculate values on same column

MarcoWedel — Fri, 05 Aug 2016 22:04:45 GMT

You're welcome.

Glad it worked for you.

Marco

Re: Calculate values on same column

Wed, 14 Sep 2016 06:03:40 GMT

I have one more question: i would like to set a negative value to other fields also. For example, beside Quantity, i would like to set negative also the Amount. Something like:

If(Type='Return',[-Quantity, -Amount],Quantity,Amount) as Negatives

Ofcourse the syntax is wrong, but i assume that it would be something like that.

Re: Calculate values on same column

vishsaggi — Wed, 14 Sep 2016 15:51:55 GMT

May be this?

If(Type='Return', -Quantity,

IF(Type = 'Return', -Amount,

IF(Type <> 'Return', -Amount,

IF(Type <> 'Return', Quantity)))) as Negatives