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