Count with two conditions

[flames]

Hi,

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

VERSION	PRODUCT	AMT	MARGIN	QTY	COST (AMT-MARGIN/QTY)	NEED	HOW ?
**	A	1800	1200	1200	$                   0.50	1.00	COST( **,A )  /   COST(**,A)
**	B	400	150	400	$                   0.63	1.00	COST( **,B)   /   COST(**,B)
**	C	1000	600	500	$                   0.80	1.00	COST( **,C)   /   COST(**,C)
*AI	A	300	169	40	$                   3.28	6.55	COST( *AI,A )  /   COST(**,A)
*AI	B	320	170	50	$                   3.00	4.80	COST( *AI,B)   /   COST(**,B)
*AI	C	400	110	70	$                   4.14	5.18	COST( *AI,C)   /   COST(**,C)
*AI	D	300	170	30	$                   4.33	0.00	COST( *AI,D)   /   COST(**,D) [**, D not exists]
*BP	A	350	180	60	$                   2.83	5.67	COST( *BP,A )  /   COST(**,A)
*BP	B	380	192	120	$                   1.57	2.51	COST( *BP,B)   /   COST(**,B)
*BP	C	420	230	45	$                   4.22	5.28	COST( *BP,C)   /   COST(**,C)
*CA	A	390	200	40	$                   4.75	9.50	COST( *CA,A   /   COST(**,A)
*CA	B	390	210	70	$                   2.57	4.11	COST( *CA,B)   /   COST(**,B)
*CA	D	360	190	90	$                   1.89	0.00	COST( *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.

[uacg0009]

Hi Peekay,

I think below qvw is what you want:

Aiolos Zhao