Measure expression by looking up field based on other field value

[#1]

Hello Everyone,

I am new to QlikView and trying to calculate Net Gain measure as below. 

The Sales Lost is calculated by looking up the Product name in another field, is there a way to calculate below via measure expressions?

CUSTOMER ORDER_ID PRODUCT  QTY PREVIOUS_PRODUCT
101  101001  ABC  100 DEF
102  101002  DEF  120 ABC
103  101003  ABC   80 DEF
104  101004  ABC  170 DEF

Net Gain = Sales Gain - Sales Lost (WHERE PREVIOUS PRODUCT = ABC)

ABC  = (100 + 80 + 170) - 120
   = 230

DEF = (120) - (100 + 80 + 170)
  = -230

  NET GAIN
ABC  230
DEF -230

Appreciate your help

Thanks!

[sebastiandperei]

You need to add this sentence to script:

aux:               // here put a table name that aren't used in the application

Load Distinct

PRODUCT as PRODUCT_aux   // Make sure that PRODUCT_aux isn't used in the app

Resident Table:    // Replace "Table" by the name of the table who contents the "PRODUCT" Field

Dimmention: PRODUCT_aux

Expression: Sum (if(PRODUCT_aux=PRODUCT,QTY)) - Sum (if(PRODUCT_aux=PREVIOUS_PRODUCT,QTY))

[#3]

Hi Sebastian

Thanks for the reply.

May be i am missing something. The above expression works well if I am introducing and reporting the metric by new dimension "PRODUCT_aux".

Is there a way to compute & report by Product dimension itself - either by looking up/mapping by name of the product? I will have to report other metrics along with Net Gain by Product dimension

Something like this

PRODUCT NET_GAIN QTY_SOLD DISTINCT_CUSTOMERS

ABC 230 330 3

DEF -230 120 1

Thanks