Calculate values on same column

[#1]

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

[shree909]

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

[vishsaggi]

My be this?

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

[sunny_talwar]

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))

[MarcoWedel]

another solution might be:

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

hope this helps

regards

Marco

[#6]

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)

[MarcoWedel · Accepted solution]

maybe

Load Type,

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

From YourSource

regards

Marco

[susovan]

Hi Mich,

You can also try this below expression

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

[#9]

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

[#10]

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