Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
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