Qlik Community

QlikView Documents

Documents for QlikView related information.

QlikView Technical Brief - AGGR.docx

abyqlik_com
Contributor

QlikView Technical Brief - AGGR.docx

AGGR is a very powerful aggregation function that is sometimes overlooked in the user interface due to is not being properly understood or indeed a developer not being sure how it can be utilised. Often, a QlikView developer will revert to more complex scripting or pre-aggregating data to service an expression that is required in a chart, which can actually be solved by using AGGR.

Labels (1)
Comments
dvqlikview
Honored Contributor II

One of my favourite functions. Thanks for sharing!

Not applicable

Great article! 

Are there any performance concerns with using Aggr? 

Is it possible to provide the same functionality using set analysis - if so would that give you better performance?

abyqlik_com
Contributor

Hi,

Yes, Aggr() does affect performance. And in some cases a lot (especially if it isn't used correctly). When the Aggr() function is calculated, an additional aggregation step is introduced and this takes time to calculate. You would also have to take a considered approach if you are working with large data volumes.

You won't be able to get the same functionality using just Set Analysis.

Thanks.


Adam.

maxim_senin
Contributor III

Hi,

Agree, in general Set Analsys cannot substitute Aggr(), also no one can guarantee absence of performance issues when using Aggr() but once you feel a performance degradation you should start considering additional scripting (precalculated values, flags, data types, linking, etc.) and/or expression optimization.

As for the use of Aggr() personally I frequently use it in calculated dimensions in order to calculate yes/no (1/0) flag in order to define whether to display a row in a table or not.

Best regards,

Maxim

Not applicable

Hi,

Nice article.  For a usage like the text box with the top three salespeople, is there an easy way to sort the result by sales leader versus the alpha sort of the name that is the default that happens here?

Thanks,

D

abyqlik_com
Contributor

Hi,

You can add the actual rank to the front of the name using the same AGGR statement:

=concat(distinct IF(aggr(rank(sum(Sales)),Salesperson)<=5,aggr(rank(sum(Sales)),Salesperson)&' - '&Salesperson&CHR(13)))

Capture.JPG

Not applicable

Adam, nice that does the trick and sorts for my example as well... thanks..

If you just wanted to sort the result you had without adding the number, would you use this method or use the sort weight of concat, any recommmendation of which to use?

=concat(distinct IF(aggr(rank(sum(Sales)),Salesperson)<=5,Salesperson),' '&chr(13), -aggr(sum(Sales),Salesperson))

this has separator for concat option of space and carriage return as:  ' '&chr(13)

this has sort option as the aggr option:  aggr(sum(Sales),Salesperson)

Stephan pointed this out as a possibility on another article.

abyqlik_com
Contributor

There are a number of ways to achieve the same thing. In this example I have used the CHR(13) as the delimiter, and as per your point, I have used the "Sort Weight" of the concat() function. However, you would still have to use the AGGR statement in the sort weight here.

=concat(distinct IF(aggr(rank(sum(Sales)),Salesperson)<=5,Salesperson),CHR(13),-aggr(sum(Sales),Salesperson))

Using the minus symbol, you can tell QlikView if the sort is to be ascending or descending. Here we have the minus so it's descending.

I personally prefer adding the rank number or indeed the Actual Sales figure to the text object to enable us to visibly see the sort weight/value.

Thanks.

Not applicable

Adam,

Excellent post. Thank you.

In fact, AGGR() does not aggregate anything. It just creates a virtual table dimensionned by the dimension in arguments, like a pivot table does. (Well, it is what I have deduced but I am perhaps wrong)

Fabrice

geetaalhan
Contributor

Hi,

Very well explained.

Thanks for sharing .

Version history
Revision #:
1 of 1
Last update:
‎03-04-2013 05:24 AM
Updated by: