Usage examples and use cases for the aggr() function

Example 1 - Highest and lowest monthly sales for each year

This example will show you how to display for each year the highest and lowest monthly sales. To start, you have a graph that displays the total sales amount by year.

The field "Sales amount" is positioned in "Value" and the date of the sale (with the year level) is positioned in argument.

To display the sales of the best month and the worst month by year, you have to create a field calculated with the following expression:

Aggr(Sum([Sales Amount]),GetYear([Sales Date]),GetMonth([Sales Date]))

Drag this field into the value area and select the Max aggregation function, to get the best month's sales per year. Drag this field into the value area again, and select the Min aggregation function to get the worst month's sales per year.

Example 2 - Percentage of Total

This example illustrates how to calculate the contribution of each quarter's sales to annual sales.

To start you have a grid that displays the amount of sales by quarter. The [Sales Amount] field is dragged into the column as well as the sales date field (at the year level and at the quarter level).

To calculate each quarter's contribution to annual sales, you must:

• Calculate the total sales per year using the aggr() function in a calculated field renamed: 'Sales per year'

aggr(Sum([Sales Amount]),GetYear([Sales Date]))

• Calculate the contribution of each quarter with the following expression:

Sum([Sales Amount])/Max([Sales per year])

I add my new calculated field renamed 'Percent of total' in the grid:

Example 3 – Analysis of the turnover of products

In this example, the idea is to display for each quarter, the sales of products acquired during that quarter. The sales of the products will be evaluated by grouping the products by quarter/year of their first sales, which will allow to compare the contribution of each one.

The following graph shows the sales by quarter.

The following expression will allow to determine the date of first sale of each product, and to express it in quarter. 'Quarter first sale':

aggr(Min(GetDateQuarterYear([Sales Date])),[Product ID])

Drag this calculated field to Series to see the distribution by quarter of acquisition.

With this calculated object, you can also analyze the number of new products per quarter of acquisition as in the following graph:

Example 4 - Number of medals per event for country

In this example you will see how to break down the number of athletes by the number of medals. The graph below counts the number of medals per athlete.

The calculated field below allows you to evaluate the separate number of medals per athlete. By using the aggr function, you can use this calculated field as an argument in the chart.

aggr(CountDistinct([Medal]),[ID])

Drag the [ID] field as a value, and select the number aggregation function. Drag the [Number of medals per athlete] field as an argument.

This graph shows the number of athletes sorted by their amount of medals.

Example 5 - Sales of the best category of the year

In this example, we want to display in a grid, for each year, the product that brought in the most money, with its sales figure next to it. To do this you will need to use nested aggregations.

The initial grid shows the annual sales.

To display the best category of each year, follow these steps:

• Create the calculated field 'Annual sales category' that returns the annual sales of each category.

aggr(Sum([Sales Amount]),GetYear([Sales Date]),[Category])

• Create the calculated field 'MAX Annual sales category' that returns the maximum annual sales per category.

aggr(Max([Annual sales category]),GetYear([Sales Date]))

• Create the calculated field 'Best category' that returns the best category with the associated sales amount.

MAX(iif([Annual sales category]=[MAX Annual sales category], [Category]+ ', ' +[Annual sales category]+' dollars',?))

• Drag this field into a column.