Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
flames
Contributor III
Contributor III

Count with two conditions

Hi,

I want to calculate a division. The Data and logic goes as follow:

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.00COST( *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)

 

 The logic is : All costs should be divided by the Cost of ** with their respective Product.

for example:

Row 1: cost (**, Product A) was divided by same thing. - so it's 1.

 

Row 4 : (Blue marked)

Cost of (*AI, Product A) was divided by Cost (**, Product A)

also, if product not exists in **, it can write 'Zero'. exmple marked in RED.

Here is the working query with out having Product column. Now looking for with Product column.

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

 

Thanks again to Sunny_talwar for that. 

Appreciate your Help, Thanks.

 

1 Reply
uacg0009
Partner - Specialist
Partner - Specialist

Hi Peekay,

I think below qvw is what you want:

Count with two conditions.PNG

Aiolos Zhao