Help with Aggr Function / Max Function

[chris1987]

Hi,

I have a simple table that contains the following informatino that I'm trying to extract:

SalesRep

QuoteNumber

QuoteVersion

QuoteValue

QuoteStatus

What happens is that Sales Reps generate various quotes so ther could be version 1, version 2, version 3 etc. but I don't want to add all the values up, just get the "Newest" (highest numbered) quote.

What I'm trying to do is, in a listbox, Put the SalesRep in Column 1, then in Column 2 put the amount of the highest quotes (maximum version number of each quote) that is open (OrderStatus='R')

So for example:

SalesMan1 has quote 123 version 1 with a value of £100 and an QuoteStatus of "R"

SalesMan1 has quote 123 version 2 with a value of £50 and an QuoteStatus of "R"

SalesMan2 has quote 345 version 1 with a value of £200 and an QuoteStatus of "R"

SalesMan2 has quote 345 version 2 with a value of £500 and an QuoteStatus of "R"

SalesMan3 has quote 678 version 1 with a value of £900 and an QuoteStatus of "R"

SalesMan3 has quote 678 version 2 with a value of £400 and an QuoteStatus of "C"

In the listbox I will just be presented with:

SalesMan1 £50 (because the value of the highest open quote version is 2, which is for £50)

SalesMan2 £500 (because the value of the highest open quote version is 2, which is for £500)

SalesMan3 £900 (because the value of the highest open quote version is 1, which is for £900)

I've manager to get it working using the below function in but you have to put a filter on the quote number for it to work.

=money(sum({$<[Quote Status]={R},[Version Number]={$(=max({$}[Version Number]))}>}[Total Value of Quote]))

I think I need to use the Aggr() but I'm not 100%

Any help would be appreciated.

Cheers

Chris

[swuehl · Accepted solution]

Maybe like this:

Sum({<[Quote Status]={R}>}

     Aggr(

          FirstSortedValue({<[Quote Status]={R}>} [Total Value of Quote], -[Version Number])

     ,SalesRep, QuoteNumber

     )

)

[chris1987]

Thanks, worked perfectly!

[trdandamudi]

Stefan,

Just a quick question for my knowledge purpose..

Why do we need {<[Quote Status]={R}>} when we are already using it inside Aggr() function ?

Do you think the below will work ?

Sum(

    Aggr(

          FirstSortedValue({<[Quote Status]={R}>} [Total Value of Quote], -[Version Number])

    ,SalesRep, QuoteNumber

    )

)

Thanks

[swuehl]

Right, this probably works as well.

Without knowing the real data model, it's kind of difficult to exactely tell what's working or not.

So better safe then sorry:

Set Analysis in the Aggr function

[trdandamudi]

Got it,,,

Thank you.