Intermediate Level Aggregations
- 8 minutes to read
The Dashboard can aggregate and summarize data on different levels.
- The Query Builder allows you to prepare an underlying data source before analyzing data. You can apply grouping, sorting, summarization and other data shaping operations during data selection.
- Dashboard items aggregate and summarize data at a visualization level using dimensions and measures, respectively. For more information, see Binding Dashboard Items to Data in the Designer.
- The
aggrfunction allows you to introduce an intermediate detail level that is not related to the visualization level. This allows you to create custom aggregations at different levels and combine these aggregations with existing visualizations.
Overview
The aggr function aggregates and summarizes underlying data using the detail level specified by a predefined set of dimensions and a specified summary function. This function can be used during the creation of a new calculated field in the Expression Editor.
The aggr function has the following syntax.
aggr(summaryExpression, dimension1, dimension2, ...)
The first argument is a summary expression calculated against a specific data source field. The next arguments are the set of dimensions whose values are aggregated and used to calculate summaries specified using the first argument.
For instance, the following function calculates sums of sales for each product within the specified category.
aggr(Sum([Sales]), [Category], [Product])
If you create the calculated field that includes the aggr function and drop the created field into an existing dashboard item, the Dashboard joins the resulting aggregation with the already displayed data. This means that you can add data with the increased or decreased granularity to the dashboard item.
There are two main scenarios.
Less Detailed Granularity
In the first scenario, an aggregation has a less detailed granularity than visualized data. In this scenario, an underlying data source contains the list of orders for two categories and corresponding products.
To aggregate this data by individual categories, create a calculated field with the following expression.
aggr(Sum([Sales]), [Category])
The following internal table is generated for this calculated field:
The sample Grid dashboard item contains more detailed data and includes the following columns: Category, Product, and the sum of Sales.
If you drop the created calculated field to the Grid, the sum of sales for each category is repeated for each Grid row.
For instance, you can use these values later to calculate a contribution of each product to a category’s sales.
More Detailed Granularity
In the second scenario, an aggregation has a more detailed granularity than visualized data. To aggregate this data by categories and products, create a calculated field with the following expression.
aggr(Sum([Sales]), [Category], [Product])
The following internal table is generated for this calculated field.
Drop the created calculated field to the Grid and set its summary type to Min. The Grid displays minimum product sales within each category.
Function Extension
You can use the joinRule function that is an extension for the aggr function. It specifies the dimension for the aggrfunction and a column whose values are used in the join operation after aggregation is completed. ThejoinRulefunction can be used only as an aggr function argument. The following example shows how to display values for the previous year:
aggr(Sum([ExtendedPrice]), joinRule(GetYear([Date]), GetYear([Date]) - 1))
Example 1 - Best/Worst Sales by Year
The following example shows how to display best and worst monthly sales for each year.
In this example, the Chart dashboard item shows the sum of sales by different years. The Sales field is placed in the Values section and the OrderDate (with the Year group interval) is placed in the Arguments section.
To display sales by the best/worst months for each year, create a new calculated field with the following expression.
aggr(Sum([Sales]), GetYear([OrderDate]), GetMonth([OrderDate]))
Drop this field (Sales by Year/Month in the image below) to the Values section and set its summary type to Max. Then, drop this field to Values again and set its summary type to Min. The Chart visualizes sales by the best/worst months in a year.
Tip
Download the Example: How to display best and worst monthly sales for each year
Example 2 - Percent of Total
This example shows how to calculate a contribution of individual quarter sales to year sales.
In this example, the Pivot dashboard item displays the sum of sales by year/quarter. The Sales field is placed in the Values section and the hierarchy of OrderDate fields (with the Year and Quarter group intervals) is placed in Rows.
To calculate a contribution of each quarter to a year sales, do the following.
Calculate totals for each year. Create a calculated field that uses the following
aggrfunction:aggr(Sum([Sales]), GetYear([OrderDate]))Set the name of the created field to Sales by Year.
Calculate a contribution of each quarter to year sales. Create a one more calculated field:
Sum([Sales]) / Max([Sales by Year])Name this field Percent of Total and drop it to Values to see the result.
Tip
Download the Example: How to calculate a contribution of individual quarter sales to a year sales
Example 3 - Customer Acquisition
In this example, a customer acquisition is evaluated by grouping customers by the quarter/year of their first purchase to compare sales contributions.
The Chart dashboard item below visualizes sales by quarter/year.
The following expression determines the minimum order date (the first purchase date) per customer.
aggr(Min(GetDateQuarterYear([OrderDate])), [CustomerID])
Set the name of the created field to Customer First Order and drop this field to the Series section to see the result.
Tip
Download the Example: How to evaluate a customer acquisition using the quarter/year of their first purchase
Example 4 - Customer Order Count
The example shows how to divide customers count by the number of orders they made.
The Chart below shows the number of orders that is made by each customer.
The calculated field below evaluates the number of unique orders made by each customer.
aggr(CountDistinct([OrderID]), [CustomerID])
Set the name of this field to Customer Order Count and drop this field to arguments. Then, drop the CustomerID field to Values and change its summary type to Count Distinct.
The Chart shows the number of customers that made a specific number of orders.
Tip
Download the Example: How to divide customers count by the number of orders they made
Example 5 - Best Product Sales by Year
This scenario requires the use of nested aggregations. In this example, the dashboard shows products with the best sales in a year along with sales values.
The initial Grid dashboard item shows sales of all products by year (the OrderDate column with the Year group interval and the Sales column). The data source also contains the ProductName field.
To implement this scenario, do the following steps.
Create the calculated field that returns product sales for individual years.
aggr(Sum([Sales]), GetYear([OrderDate]), [ProductName])Set its name to Product Sales by Year.
Create the calculated field that returns maximum sales values.
aggr(Max([Product Sales by Year]), GetYear([OrderDate]))Set its name to Max Product Sales by Year.
Finally, create a calculated field returning the name of the product with the best sales and a corresponding sales value.
Iif([Max Product Sales by Year] = [Product Sales by Year], [ProductName] + ' ($ ' + [Product Sales by Year] + ')', null)Specify the name as Best Sales Product. Then, drop this field to the Columns section to see the result.
Tip
Download the Example: How to show products with the best sales in a year along with sales values
Example 6 - Year-on-Year Comparison
The two examples in this section compare sales during a time period to sales during the corresponding period the previous year.
The Previous Year Sales Grid displays sales by years in comparison with the previous year’s sales. The PrevYearSales field is calculated by the following expression:
aggr(Sum([Sales]), joinRule(GetYear([OrderDate]),GetYear([OrderDate])-1))
The joinRule function here is used to shift the calculated sales back a year. Add the created field to the Grid’s Columns section to see the result:
The second Grid, Product Sales by Quarters, displays quarterly sales numbers in comparison with corresponding quarterly numbers from the previous year.
The PrevQuarterYearSales calculated field aggregates sales by products and the previous year’s quarter. The joinRule function is used to calculate the second dimension.
aggr(Sum([Sales]),[ProductName],joinRule(GetDateQuarterYear([OrderDate]),GetDateQuarterYear(AddYears([OrderDate], -1))))
Add the created field to the Grid’s Columns section to see the result. A format rule is applied to highlight sales that declined compared to the previous year:
Tip
Download the Example: How to display sales by years in comparison with the previous year’s sales