Window Calculations Overview

• Oct 25, 2021
• 2 minutes to read

Window calculations use window functions to calculate data across a set of window rows related to the current row. Partitioning criteria are applied to rows to arrange them in windows.

Note

Window calculations are available for the Optimized calculation engine. Use the PivotGridOptionsData.DataProcessingEngine property to enable the Optimized engine.

The following calculation types are supported:

Running Total

Allows you to calculate a cumulative total for a set of measure values.

Expression

RunningSum(Sum([Sales]))

Field Data Binding

Binding	Partitioning	Parameters
RunningTotalBindingBase	ColumnValue	SummaryType = Sum
RunningTotalBinding	ColumnValue	SummaryType = Sum

Moving Calculation

Allows you to apply a moving calculation, which uses neighboring values to calculate a total. Note that neighboring values are specified using offsets from the currently processed value.

Expression

WindowSum(Sum([Sales]), -1, 1)

Field Data Binding

Binding	Partitioning	Parameters
MovingCalculationBindingBase	ColumnValue	SummaryType = Sum, prevCount = 1, nextCount = 1
MovingCalculationBinding	ColumnValue	SummaryType = Sum, previousValuesCount = 1, nextValuesCount = 1

Difference

Allows you to compute differences between measure values.

Expression

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

Field Data Binding

Binding	Partitioning	Parameters
DifferenceBindingBase	ColumnValue	Target = Previous , DifferenceType = Absolute
DifferenceBinding	ColumnValue	Target = Previous, DifferenceType = Absolute

Percent of Total

Allows you to calculate a contribution of individual measure values to a total.

Expression

Sum([Sales]) / Total(Sum([Sales]))

Field Data Binding

Binding	Partitioning	Parameters
PercentOfTotalBindingBase	ColumnValue
PercentOfTotalBinding	ColumnValue

Rank

Allows you to rank values of the specified measure.

Expression

RankCompetition(Sum([Sales]), 'asc')

Field Data Binding

Binding	Partitioning	Parameters
RankBindingBase	ColumnValue	RankType = Competition, Order = Ascending
RankBinding	ColumnValue	RankType = Competition, Order = Ascending