7 Replies Latest reply: May 14, 2013 3:02 AM by Manish Kachhia RSS

    Find Min From Multiple Rows

    Manish Kachhia

      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..

        • Re: Find Min From Multiple Rows

          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.

            • Re: Find Min From Multiple Rows
              Manish Kachhia

              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.

            • Re: Find Min From Multiple Rows
              Gysbert Wassenaar

              See attached qvw