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

[#2]

See the attached qvw, I feel like this is similar to what you are doing.

[benhumphrey]

Multiple components could have the same maximum price yes

[benhumphrey]

That is exactly what i would like. On a plus note the set analysis does work as i want it to if that component is selected, but the expression returns 0 when no component is selected and is just showing a default straight table, but i would like it to return the value even when the component is not selected.

[MK_QSL]

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

[benhumphrey]

Hi,

This returns some figures and doesnt against some others. The qty is wrong and for some reason when placed in a straight table with a normal sum(Qty) doubles or even triples this number

[MK_QSL]

Can you provide some sample data alongwith your required result?

[#8]

Could you provide some context? Where are you looking to use this expression and why?

[benhumphrey]

Okay. The way the data tables currently are is as follows;

Bill of materials - Contains Fields - Parent, Component and Lvl.

Purchase Orders - Contains Fields - Qty, Cost and Component

The purchase orders links through on Component to the Bill of materials as this is what we buy in. The Parent is purely used as a dimension to find all of the components associated with it so we can look at prices per product. The straight table will look like this.

Parent Code    Level      Component      Qty Recieved    Highest Price     Highest Qty

Parent A               1         CompA                   500                    2.5                         ?

Parent A               2         CompB                   400                    2.7                         ?

Parent A               2         CompC                   100                    1.1                         ?

Parent A               3         CompD                   900                    0.8                         ?

And so on for all the components that make up the Parent. What i want to do is identify what qty of each component did we buy in at the highest price versus the qty we bought in at the lowest price.

I hope this helps some more.

[#10]

So in your example Highest Quantity should be 400?

[MK_QSL]

Provide data please...!