Window Calculation Functions

Jul 12, 2023
4 minutes to read

You can use the WindowExpression.Expression property to compose an expression that uses window calculation functions, as illustrated in the following code snippet:

csharp

pivotGridControl.Fields["fWindowExpr"].DataBinding = new WindowExpressionBinding() {
    Expression = "RunningMax(Sum([ProductAmount]))",
    PartitioningCriteria = CalculationPartitioningCriteria.ColumnValueAndRowParentValue
};

The resulting grid looks as shown below:

Documentation:

The following table lists functions you can use to compose an expression for the WindowExpressionBinding data binding instance’s Expression property:

Function

Description

Example

| | --- | --- | --- | |

Last()

Returns the number of rows from the current row to the last row in the window.

Last()

| |

First()

Returns the number of rows from the current row to the first row in the window.

First()

| |

Index()

Returns the index of the current row in the window.

Index()

| |

Size()

Returns the number of rows in the window.

Size()

| |

Lookup(SummaryExpression, Position)

Returns the value of the expression in a target position specified as a relative offset from the current position.

Lookup(Sum([Amount]), -1)

| |

RankCompetition(SummaryExpression, [‘asc’ | ‘desc’])

Returns the standard competition rank for the current row in the window.

RankCompetition(Sum([Amount]), ‘asc’)

| |

RankDense(SummaryExpression, [‘asc’ | ‘desc’])

Returns the dense rank for the current row in the window.

RankDense(Sum([Amount]), ‘asc’)

| |

RankUnique(SummaryExpression, [‘asc’ | ‘desc’])

Returns the unique rank for the current row in the window.

RankUnique(Sum([Amount]), ‘asc’)

| |

RankModified(SummaryExpression, [‘asc’ | ‘desc’])

Returns the modified competition rank for the current row in the window.

RankModified(Sum([Amount]), ‘asc’)

| |

RankPercentile(SummaryExpression, [‘asc’ | ‘desc’])

Returns the percentile rank for the current row in the window.

RankPercentile(Sum([Amount]), ‘asc’)

| |

RunningAvg(SummaryExpression)

Returns the running average of the specified expression from the first row in the window to the current row.

RunningAvg(Sum([Amount]))

| |

RunningAggregate(SummaryExpression, CustomAggregateName)

Returns the cumulative result of the custom function applied to specified expression from the first row in the window to the current row.

RunningAggregate(Min([City]), ‘StringConcat’)

| |

RunningCount(SummaryExpression)

Returns the running count of the specified expression from the first row in the window to the current row.

RunningCount(Sum([Amount]))

| |

RunningMax(SummaryExpression)

Returns the running maximum of the specified expression from the first row in the window to the current row.

RunningMax(Sum([Amount]))

| |

RunningMin(SummaryExpression)

Returns the running minimum of the specified expression from the first row in the window to the current row.

RunningMin(Sum([Amount]))

| |

RunningSum(SummaryExpression)

Returns the running sum of the specified expression from the first row in the window to the current row.

RunningSum(Sum([Amount]))

| |

WindowAggregate(SummaryExpression, StartOffset, EndOffset, CustomAggregateName)

Returns the result of the custom function applied to the specified expression in the window.

WindowAggregate(Min([CompanyName]), -1, 1, ‘StringConcat’)

| |

WindowAvg(SummaryExpression, StartOffset, EndOffset)

Returns the average of the expression within the window, which is defined using offsets from the current row.

WindowAvg(Sum([Amount]), First(), Last())

| |

WindowCount(SummaryExpression, StartOffset, EndOffset)

Returns the count of the expression within the window.

WindowCount(Sum([Amount]), First()+2, Last())

| |

WindowCountDistinct(SummaryExpression, StartOffset, EndOffset)

Returns the distinct count of the expression within the window.

WindowCountDistinct(Sum([Amount]), First(), Last())

| |

WindowMax(SummaryExpression, StartOffset, EndOffset)

Returns the maximum of the expression within the window.

WindowMax(Sum([Amount]), First(), Last())

| |

WindowMin(SummaryExpression, StartOffset, EndOffset)

Returns the minimum of the expression within the window.

WindowMin(Sum([Amount]), First(), Last())

| |

WindowMode(SummaryExpression, StartOffset, EndOffset)

Returns the statistical mode in the window (the element that occurs most often in the values calculated with the specified expression within the window).

WindowMode(Sum([Amount]), First(), Last())

| |

WindowMedian(SummaryExpression, StartOffset, EndOffset)

Returns the median of the expression within the window.

WindowMedian(Sum([Amount]), First(), Last())

| |

WindowSum(SummaryExpression, StartOffset, EndOffset)

Returns the sum of the expression within the window.

WindowSum(Sum([Amount]), First()+2, Last())

| |

WindowVar(SummaryExpression, StartOffset, EndOffset)

Returns the variance of the expression within the window.

WindowVar(Sum([Amount]), First(), Last())

| |

WindowVarp(SummaryExpression, StartOffset, EndOffset)

Returns the biased variance of the expression within the window.

WindowVarp(Sum([Amount]), First(), Last())

| |

WindowStdDev(SummaryExpression, StartOffset, EndOffset)

Returns the sample standard deviation of the expression within the window.

WindowStdDev(Sum([Amount]), First(), Last())

| |

WindowStdDevp(SummaryExpression, StartOffset, EndOffset)

Returns the biased standard deviation of the expression within the window.

WindowStdDevp(Sum([Amount]), First(), Last())

| |

Total(SummaryExpression)

Returns the total for the specified expression in a calculation window.

Total(Sum([Amount]))