Display Functions

Display functions operate on the result set of a query.

Function Example Description

BottomN

BottomN(Sales, 10)

Returns the n lowest values of expression, ranked from lowest to highest.

Filter

Filter(Sales USING Product = 'widgit')

Computes the expression using the given preaggregate filter.

Mavg

Mavg(Sales, 10)

Calculates a moving average (mean) for the last n rows of data in the result set, inclusive of the current row.

Msum

SELECT Month, Revenue, Msum(Revenue, 3) as 3_MO_SUM FROM Sales

Calculates a moving sum for the last n rows of data, inclusive of the current row.

The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data, and so on. When the nth row is reached, the sum is calculated based on the last n rows of data.

NTile

Ntile(Sales, 100)

Determines the rank of a value in terms of a user-specified range. It returns integers to represent any range of ranks. The example shows a range from 1 to 100, with the lowest sale = 1 and the highest sale = 100.

Percentile

Percentile(Sales)

Calculates a percent rank for each value satisfying the numeric expression argument. The percentile rank ranges are from 0 (1st percentile) to 1 (100th percentile), inclusive.

Rank

Rank(Sales)

Calculates the rank for each value satisfying the numeric expression argument. The highest number is assigned a rank of 1, and each successive rank is assigned the next consecutive integer (2, 3, 4,...). If certain values are equal, they are assigned the same rank (for example, 1, 1, 1, 4, 5, 5, 7...).

Rcount

SELECT month, profit, Rcount(profit) FROM sales WHERE profit > 200

Takes a set of records as input and counts the number of records encountered so far.

Rmax

SELECT month, profit, Rmax(profit) FROM sales

Takes a set of records as input and shows the maximum value based on records encountered so far. The specified data type must be one that can be ordered.

Rmin

SELECT month, profit, Rmin(profit) FROM sales

Takes a set of records as input and shows the minimum value based on records encountered so far. The specified data type must be one that can be ordered.

Rsum

SELECT month, revenue, Rsum(revenue) as RUNNING_SUM FROM sales

Calculates a running sum based on records encountered so far.

The sum for the first row is equal to the numeric expression for the first row. The sum for the second row is calculated by taking the sum of the first two rows of data, and so on.

TopN

TopN(Sales, 10)

Returns the n highest values of expression, ranked from highest to lowest.