sum a field based on the max value of another field

[benhumphrey]

Hi all,

I am trying to sum the Quantity Received based on the maximum price of the Component. Below is a table to try and demonstrate what I mean.

Component         Price        Qty

Product A            2.4            500

Product A            2.1            400

Product B            1.8            1000

So my dimension is component. And I have an expression to calculate the max price but I need to know how to then calculate the sum of the Qty bought in at the max price.

Any help is appreciated. Thank you in advance

[benhumphrey]

The quantity recieved column is a sum of all quantities we have recieved in against that component code regardless of price. What i want to know is out of the 400 total quantity what quantity was at 2.7. Im now getting some data together for you to look at.

[benhumphrey]

I have attached in excel what the Bill of materials table looks like and what the Purchase order price table looks like. I hope that this helps.

[MK_QSL]

what is the result you are looking for?

[benhumphrey]

Component          Highest Price          Highest Qty

CompA                         3.1                         200

CompB                         3.2                         800

CompC                         2.6                         200

CompD                         4.5                         200

CompE                         2.1                         250

CompF                         2.2                         120

CompG                         5.2                         100

CompH                         4.9                         600

CompI                            3                           250

If my maths is right thats what i think i should get

[MK_QSL]

Create a Straight Table

Dimension

Component

Expression

=Max(Cost)

=FirstSortedValue(Qty,-Cost)

[#7]

I think this might be what you want

[benhumphrey]

Its weird. This does work correctly on the data i provided you with but when i put it into my actual qvw it doesnt give a figure for all components only some of them. Would you happen to know why this is?

[MK_QSL · Accepted solution]

Try this

=FirstSortedValue(Distinct Aggr(Max(Qty),Component),-Aggr(Max(Cost),Component))

[benhumphrey]

Thanks very much to all of you for the help given! Its made my day.

[benhumphrey]

Thanks very much for this. If its not too much trouble could you briefly explain what it is doing? I know some of it but not all of it.