Aggr() function

[#1]

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

[johnw · Accepted solution]

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

[#3]

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.

[#4]

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]

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]

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)

[#7]

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

[#8]

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

Richard

[johnw]

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.

[#10]

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