Find Min From Multiple Rows

[MK_QSL]

I have below two tables…

Part Details:

PART	Annual Sale	PRICE	Supplier
Part 01	115	$ 100.00	A
Part 01	115	$ 120.00	B
Part 01	115	$ 115.00	C
Part 02	40	$   75.00	A
Part 02	40	$   70.00	B
Part 02	40	$   73.00	C
Part 03	450	$   42.00	A
Part 03	450	$   43.00	B
Part 03	450	$   41.00	C
Part 04	360	$   38.00	A
Part 04	360	$   32.00	B
Part 04	360	$   36.00	C
Part 05	1200	$   15.00	A
Part 05	1200	$   20.00	B
Part 05	1200	$   25.00	C
Part 06	185	$ 30.00	A
Part 06	185	$ 32.00	B
Part 06	185	$ 40.00	C

Supplier Details:

Supplier	Discount	Freight
A	10%	2%
B	8%	1%
C	11%	3%

Need Final Output like this…

Where,

Net Price = Price * (1 + Freight%) * (1 – Discount%)

OUTPUT 01:

PART	Cheapest Supplier	Cheapest Supplier's Net Price	Annual Sale	TOTAL
Part 01	A	$     91.80	115	$ 10,557.00
Part 02	B	$     65.04	40	$   2,601.76
Part 03	C	$     37.58	450	$ 16,913.12
Part 04	B	$     29.73	360	$ 10,702.80
Part 05	A	$     13.77	1200	$ 16,524.00
Part 06	A	$     27.54	185	$   5,094.90

OUTPUT 02:

Supplier	Number of Part where Cheapest Supplier	Annul Sale Total
A	3	$ 32,175.90
B	2	$ 13,304.56
C	1	$ 16,913.12

Appriciate you urgent help..

[#2]

Hi,

For OUTPUT 01, use Part as Dimension, then i think you should use an expression like ONLY({<Supplier={"=rank(-min(Price),Supplier)=1"}>}Supplier) for Cheapest Supplier, which means that you should only take the Supplier that has the lowest Price. then you probably can use an expression like ONLY({<Supplier={"=rank(-min(Price),Supplier)=1"}>}Price*(1+Freight%)*(10Discount%)) for the Cheapest Supplier's Net Price and ONLY({<Supplier={"=rank(-min(Price),Supplier)=1"}>}Annual Sale) as Annual Sale. The Total column can be determined by column(2)*column(3).

Hope this can help you creating this output.

[MK_QSL]

I have made variables and assigned respective values.

Discount : vDisA, vDisB and vDisC

Freight : vFreightA, vFreightB, vFreightC

For the first OUTPUT table…

I have used below expressions

Cheapest Supplier = FirstSortedValue(Supplier,

if(Supplier='A',PRICE*(1+vFreightA)*(1-vDisA),
if(Supplier='B',PRICE*(1+vFreightB)*(1-vDisB),
PRICE*(1+vFreightC)*(1-vDisC)))

Net Price = =FirstSortedValue(if(Supplier='A',PRICE*(1+vFreightA)*(1-vDisA),
if(Supplier='B',PRICE*(1+vFreightB)*(1-vDisB),
PRICE*(1+vFreightC)*(1-vDisC)
)),

          (if(Supplier='A',PRICE*(1+vFreightA)*(1-vDisA),

          if(Supplier='B',PRICE*(1+vFreightB)*(1-vDisB),
PRICE*(1+vFreightC)*(1-vDisC))))

But don’t have idea how to get 2^nd OUTPUT table.

[Gysbert_Wassenaar · Accepted solution]

See attached qvw

[MK_QSL]

Hi Gysbert,

Looks absolutely working.

I am little bit curious about expressions you have used in second Table...

SUM(AGGR(IF(RANK ....

Could you please be kind to give me brief idea how they are working (atleast for my example)?

Thanks.....

[MK_QSL]

can someone please explain what below expression is for?

this is from Mr Gysbert's qvw file.

sum(aggr(if(rank(-PRICE*(1+Freight/100)*(1-Discount/100))=1,1,0),PART,Supplier))

Specially, what -Price..... in RANK suggest...

Your immediate help would be highly appriciated...

Thanks

[Gysbert_Wassenaar]

PRICE*(1+Freight/100)*(1-Discount/100) is your net price. You want the minimum net price. The rank function returns the ranking, where the highest value gets rank 1. By multiplying the net price with -1 the lowest net price gets rank 1. So

rank(-PRICE*(1+Freight/100)*(1-Discount/100)) will return 1 if the net price is the lowest net price. Because the lowest net price per PART-Supplier combination is required the aggr function is needed to aggregate over PART and Supplier.

[MK_QSL]

Hi Gysbert,

Thanks again for your help. Now very well understood.