Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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.
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 2nd OUTPUT table.
See attached qvw
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.....
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
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.
Hi Gysbert,
Thanks again for your help. Now very well understood.