Aggregate Functions

In this article we will show how to use and change aggregate functions in RadPivotGrid and RadPivotFieldList.

Different aggregate functions are available only for LocalDataSourceProvider. When your data source is OLAP Cube, the data is already aggregated in it and you cannot apply new aggregate function.

Items Source

In our application we will use a very simple ItemsSource for our LocalDataSourceProvider and we will change only the aggregated functions just to show the different result in RadPivotGrid.

Name Promotion Price
Pen Extra Discount 10.61
Pen Extra Discount 10.82
Pen Extra Discount 11.24
Pen Free shipping 10.47
Pen Free shipping 11.14
Pen Free shipping 10.89
Pencil Extra Discount 8.82
Pencil Extra Discount 8.95
Pencil Extra Discount 7.12
Pencil Free shipping 8.63
Pencil Free shipping 7.28
Pencil Free shipping 7.59
Notebook Extra Discount 22.86
Notebook Extra Discount 23.02
Notebook Extra Discount 23.22
Notebook Free shipping 21.99
Notebook Free shipping 22.45
Notebook Free shipping 22.56

We will use Name as RowGroupDescription, Promotion as ColumnGroupDescription and Price as AggregateDescription.

Aggregate Functions

RadPivotGrid is designed to aggregate data. Aggregated data is shown in the cells. You can set different aggregate functions to change the view of your RadPivotGrid. The chosen function is applied on the data in the cells and on the GrandTotals as well:

Rad Pivot Grid Features Aggregate Functions 01

On the image you can see RadPivotGrid with Price defined as PropertyAggregateDescription with Average function. Note how the data is grouped together and an aggregate function is applied:

  • Data is aggregated in the cells - check the red rectangle - it shows 23.03 which is the average of the values for all Notebooks with Extra Discount promotions.

  • GrandTotals use the same aggregate function - as you can see in the Green and Blue rectangles, GrandTotals for Rows and Columns are using the same aggregate function - average. So in the Blue rectange you can see that the average price of all products with Free shipping promotion is 13.67. In the Green rectangle you can check easily that the average price of all Pens is 10.86.

  • The average of GrandTotals for columns is the same as average of GrandTotals for rows. The value in the bottom right corner is calculated with the aggregate function applied on the GrandTotals. There shouldn't be any difference whether you apply the aggregate function on the GrandTotals of the rows or of the columns.

Here is a full list with the functions you can use:

  • Average - this function will sum all data and then divide it to the number of items. For example we have three products "Pen" with "Extra Discount" promotion. In RadPivotGrid you can see the average price. The calculation is simple - sum the prices of the three products (10.61 + 10.82 + 11.24 = 32.67). Now divide it to the count of items (32.67 / 3 = 10.89).

  • Count - this function will count the items - in our example there are three products "Pen" with "Extra Discount" promotion, so in the matching cell you will see number 3:

  • Max - this function will show the highest value of all possible for the current cell - for product "Pen" with "Extra Discount" promotion we have prices 10.61, 10.82, 11.24. The highest value is 11.24 so it will be shown in the cell.

  • Min - this function will show the lowest value of all possible for the current cell - for product "Pen" with "Extra Discount" promotion we have prices 10.61, 10.82, 11.24. The lowest value is 10.61 so it will be shown in the cell.

  • Product - this function is doing multiplication of the values - for product "Pen" with "Extra Discount" promotion we have prices 10.61, 10.82, 11.24. So the Product function will be: 10.61 * 10.82 * 11.24 = 1290.35

  • StdDev - this function is calculating the standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value. The formula is:

Rad Pivot Grid Features Aggregate Functions 02

So for product "Pen" in promotion "Extra Discount" the standard deviation is:

Rad Pivot Grid Features Aggregate Functions 04

Rad Pivot Grid Features Aggregate Functions 03

  • StdDevP - this function is calculating the standard deviation based on the entire population given as arguments. The standard deviation is a measure of how widely values are dispersed from the average value. The formula is:

Rad Pivot Grid Features Aggregate Functions 05

So for product "Pen" in promotion "Extra Discount" the StdDevP is:

Rad Pivot Grid Features Aggregate Functions 04

Rad Pivot Grid Features Aggregate Functions 06

  • Sum - this function will sumarize all values. For product "Pen" in promotion "Extra Discount" the value in the cell will be 10.61 + 10.82 + 11.24 = 32.67.

  • Var - estimates variance based on a sample. The formula is:

Rad Pivot Grid Features Aggregate Functions 07

So for product "Pen" in promotion "Extra Discount" the variance is:

Rad Pivot Grid Features Aggregate Functions 04

Rad Pivot Grid Features Aggregate Functions 08

  • VarP - calculates variance based on the entire population. The formula is:

Rad Pivot Grid Features Aggregate Functions 09

So for product "Pen" in promotion "Extra Discount" the variance is:

Rad Pivot Grid Features Aggregate Functions 04

Rad Pivot Grid Features Aggregate Functions 10

Defining Aggregate Function

You can define Aggregate function in the XAML, in code behind or even at runtime by using RadPivotFieldList.

Aggregate Function is defined on PropertyAggregateDescription. There is property AggregateFunction which should be set in one of the following ways:

<pivot:PropertyAggregateDescription PropertyName="Price" AggregateFunction="StdDevP" /> 

PropertyAggregateDescription aggregate = new PropertyAggregateDescription(); 
aggregate.PropertyName = "Price"; 
aggregate.AggregateFunction = AggregateFunctions.StdDevP; 
Dim aggregate As New PropertyAggregateDescription() 
aggregate.PropertyName = "Price" 
aggregate.AggregateFunction = AggregateFunctions.StdDevP 

You can change applied Aggregate Function at runtime by using RadPivotFieldList. Just find the aggregate description which you want to modify in the "Values" list and click on it - some of the aggregate functions are listed there, but if you don't see the one you need - click on "More aggregate Options..." item and a new dialog RadWindow will open. In it you will find all predefined Aggregate Functions.

Rad Pivot Grid Features Aggregate Functions 11

Rad Pivot Grid Features Aggregate Functions 12

See Also

In this article