Discussion board where members can get started with QlikView.
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
You need to add this sentence to script:
aux: // here put a table name that aren't used in the application
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
Expression: Sum (if(PRODUCT_aux=PRODUCT,QTY)) - Sum (if(PRODUCT_aux=PREVIOUS_PRODUCT,QTY))
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