Window functions
Window functions are calculated in the same way as aggregations, but they do not merge multiple entries into one. In some cases, this leads to duplication of values among entries in the same group (for example, SUM(... TOTAL)
).
Aggregate functions are calculated from groups of values that are determined by the dimension fields used in a data query: entries with matching dimension values are grouped. Window functions are also calculated over groups of entries called windows. In this case, you should specify grouping parameters in the function call as a list of dimensions to be included (WITHIN ...
) or excluded (AMONG ...
) from the grouping.
Usage Restrictions

Window functions can take as arguments only dimensions or aggregations (or more complex expressions composed of both). At least one of the arguments must be an aggregate expression.
Examples:
 Valid:
RANK(MAX([Profit]) TOTAL)
 Not valid:
MAX(RANK([Profit] TOTAL))
.  Not valid:
RANK([Profit] TOTAL)
, where[Profit]
is not an aggregate expression.
 Valid:

A window function cannot be nested into another window function.
Example:
 Not valid:
RSUM(RANK(SUM([Profit]) WITHIN [Order Date]) TOTAL)
.
 Not valid:

The
AMONG
keyword cannot be used with dimensions that are not included in the data query.Example:
 Not valid:
RANK(SUM([Profit]) AMONG [City])
with dimensions[Order Date]
and[Category]
.
 Not valid:
Syntax
The general syntax for window functions is as follows:
<WINDOW_FUNCTION_NAME>(
arg1, arg2, ...
[ TOTAL
 WITHIN dim1, dim2, ...
 AMONG dim1, dim2, ... ]
)
The values of arg1, arg2, ...
are the function arguments. The arguments are followed by a window grouping, which can be one of three types:
TOTAL
(equivalent toWITHIN
without dimensions): all query entries fall into a single window.WITHIN dim1, dim2, ...
: records are grouped by the dimensionsdim1, dim2, ...
.AMONG dim1, dim2, ...
: records are grouped by all dimensions from the query, except those listed. For example, if we use formulaRSUM(SUM([Sales]) AMONG dim1, dim2)
with dimensionsdim1
,dim2
,dim3
,dim4
in the data query, then the entries will be grouped bydim3
anddim4
, so it will be equivalent toRSUM([Sales] WITHIN dim3, dim4)
.
The grouping clause is optional. TOTAL
is used by default.
Aggregate Functions as Window Functions
The following aggregations can also be used as window functions:
Aggregations  Conditional Aggregations 

