Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
Henric_Cronström

The Aggr() functions is one of the most advanced functions in the Qlik engine, and it is not always easy to use. This blog post is about its most common pitfalls.

This is a function that enables the app developer to create nested aggregations, i.e. aggregations in two steps. Essentially the Aggr() is a For-Next loop where each loop contains a measure calculation. When all loops have been performed, a final aggregation based on the array of calculated measures can be made.

If we, for instance, want to calculate the monthly product sales, we could do it by using

Aggr( Sum(Sales), ProductName, OrderMonth )

This will produce a virtual table that looks like

Image1.png

Note that there is exactly one row per distinct combination of the two dimensions.

So, the Aggr() function creates a virtual table; an array of values that need to be aggregated in a second step. Hence you should wrap the Aggr() in an outer aggregation function. For instance, if you want to use the above table to calculate the average monthly sales, you should use

Avg( Aggr( Sum(Sales), ProductName, OrderMonth ))

 

Image2.png

 

With this mental picture of how the function works, we can start looking at the pitfalls.

 

1. Missing inner aggregation function

The first parameter of the Aggr() is a measure, and as such it is always an aggregation. Hence, you should use an aggregation function. If you don’t, the calculation will use Only() and the virtual table may inadvertently get NULLs in the Measure column. See more on Use Aggregation Functions!

 

2. Missing outer aggregation function

The outer aggregation function is needed whenever Aggr() returns more than one value. If no aggregation function is specified, Only() will be used, which may cause your chart measure to contain NULLs.

Note the totals line in the chart above: It shows the average over both products and months. Generally, totals will always be NULL if the outer aggregation is omitted.

 

3. Missing Set Analysis expression

So, there are both inner and outer aggregation functions. Where do I put my Set Analysis expression?

The answer is often “In both”. It may not be enough to have it in only one of the levels.

The current selection (or the relevant selection state) will always affect the aggregation scope of an aggregation function, unless you specify otherwise using Set Analysis. So, if you need Set Analysis you should in most cases have similar or identical Set Analysis expressions in both the inner and outer aggregation function.

 

4. Grain mismatch

When an Aggr() is used in a chart, you have inner dimensions in the Aggr() and outer dimensions in the chart. Then it is important that these match.

The grain of the Aggr() dimensions must be identical or finer than that of the chart dimensions.

For example, an Aggr() with Month as dimension can be put in a chart that has Year as dimension. In the following table, the average monthly sales numbers are correctly calculated, and displayed as an average per year and product.

 

Aggr correct grain.png

 

But the opposite isn’t necessarily possible. If you put an Aggr() with Year as dimension in a chart with Month as dimension, you will get problems. You may get a table that looks like this:

 

Aggr incorrect grain.png

 

Here, the Aggr() function has produced one number per product and year, and this number has been assigned to one single month while the other months contain NULL. This is a result of the grain mismatch and most likely not what you want.

With this, I hope that you have a better understanding of Aggr().

HIC

 

Further reading related to this topic:

AGGR...

When should the Aggr() function NOT be used?

Use Aggregation Functions!

23 Comments
robert_mika

Thank you HIC.

0 Likes
8,767 Views
christian77
Partner
Partner

What about the NODISTINCT modifier?

Thanks.

0 Likes
8,767 Views
Henric_Cronström

In most cases you should not use the NoDistinct qualifier.

However, if you have a grain mismatch and you know that you really want to have a grain mismatch, you should turn on NoDistinct. It usually produces the correct result. In the above case, you would get the yearly number assigned to all months, not just to one month.

HIC

0 Likes
8,767 Views
martyn43
Contributor II
Contributor II

Another clearly presented informative article

It would be useful to see the equivalent set analysis syntax, perhaps ogether with a couple of more complex set analysis examples.

Thanks again.

MK

0 Likes
8,767 Views
Henric_Cronström

The Aggr() function and Set Analysis are fundamentally different. You cannot substitute one with the other, so there is no "equivalent" Set Analysis syntax.

  • Aggr() (without Set analysis) is a way to make a nested aggregation using the record set defined by the current selection.
  • Set analysis (without Aggr()) is a way to make a simple aggregation using a different record set than what is defined by the current selection.

But a blog post with more advanced Set Analysis could still be a good idea...

HIC

0 Likes
8,767 Views
ecolomer
Master II
Master II

Thank's HIC

0 Likes
8,767 Views
NareshGuntur
Partner
Partner

Thank You HIC.

A clear insight as usual.

Naresh

0 Likes
6,357 Views
rva_heldendaten
Partner
Partner

Hi Henric!

Any chance to sort the values in an aggr-Dimension? When I do something like

concat(distinct Year& 'Performance: '&

   aggr(nodistinct

     sum(Sales)/above(sum(Sales)),

  Year)

,chr(10))

I would like to sort the Field "Year" ASC (or DESC).

Best regards,

Roland

0 Likes
6,357 Views
jason_michaelid
Luminary Alumni
Luminary Alumni

The third parameter of Concat() deals with sort orders, so try:

concat(distinct Year& 'Performance: '&

aggr(nodistinct

sum(Sales)/above(sum(Sales)),

Year)

,chr(10)

, aggr(nodistinct

sum(Sales)/above(sum(Sales)),

Year)

Put a “-“ before the aggr to sort descending instead of ascending.

Hope this helps,

Jason

0 Likes
6,357 Views
Henric_Cronström

Unfortunately not. However, we have internally recognized the need to sort the output of the Aggr(), so it is on my wishlist, as well as on yours...

HIC

0 Likes
6,357 Views