Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

DIVISION BY FIXED value of a variable

Hello,

I am trying to divide COST column by COST of VERSION=**. i am using below code for that.

[ (Sum({<VG={"POS"},METRIC={"AMT $"}>}VALUE)/1000000)   - (Sum({<VG={"POS"},METRIC={"MARGIN $"}>}VALUE)/1000000) )

/

(Sum({<VG={"POS"},METRIC={"QTY #"}>}VALUE)/10000 ]

/

[  (Sum({<VG={"POS"},VERSION={"**"},METRIC={"AMT $"}>}VALUE)/1000000)    -  (Sum({<VG={"POS"},VERSION={"**"},METRIC={"MARGIN $"}>}VALUE)/1000000) )

/

(Sum({<VG={"POS"},VERSION={"**"},METRIC={"QTY #"}>}VALUE)/10000 ]

But, the above code is doing COST/COST - for respective VERSIONS. But I need all the costs should be divided by hte cost of VERSION = ** .

VERSIONAMTMARGINQTYCOST (AMT-MARGIN/QTY)GETTINGBUT I NEED
**$3,195.15$1,635.472,659$0.59$1.00$1.00
*AI$544.54$377.88149$1.12$1.00$1.91
*BP$584.71$355.52345$0.66$1.00$1.13
*CA$1,441.26$764.90612$1.10$1.00$1.88

 

How can i achieve the result of 

COST/Cost of VERSION=**.

Thanks.

Labels (2)
2 Solutions

Accepted Solutions
Highlighted

Try this

((Sum({<VG = {"POS"}, METRIC = {"AMT $"}>} VALUE)/1000000) -
(Sum({<VG = {"POS"}, METRIC = {"MARGIN $"}>} VALUE)/1000000)) / (Sum({<VG = {"POS"}, METRIC = {"QTY #"}>} VALUE)/10000) / ((Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"AMT $"}>} VALUE)/1000000) -
(Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"MARGIN $"}>} VALUE)/1000000)) / (Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"QTY #"}>} VALUE)/10000)

View solution in original post

Highlighted

Based on the sample provided... this type of expression should work

Alt(Sum(COST)/Sum(TOTAL <PRODUCT> {<VERSION = {'**'}>} COST), 0)

image.png

View solution in original post

4 Replies
Highlighted

Try this

((Sum({<VG = {"POS"}, METRIC = {"AMT $"}>} VALUE)/1000000) -
(Sum({<VG = {"POS"}, METRIC = {"MARGIN $"}>} VALUE)/1000000)) / (Sum({<VG = {"POS"}, METRIC = {"QTY #"}>} VALUE)/10000) / ((Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"AMT $"}>} VALUE)/1000000) -
(Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"MARGIN $"}>} VALUE)/1000000)) / (Sum(TOTAL {<VG = {"POS"}, VERSION = {'**'}, METRIC = {"QTY #"}>} VALUE)/10000)

View solution in original post

Highlighted
Contributor III
Contributor III

Thank you Sunny, That helped. 

I am alos stuck with another thing in the similar prolem, there is a new column PRODUCT.

and i need to take care of it while divided by...

like, Cost of VERSION = AI , PRODUCT = A should be divided by COST of VERSION =**, PRODUCT = A and so on.

please help in this, thanks in advance.

 

here is the table explanation.

 

VERSIONPRODUCTAMTMARGINQTYCOST (AMT-MARGIN/QTY)NEEDHOW ?
**A180012001200 $                   0.501.00COST( **,A )  /   COST(**,A)
**B400150400 $                   0.631.00COST( **,B)   /   COST(**,B)
**C1000600500 $                   0.801.00COST( **,C)   /   COST(**,C)
*AIA30016940 $                   3.286.55COST( *AI,A )  /   COST(**,A)
*AIB32017050 $                   3.004.80COST( *AI,B)   /   COST(**,B)
*AIC40011070 $                   4.145.18COST( *AI,C)   /   COST(**,C)
*AID30017030 $                   4.330.00

COST( *AI,D)   /   COST(**,D)

[**, D not exists]

*BPA35018060 $                   2.835.67COST( *BP,A )  /   COST(**,A)
*BPB380192120 $                   1.572.51COST( *BP,B)   /   COST(**,B)
*BPC42023045 $                   4.225.28COST( *BP,C)   /   COST(**,C)
*CAA39020040 $                   4.759.50COST( *CA,A   /   COST(**,A)
*CAB39021070 $                   2.574.11COST( *CA,B)   /   COST(**,B)
*CAD36019090 $                   1.890.00COST( *CA,D)   /   COST(**,D)
Highlighted

Based on the sample provided... this type of expression should work

Alt(Sum(COST)/Sum(TOTAL <PRODUCT> {<VERSION = {'**'}>} COST), 0)

image.png

View solution in original post

Highlighted
Community Manager
Community Manager

Is this QlikView? I would like to confirm and move this thread to the QlikView forum. Thanks
Sue Macaluso