# Windows Calculation Functions

This article contains a description of Windows functions that can be used to specify measure expressions.

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

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()`

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]))`

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([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]))`

RunningMin(SummaryExpression)-
Returns the running minimum of the specified expression from the first row in the window to the current row.

`RunningMin(Sum([Sales]))`

RunningSum(SummaryExpression)-
Returns the running sum of the specified expression from the first row in the window to the current row.

`RunningSum(Sum([Sales]))`

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

WindowCount(SummaryExpression, StartOffset, EndOffset)-
Returns the count of the expression within the window.

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

WindowCountDistinct(SummaryExpression, StartOffset, EndOffset)-
Returns the distinct count of the expression within the window.

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

WindowFirstValue(SummaryExpression, StartOffset, EndOffset)-
Returns the first value of the expression within the window.

`WindowFirstValue((Sum([Sales]), First(), Last())`

WindowMax(SummaryExpression, StartOffset, EndOffset)-
Returns the maximum of the expression within the window.

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

WindowMin(SummaryExpression, StartOffset, EndOffset)-
Returns the minimum of the expression within the window.

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

WindowMedian(SummaryExpression, StartOffset, EndOffset)-
Returns the median of the expression within the window.

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

WindowSum(SummaryExpression, StartOffset, EndOffset)-
Returns the sum of the expression within the window.

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

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 that the

**Total**function calculates the total based on values from the underlying data source.`Total(Sum([Sales]))`

## Comments

0 comments

Please sign in to leave a comment.