Window Calculation Functions

  • 3 minutes to read

This topic contains a description of window functions that can be used to specify measure expressions.

Function

Description

Example

Index()

Returns the index of the current row in the window.

Index()

CalculationFunction_Index

Size()

Returns the number of rows in the window.

Size()

CalculationFunction_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([Sales]), 3)

CalculationFunction_Lookup

Last()

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

Last()

CalculationFunction_Last

First()

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

First()

CalculationFunction_First

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

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

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

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

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

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

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

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

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

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

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

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

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

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

RankPercentile(Sum([Sales]), ‘desc’)

RunningAvg(SummaryExpression)

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

RunningAvg(Sum([Sales]))

CalculationFunction_RunningAvg

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’)

RunningAggregateFunction

RunningCount(SummaryExpression)

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

RunningCount(Sum([Sales]))

RunningMax(SummaryExpression)

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

RunningMax(Sum([Sales]))

CalculationFunction_RunningMax

RunningMin(SummaryExpression)

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

RunningMin(Sum([Sales]))

CalculationFunction_RunningMin

RunningSum(SummaryExpression)

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

RunningSum(Sum([Sales]))

CalculationFunction_RunningSum

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’)

windowAggregateFunction

WindowAvg(SummaryExpression, StartOffset, EndOffset)

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

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

CalculationFunction_WindowAvg

WindowCount(SummaryExpression, StartOffset, EndOffset)

Returns the count of the expression within the window.

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

CalculationFunction_WindowCount_plus2

WindowCountDistinct(SummaryExpression, StartOffset, EndOffset)

Returns the distinct count of the expression within the window.

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

WindowMax(SummaryExpression, StartOffset, EndOffset)

Returns the maximum of the expression within the window.

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

CalculationFunction_WindowMax

WindowMin(SummaryExpression, StartOffset, EndOffset)

Returns the minimum of the expression within the window.

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

CalculationFunction_WindowMin

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([Sales]), First(), Last())

WindowMedian(SummaryExpression, StartOffset, EndOffset)

Returns the median of the expression within the window.

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

CalculationFunction_WindowMedian

WindowSum(SummaryExpression, StartOffset, EndOffset)

Returns the sum of the expression within the window.

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

CalculationFunction_WindowSum-plus2

WindowVar(SummaryExpression, StartOffset, EndOffset)

Returns the variance of the expression within the window.

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

WindowVarp(SummaryExpression, StartOffset, EndOffset)

Returns the biased variance of the expression within the window.

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

WindowStdDev(SummaryExpression, StartOffset, EndOffset)

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

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

WindowStdDevp(SummaryExpression, StartOffset, EndOffset)

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

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

Total(SummaryExpression)

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

Note

Note that the Total function calculates the total based on values from the underlying data source.

Total(Sum([Sales]))

CalculationFunction_Total