Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Understanding Aggr with visuals

Not applicable

Understanding Aggr with visuals

Aggr can be a tough concept to grasp at first, but once you understand exactly what is happening behind the scenes it can become a powerful tool for any developer.

In short Aggr creates a table in-memory.  So when you write an expression like this:

Aggr(sum(Sales),CompanyName)

The table that QlikView creates in-memory looks like this:

Table 1:

CompanyName

sum(Sales)

$21,679.88

Autokleider

$1,646.23

Bond Ltd

$2,313.39

Champes

$983.82

Da Bikini Expertu

$2,426.98

Elle Fashion & Design

$691.11

Grunewald

$2,984.91

Nirvana Stores

$3,479.88

Ski Store

$3,551.24

The Corner Store

$840.36

Warp AG

$2,761.97

This can be useful if you need to see the top customer sales in a chart without CompanyName as a dimension. For example, if you had a chart with SalesPerson as the dimension and you wanted to see the single largest amount that each sales person sold to one company, you could write an expression like this:

Max(Aggr(sum(Sales),CompanyName) )

QlikView then evaluates the expression on every row and you get a table like this as your end result:

Table 2:

SalesPerson

Max(Aggr(sum(LineSalesAmount),CompanyName) )

$3,551.24

Michelle Tyson

$983.82

Tom Lindwall

$1,646.23

Rob Carsson

$2,313.39

Stefan Lind

$2,426.98

Frank Roll

$2,984.91

Helen Brolin

$3,479.88

Bob Park

$3,551.24

This may seem confusing but QlikView takes a couple of steps to arrive at its solution. Let’s look at Bob Park (the last row in the SalesPerson table above).

First the Aggr is applied and table 3 will be created in-memory for Bob Park

Table 3:

SalesPerson

CompanyName

sum(LineSalesAmount)

Bob Park

Elle Fashion & Design

$691.11

Bob Park

Ski Store

$3,551.24

Bob Park

Warp AG

$2,761.97

Then the Max expression is used on that table’s values.  You can see that 3,551.24 is the max value for Bob Park’s customers and is correctly returned in the table 2

Now that last example has its limitations, so let’s try one that is a little more practical.  Let’s say you need to show the top five customers by sales amount without using dimension limits (because we all know that dimension limits can negatively impact performance).

First we need to perform an Aggr on the sales amount by customer (CompanyName) just like we did earlier.  That will give us the table shown in Table 1.  Now we need to use Aggr to create the table in memory.  So the expression is Aggr(Sum(Sales),CompanyName).  Next we need to rank our customers according to their total Sales amount.  The Rank function should work quite nicely.  Table 4 displays what is now in-memory using the following expression:

Rank(Aggr(sum(Sales),CompanyName))

Table 4:

CompanyName

Rank(sum(Sales))

Ski Store

1

Nirvana Stores

2

Grunewald

3

Warp AG

4

Da Bikini Expertu

5

Bond Ltd

6

Autokleider

7

Champes

8

The Corner Store

9

Elle Fashion & Design

10

In order to meet our requirement we should use set analysis and the formula will look like this:

sum({<CompanyName={"=Rank(Aggr(sum(Sales),CompanyName))<=5"}>} LineSalesAmount)

To explain the formula, you insert the aggr formula in the set analysis because we want to create the in-memory chart that will rank our CompanyNames based on the Sales amount. Then we add our condition; the rank of company should be less than or equal to 5.  So we should see Ski Store to Da Bikini Expertu as our top five customers. Table 5 shows our end result

Table 5:

CompanyName

sum({<CompanyName={"=Rank(Aggr(sum(LineSalesAmount),CompanyName))<=5"}>} LineSalesAmount)

$15,204.97

Ski Store

$3,551.24

Nirvana Stores

$3,479.88

Grunewald

$2,984.91

Warp AG

$2,761.97

Da Bikini Expertu

$2,426.98


Comments
MVP & Luminary
MVP & Luminary

Nice explanation of the basics of AGGR(), thank you for posting!

I find it common for people to use AGGR() where it is not necessarily needed. For example, there is no need to use AGGR() in the last example, where you calculate Top 5 companies.

In Set Analysis, the Advanced Search condition that you are using, contains an implicit AGGR(). The search condition will get evaluated in the context of the field that's being selected. So, the last formula can be a lot simpler:

sum({<CompanyName={"=Rank(sum(LineSalesAmount))<=5"}>} LineSalesAmount)

Since the field in question is CompanyName, the Rank function will get evaluated for each CompanyName, which is identical to the AGGR() calculation.

I encourage everyone who is interested in advanced AGGR() techniques, to check out my lecture about AGGR() and Advanced Set Analysis at the Masters Summit for Qlik. This year, we have our sessions coming to Milan, Italy, and to Austin, TX.

best,

Oleg Troyansky

Check out my new book QlikView Your Business.

MVP
MVP

Caleb,

I fully agree that aggr() is not easy to understand for most beginners, thanks for taking the time to visualize some aspects.

It may be very useful to be able to see the actual data / model that you are using in your examples by attaching a QVW or a simple text file with some INLINE LOADed tables.

I do have some doubts that the samples you are showing are working as expected with any general kind of data for Sales / CompanyName / SalesPerson , but maybe only for data showing some specific relations between SalesPerson and CompanyName (1:n)

I believe you should include the chart dimension SalesPerson e.g. in the aggr() dimension list in table 2, i.e.

=Max(Aggr(sum(LineSalesAmount),CompanyName, SalesPerson) )


to avoid a possible grain mismatch as discussed in 4. in Pitfalls of the Aggr function

Regards,

Stefan

ecolomer
Honored Contributor II

Very good post.

Thank's for sharing

Enrique Colomer

Version history
Revision #:
1 of 1
Last update:
‎02-17-2016 05:06 PM
Updated by: