Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Highlighted
Not applicable

Aggr() function

Hi

Can anyone explain me how the Aggr() function works with beautiful example with data in different scenarios.

B'coz i'm getting confuse on this functionality. I've gone through the reference manual also..

1 Solution

Accepted Solutions
MVP
MVP

Re: Aggr() function

From an earlier post of mine (http://community.qlik.com/message/70711#70711):

Think of the aggr() function as making a temporary table. For instance, let's say we have this real table of data:

Customer, Value
A, 20
A, 30
B, 10
B, 15
C, 40

Now, as a nonsense requirement, let's say that we're looking for maximum value by customer, and then the minimum of THAT value across customers. Our max value by customer is A=30, B=15, C=40. Our min of those is 15. Note how we built a temporary table with three rows while calculating it. Aggr() is one way to build this temporary table. Our dimension is Customer, and our expression is max(Value). We could build this as a real chart, but instead we want to use it in an expression without doing so. The general format for aggr() is this:

aggr(expression, dimensions)

So in our case:

aggr(max(Value),Customer)

We're trying to extract the minimum from that temporary table, that list of values, so we just enclose it in min():

min(aggr(max(Value),Customer))

53 Replies
MVP
MVP

Re: Aggr() function

From an earlier post of mine (http://community.qlik.com/message/70711#70711):

Think of the aggr() function as making a temporary table. For instance, let's say we have this real table of data:

Customer, Value
A, 20
A, 30
B, 10
B, 15
C, 40

Now, as a nonsense requirement, let's say that we're looking for maximum value by customer, and then the minimum of THAT value across customers. Our max value by customer is A=30, B=15, C=40. Our min of those is 15. Note how we built a temporary table with three rows while calculating it. Aggr() is one way to build this temporary table. Our dimension is Customer, and our expression is max(Value). We could build this as a real chart, but instead we want to use it in an expression without doing so. The general format for aggr() is this:

aggr(expression, dimensions)

So in our case:

aggr(max(Value),Customer)

We're trying to extract the minimum from that temporary table, that list of values, so we just enclose it in min():

min(aggr(max(Value),Customer))

Not applicable

Aggr() function

John

Thanks a lot for your breify explaination on Aggr() function. Really it is good example to understood where easily. Now i'm clear on this and i need to play on this function.

Not applicable

Re: Aggr() function

This is a great explanation.  Thank you.  Could you take it 1 step further in this example?  The table is:

Number, Name, Office, Dept

1, Rich, NY, Staff

2, Rich, NY, Bank

3, Rich, Dallas, Lit

4, Rich, Dallas, Corp

1, Bob, London, Corp

2, Bob, NY, Corp

3, Bob, NY, Lit

7, Bob, DC, Bank

I need the output from the expression to be the MAX Number, Office and Dept within each Name.  So in this example, my output should be:

4, Rich, Dallas, Corp

7, Bob, DC, Bank

Can you please help as I am totally stuck?  Thanks very much.

nagaiank
Valued Contributor III

Re: Aggr() function

For this, you will be using FirstSortedValue function (and not Aggr)

Try this script:

load Name

     , Max(Number) as MaxNUmber

     , FirstSortedValue(Office,-Number) as LastOffice

     , FirstSortedValue(Dept,-Number) as LastDept

      resident (your table) Group By Name;

MVP
MVP

Re: Aggr() function

I agree with Krishna that you'd use firstsortedvalue() and his exact expressions.  If you're just making a single chart, and don't need to make selections on this data, and performance isn't an issue, I'd probably do it all inside of the chart instead of making a data table.  Either way, it's the same idea.

Dimension    = Name
Expression 1 = max(Number)
Expression 2 = firstsortedvalue(Office,-Number)
Expression 3 = firstsortedvalue(Dept,-Number)

Not applicable

Re: Aggr() function

John, thank you VERY much for the quick accurate response.   This is the first time I have used this forum and wow what a great experience.  I really appreciate it!

Richard

Not applicable

Re: Aggr() function

Thank you very much for the response.   This was very helpful!

Richard

MVP
MVP

Re: Aggr() function

I should probably warn you that the firstsortedvalue() function requires the Name/Number pairs to be unique to return the desired data in this case.  So as an example, if you added another row to your data:

7, Bob, London, Lit

You'd get nothing back for the office or department for Bob since which value to use is no longer defined.  I'm guessing this isn't a problem for your actual data, but if it is, you'd need to define what to do in that case, and the solution would be more complex.

Not applicable

Re: Aggr() function

Hi John,

Is there a way I can contact you directly ?

We (http://www.youappi.com) are using qlikview to analyze extensive daily traffic and need some remote highly professional support. Couple of hours a month to review and resolve critical issues.

Sure hope we can get some of your wisdom and help.

Best regards,

Gur

Community Browser