topic Re: Help with Aggr Function / Max Function in QlikView

Help with Aggr Function / Max Function

chris1987 — Tue, 24 May 2016 14:09:58 GMT

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

Re: Help with Aggr Function / Max Function

swuehl — Tue, 24 May 2016 14:43:04 GMT

Maybe like this:

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

Aggr(

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

,SalesRep, QuoteNumber

)

Re: Help with Aggr Function / Max Function

chris1987 — Tue, 24 May 2016 15:43:54 GMT

Thanks, worked perfectly!

Re: Help with Aggr Function / Max Function

trdandamudi — Tue, 24 May 2016 16:19:41 GMT

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

Re: Help with Aggr Function / Max Function

swuehl — Tue, 24 May 2016 16:24:27 GMT

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

Re: Help with Aggr Function / Max Function

trdandamudi — Tue, 24 May 2016 16:26:04 GMT

Got it,,,

Thank you.