Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

Last week Adam wrote a post about the Aggr() function and a Technical Brief about how to use it (Explaining the Aggr function). If you haven’t read it, I strongly recommend that you do. It is an excellent paper describing a function that can be used for nested aggregations - complex calculations where you need to make a second aggregation based on the result of a first aggregation.

However, I have noticed in the discussion forum that Aggr() often is used when it isn’t necessary. And since you get a performance penalty when you use the function, today’s post is about discouraging you from using it when you don't need to.

So, when should you not use it?

1. Standard Aggregations

Standard, non-nested aggregations, e.g. “=Sum(Sales)” or “=Count(distinct OrderID)” can be used almost anywhere in QlikView, in charts, in text boxes and as labels in any object. They will work directly as they are. Here you do not need any Aggr() function.

If you want to calculate the aggregation several times, e.g. once per customer, just use the aggregation function in a chart and use Customer as dimension. Nothing else.

This seems obvious, but I have on multiple occasions seen developers use the Aggr() function in such situations. Totally unnecessary - and it will just increase response times.

Bottom line: You should not use Aggr() for standard non-nested aggregations.

2. Calculation of a subtotal within the same chart

In some cases you need to use a subtotal from the same chart in your calculation. Then you should use the total qualifier inside your Sum() function (or other aggregation function). It will perform the given calculation disregarding one or several dimensions of the chart. It will however respect the selection.

For instance, the sales in percent can be calculated using

     Sum( Sales ) / Sum( total Sales )

This expression will return the sum of sales for each possible dimensional value, as a fraction of all possible sales. In other words; "Sum( total Sales )" disregards all dimensions of the chart.

Total.png

Bottom line: You should not use Aggr() for calculating subtotals that you can calculate with the total qualifier.

3. Calculation of an Average

If you can avoid the Aggr() function by writing your expression smarter, you should. One specific case is a standard average.

For example, say that you want to calculate the average order value. This is a nested aggregation: First you need to sum the sales value per order (an order can have several order lines), and then you need to average over the resulting set of sales values per order. In other words: You can calculate it using:

     Avg( Aggr( Sum( Sales ), OrderID ) )

But since it is a linear calculation, you can also calculate the same number just by dividing with the number of orders:

     Sum( Sales ) / Count( distinct OrderID )

… which in most cases is evaluated faster. Also, the latter expression is easier to understand for the person responsible for the application maintenance.

Bottom line: You should usually not use Aggr() for calculating an average that is a ratio of two separate aggregations.

4. Static aggregations

Sometimes you want to classify a dimensional value (customers, products, suppliers, etc.) using static aggregations, e.g. “Customers that only placed one order” or “Customers that bought goods at a total value higher than X”. “Static” meaning that you do not want the classification to change as you make a selection.

In such a case, the aggregation should not be made in the UI, but rather when creating the data model and stored as a separate field.

Bottom line: Aggregations for classification of dimensional values should often be made by creating an attribute field in the script using a “Group By”.

HIC

22 Comments
hic
Former Employee
Former Employee

You can't.

The data is not consistent with the numbers you want to achieve. The Sum(QTY) clearly sums to 22 but you want to show 27 instead. You want to count the A=5 twice since A belongs to two groups. But there is no way that QlikView can "know" that.

However, you can use Aggr() to define this type of logic. Bottom line: You must use Aggr() to do this.

HIC

0 Likes
4,610 Views
Not applicable

thanks for the prompt answer.

can u also throw some light on

1) In the screenshot below, do u use 'if' with dimensionality() to decide which expression should apply ?

unable to upload the picture (refer to the screenshot example in ur explanation with expressions and arrows at different levels)

2) Can we list out the difference between aggr() and total<fld> method ?

Regards

0 Likes
4,610 Views
hic
Former Employee
Former Employee

You could probably write some complicated formula using if() and dimensionality(), but that would be to complicate it. Look at the charts below.

Total.png

The top left chart contains the "real" numbers. The captions are the expressions with the total qualifier - exactly as I have written them. So by using the correct parameter within the brackets after the total you can specify which number you want.

The difference between aggr() and total:

  • "Total" always uses the grouping of the chart dimension(s). So it always returns a number that is a subtotal - a number that already is calculated in the chart.
  • The Aggr() is a way to define a different grouping than the one of the chart dimension: An additional grouping that is calculated before the calculations in the chart grouping are made.

HIC

0 Likes
4,568 Views
Not applicable

Hi,

thanks again for ur time.

i understand that we can have 4 different charts with these expressions, but i wanted to ask how to use them together in one chart as you've shown in ur main post denoting each by arrow. Have u made it possible without 'if' and 'dimenisonality()'

or you just suggested that such expressions could replace the aggr functions. ?

Aggr vs total: i undertood that total <fieldname> where field name shud be a part of the chart dimension already i.e. other dimensions can't be used in the chart.

But Aggr can use other dimensions also.

I hope i understood this right

0 Likes
4,568 Views
Not applicable

i understood now what u meant by the arrows.

0 Likes
4,568 Views
Anonymous
Not applicable

Thanks for sharing.

0 Likes
4,568 Views
rajkumarb
Creator II
Creator II

Very Useful, Thank You @ HIC

0 Likes
4,568 Views
roysomna
Contributor II
Contributor II

If we need to create buckets of customer order amount as below and perform an year over year analysis, Can we use the Aggr function ?

Selection  : Year = 2015

Order Amount                 # of Orders Current Year  - 2015             # of Orders Prior Year - 2014

>100k                                2                                                                 3

50K - 100k                          5                                                                4

<50K                                  60                                                               50

0 Likes
4,568 Views
alexis_garcia
Creator II
Creator II

Very useful, Thanks!

0 Likes
4,540 Views
Not applicable

This article is very usefull

0 Likes
4,540 Views