Aggr Function

• Nov 07, 2019
• 2 minutes to read

The Optimized calculation engine introduces a new aggregate function not available in Legacy and LegacyOptimzed modes - the Aggr function.

The Aggr function uses the summary function to calculate values for data obtained from the first specified data source column, group data by the next specified columns, and calculate group summaries. Aggr functions can be nested, and you can summarize aggregated data in a single expression.

The Aggr function has the following syntax:

Aggr(summaryExpression, column1, column2, ...) 

The first argument is a summary expression calculated against a data source column, followed by a set of data columns whose values are grouped to calculate summaries for the first argument.

Note

The Aggr function is available for the Optimized calculation engine. Use the PivotGridOptionsData.DataProcessingEngine property to enable the Optimized engine.

For example, the data below is Northwind orders for several years:

To find countries with sales by year that exceed 30000, create the SalesByYearByCountry field and calculate its value with the following expression:

Aggr(Sum([ExtendedPrice]), GetYear([OrderDate]), [Country]) 

Place the SalesByYearByCountry field in the Pivot Control’s Filter Header area. Click the field and apply a filter.

The following picture illustrates the resulting table:

You can find this example and other examples in our Demo Center:

Examples for WinForms Pivot Grid

• Use Aggr Function in Pivot Grid Field Expressions

Examples for ASP.NET Pivot Grid

• How to Calculate Totals for the Minimum Values

Examples for Dashboards

• Best/Worst Sales by Year
• Percent of Total
• Customer Acquisition
• Customer Order Count
• Best Product Sales by Year