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]

You can use set analysis:

sum({<Price = {$(=max(Price))}>} Qty)

[benhumphrey]

Thanks for the quick response. I have tried using your set analysis expression but it returns 0.

[#4]

You need to match the fields exactly( Price = Your Price Field, Qty = Your Quantity Field). Not sure if this is the problem though. Can you post your qvw?

[MK_QSL]

May be

=FirstSortedValue(Qty,-Price)

[venkatg6759]

sum({<Price = {"$(=max(Price))"}>} Qty)

[#7]

Do you mean you want to find the max price and then multiply that against the quantity purchased at that price?  If I am understanding correctly you'll want to use =sum({<Price = {$(=max(Price))}>} Qty * Price) as the expression.

[benhumphrey]

I changed it to match the fields names I am using. Unfortunately I can't post the qvw as it contains sensitive info. Sorry not to be much help

[#9]

Are there multiple components sold at the max price or just one component?

[benhumphrey]

Ideally yes I would but I need to display the Qty purchased at the highest price as well. Which is where I'm having trouble with the expression.