What's New
Search Results for

    Show / Hide Table of Contents

    Calculation Functions Reference

    This topic contains the descriptions of window functions that can be used to specify measure expressions.

    Function Description Example Image
    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
    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
    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
    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 that the Total function calculates the total based on values from the underlying data source. Total(Sum([Sales])) CalculationFunction_Total
    Important

    Note that window functions cannot be used inside Aggr.

    In This Article
    Back to top 2025 - ReportsNow Inc Download as PDF