Qlik Community

Ask a Question

Documents

QlikView documentation and resources.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER

QlikView Technical Brief - AGGR.docx

abyqlik_com
Creator
Creator

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)
Attachments
Comments
fbl
Contributor II
Contributor II

Hi,

how would the expression look like, when I want to display Fred and the Top 3 others of the list ?

So basically a set returned by the ranking function + a static value

=concat(distinct IF(aggr(rank(sum({<Salesperson-={'Fred'}>}Sales)),Salesperson)<=3,Salesperson[add Fred here to the set of Salespersons]),CHR(13),-aggr(sum(Sales),Salesperson))

0 Likes
qlikviewwizard
Master II
Master II

Very good document. Thanks for sharing.

alexdataiq
Partner
Partner

If your having trouble using AGGR this might be the document for you. It's well explained and the author makes it easy to understand. Also, several simple yet useful example are provided to show some uses of AGGR.

0 Likes
Not applicable

Bit confused by in using the rank function along with the Aggr. In some example I have seen Rank used outside the Aggr. In this document, rank function is used inside the Aggr.

Just curious, isn't Rank compares things and ranked the items, so ins't outside Aggr, so the virtual table produced by the Aggr can br ranked.

Also it is assumed that internal table produced would be sorted from highest to lowest, just wonder if we need the reverse, how to do that.

Thanks.@aby

0 Likes
jkampmeijer
Contributor II
Contributor II

Thank you for sharing.

0 Likes
laureadiaz
Contributor III
Contributor III

This helped me understand aggr, thanks!

0 Likes
lidiavonkrond
Partner
Partner

‌Hi Adam,

Great function, i learned that aggr on a sorted table (in the rijgt way) increase the speed. But i do not realy understand why this is.  Can you tel me when to use order by and when not ?

thanks in advance

Lidia

0 Likes
siddharthsoam
Partner
Partner

Hi need help 

FirstSortedValue(Product,aggr(sum(Sales),Product)) is not returning any name though FirstSortedValue(Product,-aggr(sum(Sales),Product)) is giving me the minimum sales product

 

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