topic Re: AGGR() Question in QlikView

AGGR() Question

Wed, 25 Nov 2020 16:16:04 GMT

Challenge: Compare ABC item price (lowest price) against all competitors. Show only records where ABC items (lowest priced item) is greater than ANY competitors's price (also lowest)

Dimensions:

Date, Competitors, Stores, Rtype

Competitors:

ABC, DEF, 123, 456

Stores: Store descriptions are the same on all competitors.

Items: Items descriptions are not the same on all competitors.

Metric:

Min(
aggr(
sum({<Competitor={"=min(aggr(sum({<Competitor ={'ABC'}>}PRICE),DepDate,Competitor,Stores,RTYPE)) >
min(aggr(sum({<Competitor-={'ABC'}>}PRICE),DepDate,Competitor,Stores,RTYPE))"},Competitor={"*"}>}
PRICE)
,DepDate,Competitor,Stores,RTYPE)
)

This seems to work except when there are more than one RTYPE records. It seems like it's summing all the RTYPE prices instead of using just the lowest price. "High Park" and "Almond" are multi records. I meant to compare it with only the lowest, not the sum of all RTYPE.

High Park SUM(PRICE) is equal to 27,000. the min() = 1541

Almond SUM(PRICE) = 15,000 min() = 2131

DepDate	Stores	Competitor	ABC	DEF	123	456
07/01/2017	High Park		1541	-	-	2066
07/01/2017	Quiapo Store	-	-	1365.32	-
07/01/2017	Tres Amigo	-	-	-	-
07/01/2017	ADAGIO ACC	-	-	-	-
07/01/2017	PARIS VILLAGE	-	-	-	-
07/01/2017	PARIS MONTMARTRE	-	-	-	-
07/01/2017	BEACH AND WELLNESS RESORT		5656	-	-
07/01/2017	RITMO CANADA		5226	-	-
07/01/2017	ALMOND		2131	-	-	2651
07/01/2017	CAP CAN		3648	3268	-	-

Any idea what I'm missing?

Re: AGGR() Question

Digvijay_Singh — Mon, 22 May 2017 00:57:00 GMT

I don't see RTYPE dimension values in your shared data, since you have used all four dimensions, just wondering what this data means, is this the output of your straight table?

Re: AGGR() Question

Mon, 22 May 2017 01:56:52 GMT

This is the expanded all. Rtype: View 4 is the MIN() = 1541 and Flower View mn() = 2131.

Date	Hotel	RTYPE	Competitor	ABC	DEF	123	456
07/01/2017	High Park	Vew 1		3791	-	-	-
07/01/2017	High Park	Vew 2		1971	-	-	-
07/01/2017	High Park	Vew 3		3201	-	-	-
07/01/2017	High Park	Vew 4		1541	-	-	-
07/01/2017	High Park	Vew 5		-	-	-	2066
07/01/2017	High Park	Vew 6		3381	-	-	-
07/01/2017	High Park	Vew 7		1671	-	-	-
07/01/2017	High Park	Vew 8		3201	-	-	-
07/01/2017	High Park	Vew 9		3311	-	-	-
07/01/2017	High Park	Vew 10		1621	-	-	-
07/01/2017	High Park	Vew 11		3311	-	-	-
07/01/2017	Quiapo Store	Penthouse		-	-	-	1365.32
07/01/2017	BEACH AND WELLNESS RESORT	Type 1		5656	-	-	-
07/01/2017	RITMO CANADA	Coconut View		5226	-	-	-
07/01/2017	ALMOND	Flower View 1		-	-	-	2651
07/01/2017	ALMOND	Flower View 2		2291	-	-	-
07/01/2017	ALMOND	Flower View 3		2131	-	-	-
07/01/2017	ALMOND	Flower View 4		2581	-	-	-
07/01/2017	ALMOND	Flower View 5		2581	-	-	-
07/01/2017	CAP CANA	Row 1		3648	3268	-	-

Re: AGGR() Question

Digvijay_Singh — Mon, 22 May 2017 19:22:23 GMT

Couldn't manage to correct your expression but it worked like below -

I added one more record through script to test one new case other than your data, it worked as desired.