SUM  SUM_IF 
COUNT  COUNT_IF 
AVG  AVG_IF 
MAX  
MIN 
To use the window version of the aggregate functions, you must explicitly specify the grouping (unlike other window functions, where it is optional).
Example:
SUM([Sales]) / SUM(SUM([Sales]) TOTAL)
can be used to calculate the ratio of a group's sum of[Sales]
to the sum of[Sales]
among all entries.
AVG
Syntax:AVG( value )
Returns the average of all values. Applicable to numeric data types as well as Date  Datetime
.
AVG_IF
Syntax:AVG_IF( expression, condition )
Returns the average of all values that meet the condition
condition. If the values don't exist, it returns NULL
. Applicable to numeric data types only.
COUNT
Syntax:COUNT( [ value ] )
Returns the number of items in the group.
COUNT_IF
Syntax:COUNT_IF( condition )
Returns the number of items in the group meeting the condition
condition.
MAVG
Syntax:MAVG( value, rows_1 [ , rows_2 ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the moving average of values in a fixedsize window defined by the sort order and arguments:
rows_1 
rows_2 
Window 

positive    The current row and rows_1 preceding rows. 
negative    The current row and rows_1 following rows. 
any sign  any sign  rows_1 preceding rows, the current row and rows_2 following rows. 
Window functions with a similar behavior: MSUM, MCOUNT, MMIN, MMAX.
MAX
Syntax:MAX( value )
Returns the maximum value.
If value
:
 number — Returns the largest number.
 date — Returns the latest date.
 string — Returns the last value in the alphabetic order.
MCOUNT
Syntax:MCOUNT( value, rows_1 [ , rows_2 ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the moving count of (nonNULL
) values in a fixedsize window defined by the sort order and arguments:
rows_1 
rows_2 
Window 

positive    The current row and rows_1 preceding rows. 
negative    The current row and rows_1 following rows. 
any sign  any sign  rows_1 preceding rows, the current row and rows_2 following rows. 
Window functions with a similar behavior: MSUM, MMIN, MMAX, MAVG.
MIN
Syntax:MIN( value )
Returns the minimum value.
If value
:
 number — Returns the smallest number.
 date — Returns the earliest date.
 string — Returns the first value in the alphabetic order.
MMAX
Syntax:MMAX( value, rows_1 [ , rows_2 ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the moving maximum of values in a fixedsize window defined by the sort order and arguments:
rows_1 
rows_2 
Window 

positive    The current row and rows_1 preceding rows. 
negative    The current row and rows_1 following rows. 
any sign  any sign  rows_1 preceding rows, the current row and rows_2 following rows. 
Window functions with a similar behavior: MSUM, MCOUNT, MMIN, MAVG.
MMIN
Syntax:MMIN( value, rows_1 [ , rows_2 ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the moving minimum of values in a fixedsize window defined by the sort order and arguments:
rows_1 
rows_2 
Window 

positive    The current row and rows_1 preceding rows. 
negative    The current row and rows_1 following rows. 
any sign  any sign  rows_1 preceding rows, the current row and rows_2 following rows. 
Window functions with a similar behavior: MSUM, MCOUNT, MMAX, MAVG.
MSUM
Syntax:MSUM( value, rows_1 [ , rows_2 ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the moving sum of values in a fixedsize window defined by the sort order and arguments:
rows_1 
rows_2 
Window 

positive    The current row and rows_1 preceding rows. 
negative    The current row and rows_1 following rows. 
any sign  any sign  rows_1 preceding rows, the current row and rows_2 following rows. 
Window functions with a similar behavior: MCOUNT, MMIN, MMAX, MAVG.
RANK
Syntax:RANK( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the rank of the current row if ordered by the given argument. Rows corresponding to the same value used for sorting have the same rank. If the first two rows both have rank of 1
, then the next row (if it features a different value) will have rank 3
, so, in effect, it is rank with gaps.
If direction
is "desc"
or omitted, then ranking is done from greatest to least, if "asc"
, then from least to greatest.
See also RANK_DENSE, RANK_UNIQUE, RANK_PERCENTILE.
RANK_DENSE
Syntax:RANK_DENSE( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the rank of the current row if ordered by the given argument. Rows corresponding to the same value used for sorting have the same rank. If the first two rows both have rank of 1
, then the next row (if it features a different value) will have rank 2
, (rank without gaps).
If direction
is "desc"
or omitted, then ranking is done from greatest to least, if "asc"
, then from least to greatest.
See also RANK, RANK_UNIQUE, RANK_PERCENTILE.
RANK_PERCENTILE
Syntax:RANK_PERCENTILE( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the relative rank (from 0
to 1
) of the current row if ordered by the given argument. Calculated as (RANK(...)  1) / (row count)
.
If direction
is "desc"
or omitted, then ranking is done from greatest to least, if "asc"
, then from least to greatest.
See also RANK, RANK_DENSE, RANK_UNIQUE.
RANK_UNIQUE
Syntax:RANK_UNIQUE( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the rank of the current row if ordered by the given argument. Rows corresponding to the same value have different rank values. This means that rank values are sequential and different for all rows, always increasing by 1
for the next row.
If direction
is "desc"
or omitted, then ranking is done from greatest to least, if "asc"
, then from least to greatest.
See also RANK, RANK_DENSE, RANK_PERCENTILE.
RAVG
Syntax:RAVG( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the average of all values in a growing (or shrinking) window defined by the sort order and the value of direction
:
direction 
Window 

"asc" 
Starts from the first row and ends at the current row. 
"desc" 
Starts from the current row and ends at the last row. 
By default "asc"
is used.
Window functions with a similar behavior: RSUM, RCOUNT, RMIN, RMAX.
RCOUNT
Syntax:RCOUNT( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the count of all values in a growing (or shrinking) window defined by the sort order and the value of direction
:
direction 
Window 

"asc" 
Starts from the first row and ends at the current row. 
"desc" 
Starts from the current row and ends at the last row. 
By default "asc"
is used.
Window functions with a similar behavior: RSUM, RMIN, RMAX, RAVG.
RMAX
Syntax:RMAX( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the maximum of all values in a growing (or shrinking) window defined by the sort order and the value of direction
:
direction 
Window 

"asc" 
Starts from the first row and ends at the current row. 
"desc" 
Starts from the current row and ends at the last row. 
By default "asc"
is used.
Window functions with a similar behavior: RSUM, RCOUNT, RMIN, RAVG.
RMIN
Syntax:RMIN( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the minimum of all values in a growing (or shrinking) window defined by the sort order and the value of direction
:
direction 
Window 

"asc" 
Starts from the first row and ends at the current row. 
"desc" 
Starts from the current row and ends at the last row. 
By default "asc"
is used.
Window functions with a similar behavior: RSUM, RCOUNT, RMAX, RAVG.
RSUM
Syntax:RSUM( value [ , direction ] [ TOTAL  WITHIN [ dim1, ... ]  AMONG [ dim1, ... ] ] )
Returns the sum of all values in a growing (or shrinking) window defined by the sort order and the value of direction
:
direction 
Window 

"asc" 
Starts from the first row and ends at the current row. 
"desc" 
Starts from the current row and ends at the last row. 
By default "asc"
is used.
Window functions with a similar behavior: RCOUNT, RMIN, RMAX, RAVG.
SUM
Syntax:SUM( value )
Returns the sum of all expression values. Applicable to numeric data types only.
SUM_IF
Syntax:SUM_IF( expression, condition )
Returns the sum of all the expression values that meet the condition
condition. Applicable to numeric data types only.