Aggr, with sum of one value based on max of another.

[#1]

Hi All,

I'm after a total quote value based on the latest revision of the quote.

Sample Data.

Quote               Version               Value

Q1                    1                         1000

Q1                    2                         1200

Q1                    3                         1100

Q2                    1                         800

Q3                    1                         1200

Q3                    2                         1500

So I'd like a formula that gives me a total of 3400 from the above.

Based on

Q1          3          1100

Q2          1          800

Q3          2          1500.

I've tried variations of max(aggr(Sum(Value)),Quote,Version)) but this just gives me the highest quote value of 1500.

Thanks

Steve

[chrismarlow]

As an expression FirstSortedValue(Value,-Version) returns the values that sum to 3400, so 3rd column in your second grid, then changing the total mode of the chart would give 3400, but is that what you are after, or a single 3400 total?

[morganaaron]

If so, the equation would be:

=Sum(Aggr(FirstSortedValue(Value, -Version), Quote))