Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe like this:
Sum({<[Quote Status]={R}>}
Aggr(
FirstSortedValue({<[Quote Status]={R}>} [Total Value of Quote], -[Version Number])
,SalesRep, QuoteNumber
)
)
Maybe like this:
Sum({<[Quote Status]={R}>}
Aggr(
FirstSortedValue({<[Quote Status]={R}>} [Total Value of Quote], -[Version Number])
,SalesRep, QuoteNumber
)
)
Thanks, worked perfectly!
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
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:
Got it,,,
Thank you.