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]

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.

[#3]

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

[#4]

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.

[#5]

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!

[johnw]

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.

[johnw]

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)

[#8]

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. 

[#9]

Hi Gur,

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

Thanks,

SK

[#10]

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.

[#11]

Thanks for you help.  Corroboration is always reassuring.

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