The Aggregate function (AGGR) is a great function to use in dashboards in the right circumstances. Often when starting to use this function, the difficulty lies with the inability to visualise what the function is actually doing behind the scenes.
For those that have never come across this function, the AGGR essentially creates a virtual table in memory based off your expression/measure. This is grouped over one or multiple dimensions.
For example, the following virtual table is created when the following expression is used – Aggr(Sum(UnitSales),Customer)
| Customer | Aggr(Sum(UnitSales),Customer) |
| Astrida | 23 |
| Betacab | 32 |
| Canutility | 27 |
You can also add additional functions, such as SUM, AVG or MAX, to an AGGR expression.
For example, taking the AGGR expression used in the table above, you can include a Max function to get the Maximum Sales Unit value per customer.
Max(Aggr(Sum(UnitSales),Customer)) = 32
The table below shows mock data based around the unit sales of numerous products for one customer. It highlights how the aggregate function works as a direct comparison against a similar expression. In this example it shows how the sum of unit sales can be calculated as a normal expression in comparison with the sum of unit sales which is aggregated by customer.
| Customer | Product | Unit Sales | Sum(UnitSales) | Aggr(Sum(UnitSales),Customer) |
| Betacab | BB | 5 | 5 | 32 |
| Betacab | CC | 2 | 2 | |
| Betacab | DD | 25 | 25 |
Looking at the table above, you can see the Sum(UnitSales) expression returns the same figures as the Unit Sales dimension. This is due to the expression being aggregated by the three dimensions columns in the table (Customer, Product and UnitSales).
In comparison, the Aggr(Sum(UnitSales),Customer) expression shows the Sum of all Unit Sales for the Customer dimension and the values are no longer aggregated by the Product and Unit Sales dimensions.
This is due to the AGGR expression specifically stating which dimensions the expression should be analysed against. As you can see with the table above, AGGR expressions in a table are attributed to the first related row in the dataset.
The syntax for different expressions using the AGGR function are:
- Default Syntax: Aggr(Expression, Dimension, Dimension)
- Sum of Sales by Customer: Aggr(Sum(Sales), Customer)
- Sum of Sales by Customer with Set Analysis: Aggr(Sum({<Product={‘CC’}>}Sales), Customer)
- Sum of Sales by Customer and Product: Aggr(Sum(Sales), Customer, Product)
- Max Sum of Sales by Customer and Product: Max(Aggr(Sum(Sales), Customer, Product))
Calculated Dimension
The AGGR function can also be used as part of a Calculated Dimension. Normal expressions such as Sum(UnitSales) can’t be used in dimensions because any dimension, in any chart or table, needs to be a set/array of values which will enable you to plot your expression. When you aggregate the expression by a dimension field, such as customer, you are then able to plot the values of your expression against the values of your calculated dimension.
Using the Rank Function with AGGR
Once you start to understand how the AGGR function works you can then start to create more advanced expressions by combining AGGR with other functions, such as Rank. For example, if you wanted to rank the Customers based of Unit Sales, you could use the following expression:
Aggr(Rank(Sum(UnitSales)), Customer)
Alternatively, if you wanted to show the top three Customers based of sales you could use the following expression which uses the Rank function in combination with an IF statement to only show the top three customers.
Concat(Distinct IF(Aggr(Rank(Sum(UnitSales)), Customer) <= 3, Customer))
