dashboard-115870-common-features-advanced-analytics-aggregations-intermediate-level-aggregations.md
The dashboard controls can aggregate and summarize data on different levels.
aggr function that specifies how to summarize data.To set up an intermediate level aggregation, create a new calculated field and use the aggr function. This function has the following syntax:
aggr(summaryExpression, dimension1, dimension2, ...)
The first argument is a summary expression calculated against a specific data source field. The subsequent arguments are the dimensions that define the calculation level.
For instance, the following function calculates yearly product sales:
aggr(Sum([Sales]), [Product], GetYear([Date]))
The function with non-specified dimension arguments combines all data into one group. For instance, the following function calculates grand total sales:
aggr(Sum([Sales]))
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.
Consider the following data source that contains customer orders:
To aggregate this data by individual categories, create the SalesByCategory calculated field with the following expression:
aggr(Sum([Sales]), [Category])
The internal table below is generated for this calculated field:
You can add the following columns to a Grid dashboard item: Category, Product, and the sum of Sales.
If you drop the calculated field onto this Grid, the sum of sales for each category is repeated in each Grid row. In other words, you added data with less detailed granularity than specified by the item’s dimensions.
For instance, you can use these values later to calculate each product’s contribution to the sales category.
This section assumes you operate with the same data. You can create a SalesByProduct calculated field that computes product sales:
aggr(Sum([Sales]), [Product])
The following internal table is generated for this calculated field.
A Grid item displays sales by category. You can drop the created calculated field onto the Grid and set its summary type to Min. The Grid displays minimum product sales for each category. In this instance, you added aggregation of more detailed granularity to the dashboard item.
You can use the joinRule function to shift the calculation level. For example, you can have a dashboard item calculate yearly values, and use the following function to add summaries for preceding years:
aggr(Sum([ExtendedPrice]), joinRule(GetYear([Date]), GetYear([Date]) - 1))
Note
The joinRule function is available only for client data processing mode.
All of the examples below are created in the WinForms Designer, but you can also create calculated fields with the same expressions in the Web Dashboard.
The following example shows how to display the highest and lowest monthly sales for each year.
In this example, the Chart dashboard item shows yearly sales numbers.
To display the highest/lowest monthly sales 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. Drop the same field to Values again, but set its summary type to Min. The resulting Chart will display the highest and lowest monthly sales for each year.
This example shows how to calculate the contribution of quarterly sales to total yearly 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.
Do the following to display quarterly sales as a percentage of total sales.
Calculate grand total sales. Create a calculated field that uses the following aggr function:
Calculate the contribution of quarterly sales to total yearly sales:
The Chart dashboard item below displays quarterly sales.
The following expression determines the quarter when customers made their first purchase.
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. Each bar now displays a breakdown by customers, grouped by their first order date.
The example shows how to group customers by the number of orders placed.
The Chart below shows the number of orders placed by each customer.
The calculated field below evaluates the number of unique orders placed 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 now groups customers by the number of orders placed.
This scenario requires the use of nested aggregations. In this example, the dashboard shows products with the highest 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.
Follow the steps below:
Create the calculated field that returns product sales for individual years.
Create the calculated field that returns maximum sales values.
Finally, create a calculated field that returns the name of the product with the highest sales and a corresponding sales value.
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 year 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 in comparison to the previous year:
The example shows how to filter item source data by the aggregated ProductSales field and how to display total product sales that are greater than $20k.
The Grid item below displays product sales:
The following SalesByProduct calculated field determines the total product sales:
aggr(Sum([ProductSales]), [ProductName])
Add the created field to the Grid’s hidden dimension section and set the expression [SalesByProduct] > 20000.0m in a dashboard item filter to see the total product sales that are greater than $20k.
The example shows how to filter item source data by the aggregated ProductSales field and how to display totals only for those products whose sales are greater than the average in the corresponding category.
The TotalProductSales calculated field uses the following function to obtain the total sales by product:
aggr(Sum([ProductSales]), [ProductName])
The following function determines average product sales per category (the AverageProductSales calculated field):
aggr(Avg([TotalProductSales]), [CategoryName])
Add the created fields to the Grid’s hidden dimension section and set the expression [TotalProductSales] > [AverageProductSales] in a dashboard item filter to see totals only for those products whose sales are greater than the average in the corresponding category.
See Also