topic Re: Aggr() function in QlikView

Aggr() function

Thu, 10 Nov 2011 16:54:34 GMT

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

Re: Aggr() function

johnw — Thu, 10 Nov 2011 18:57:23 GMT

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

Aggr() function

Fri, 11 Nov 2011 09:42:53 GMT

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.

Re: Aggr() function

Fri, 13 Jul 2012 19:43:04 GMT

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.

Re: Aggr() function

nagaiank — Fri, 13 Jul 2012 22:14:16 GMT

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;

Re: Aggr() function

johnw — Mon, 16 Jul 2012 19:26:30 GMT

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)

Re: Aggr() function

Mon, 16 Jul 2012 20:02:46 GMT

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

Re: Aggr() function

Mon, 16 Jul 2012 20:03:15 GMT

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

Richard

Re: Aggr() function

johnw — Mon, 16 Jul 2012 20:30:08 GMT

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.

Re: Aggr() function

Mon, 03 Dec 2012 15:04:54 GMT

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

Re: Aggr() function

johnw — Tue, 04 Dec 2012 00:10:48 GMT

I'm not really set up to do billing, but you probably have better options anyway. You could just post questions to the forum, where plenty of experts may be willing to provide their advice. Send me a link to such posts, and if I have the time and the question looks interesting, I'll be happy to give my input. If you need more reliable support than relying on the kindness of strangers, there are a number of QlikView consulting firms ready to serve. I believe many of the top posters work for such firms. Of those posters, I've only actually worked with Rob Wunderlich, who is brilliant, deeply knowledgeable, and a pleasure to work with. But there are a lot of great consultants out there, ready and willing to do whatever you need.

So that you know, this year I've been mostly absent from the forum and have barely touched QlikView. I'm on a fairly different assignment right now. I like to hop on the QlikView forum when time allows to try to keep from forgetting all this, but my time is limited, and I'm more prone to mistakes this year since I'm no longer immersed in it.

If you're not sure how to send me a link to a post through this site, well, I actually don't either, but people send them to me frequently, so I assume it's simple. I'd rather not give out my direct contact information.

Re: Aggr() function

Wed, 05 Dec 2012 16:44:38 GMT

John,

I'm trying to get the Min/Min customer name using set analysis.

I have posted the question already.

Do you have a quick solution for pulling customer names from a Min/Max Sales Set Analysis expression?

Thanks

Re: Aggr() function

Wed, 05 Dec 2012 18:50:43 GMT

Great explanation, thanks. But can an AGGR "temporary table" work like this...

I want to do a SUM( IF ..., but want to do it on as few rows as possible. Consider the following:

SUM( IF( Month1 <= Month2, AGGR( SUM( Value ), Month1, Month2 ) ) )

I'm trying to do a pre-aggregation before doing the final aggregation.

What I'd expect this to do is to apply the IF comparisons to the AGGR dimensions; ie., do the AGGR once and apply the IF comparisons over the reduced number of rows.

Is this how it's supposed to work?

I tried testing it and it seems to help, but I expected better performance.

Re: Aggr() function

Wed, 05 Dec 2012 19:35:09 GMT

Hi John!

You can explain me this problem, i have a table with this records:

Type1,value1,value2

A,1,zz

A,2,xy

A,3,jk

B,2,lm

B,1,wx

how i can use the function Aggr to the result are this:

A,zz

B,wx

how you can see i need the records with value1 = 1

Thank's for your help!

Re: Aggr() function

johnw — Wed, 05 Dec 2012 20:44:37 GMT

DaveLyon wrote:
...I want to do a SUM( IF ..., but want to do it on as few rows as possible. Consider the following:
SUM( IF( Month1 <= Month2, AGGR( SUM( Value ), Month1, Month2 ) ) )
...
I expected better performance.

So you want the same results as a sum(if(Month1<=Month2,Value)), but were hoping to use aggr() to improve performance? I wouldn't expect it to help. Either way, QlikView must still process ALL of the underlying rows. In your aggr() format, it has to process all of them to sum up the rows for your aggr "temporary table". The sum(if(...)) part might then be fast, but you've already blown all the time in the aggr(sum(...)) part. That's what I suspect is causing it to be slow, in any case.

What I'd probably do is create a new Value field in the script that only exists on the desired rows:

if(Month1<=Month2,Value) as FlaggedValue

And then do a simple sum of that:

sum(FlaggedValue)

I believe QlikView would only look at the desired rows to do that sum, and so it would perform well, or as well as you're going to get (it won't perform well if 90% of the rows match your condition, but probably nothing would). But if I'm wrong, there are of course other approaches.

Re: Aggr() function

johnw — Wed, 05 Dec 2012 20:49:13 GMT

johnmarquinez wrote:
...
Type1,value1,value2
A,1,zz
A,2,xy
A,3,jk
B,2,lm
B,1,wx
how i can use the function Aggr to the result are this:
A,zz
B,wx
how you can see i need the records with value1 = 1
...

It's hard to know if this is the best answer with little context, but:

Dimension = Type1

Expression = only({<value1={1}>} value2)

Or create a flagged value in the script as mentioned in the previous post:

if(value1=1,value2) as FlaggedValue

Dimension = Type1

Expression = only(FlaggedValue)

Re: Aggr() function

Wed, 05 Dec 2012 20:52:08 GMT

I agree with John. Having spent a LOT of time working with this since my post months ago....the only way I have found to deal with the situation of having millions of rows of data that need to be operated on in some type of SUM expression is to not use aggr. It is just too slow. The workaound is to create the distinct values in a new column or new table during the LOAD. You should only need to put the values you plan to use within the calculations, as opposed to values you are filtering on in the expression. Hope that makes sense.

Re: Aggr() function

Wed, 05 Dec 2012 21:33:06 GMT

Hi Gur,

I would be interested for the remote support work. You can reach me at k.shivanand@gmail.com

Thanks,

Re: Aggr() function

Thu, 06 Dec 2012 14:10:20 GMT

Thanks, that's exactly what I wanted to know.

My problem now is that the formula I asked about is an expression in a chart and the Month2 date is a dimension in the chart.

I'll figure it out.

Thanks again for your help.

Re: Aggr() function

Thu, 06 Dec 2012 14:13:10 GMT

Thanks for you help. Corroboration is always reassuring.

I'll have to think about your comments, but I'll get it.