Count with two conditions

flames — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Count with two conditions

uacg0009 — Wed, 30 Jan 2019 03:06:31 GMT

Hi Peekay,

I think below qvw is what you want:

Aiolos Zhao

topic Re: Count with two conditions in QlikView

Count with two conditions

Re: Count with two conditions