Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
johnw
Champion III
Champion III

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))

View solution in original post

53 Replies
johnw
Champion III
Champion III

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
Author

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
Author

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
Specialist III
Specialist III

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;

johnw
Champion III
Champion III

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
Author

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
Author

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

Richard

johnw
Champion III
Champion III

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
Author

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