Hi Joe,
Take this one first: Aggr(Sum(Sales),Year,Customer).
Aggr statements always have one expression, Sum(Sales) in this case, and one or more dimensions , Year and Customer. Aggr works like an in-memory straight table.
| Year | Customer | sum(Sales) |
|---|
| 2015 | A | 1067 |
| 2015 | B | 1037 |
| 2015 | C | 1505 |
| 2016 | A | 1431 |
| 2016 | B | 1661 |
| 2016 | C | 1429 |
| 2017 | A | 1399 |
| 2017 | B | 1121 |
| 2017 | C | 1702 |
When the statement is executed in the chart row with customer dimension = A QlikView thinks about this:
| Year | Customer | sum(Sales) |
|---|
| 2015 | A | 1067 |
| 2016 | A | 1431 |
| 2017 | A | 1399 |
This returns an array that is no good to us so we need to wrap it in an aggregation function to reduce it to a single value of the expression Sum(Sales). So Max( Aggr(Sum(Sales),Year,Customer)) returns the single value 1431 for Customer A.
Aggr(If(Rank(Sum(Sales),1,1)=1,Year), Customer,Year) creates in in-memory straight table that returns the Year with the highest ranked sales. This Aggr() expression would work most times without being wrapped in an aggregation function. The only time that would fail would be when there is a tie for first place. If there is a tie Aggr() would return an array and so we enclose it in a suitable aggregation function - concat.
Regards
Andrew
