QlikView Documents

[abyqlik_com]

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.

[IAMDV]

One of my favourite functions. Thanks for sharing!

[#3]

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]

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]

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

[#6]

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]

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

[#8]

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]

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.

[#10]

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]

Hi,

Very well explained.

Thanks for sharing .