|
Function
Syntax
|
Purpose
|
Example
|
|
TOTAL(expression)
|
Returns the total for the given expression in the
current partition.
|
TOTAL(SUM([Sales])) returns the total for the sum of sales based on the
current scope and direction.
|
|
LOOKUP(expression,
[offset])
|
Returns the value of the expression in a target row,
specified as a relative offset from the current row. If the offset is -1,
then the result will be returned for the previous value in the scope and
direction.
|
LOOKUP(SUM([Profit]), FIRST ()+2) computes the
SUM([Profit]) in the third row of the partition.
|
|
MODEL_PERCENTILE (model_specification (optional),
target_expression, predictor_expression(s))
|
Returns the probability (between 0 and 1) of the
expected value being less than or equal to the observed mark, defined by the
target expression and other predictors. This is the Posterior Predictive
Distribution Function, also known as the Cumulative Distribution Function
(CDF). This function is the inverse of MODEL_QUANTILE
|
MODEL_PERCENTILE(SUM(([Sales]), COUNT([Orders]))
returns the quantile of the mark for sum of sales, adjusted for count of
orders
|
|
MODEL_QUANTILE (model_specification
(optional), quantile, target_expression, predictor_expression(s))
|
Returns a target numeric value within the probable
range defined by the target expression and other predictors, at a specified
quantile. This is the Posterior Predictive Quantile. This function is the
inverse of MODEL_PERCENTILE.
|
MODEL_QUANTILE(0.5, SUM([Sales]), COUNT([Orders]))
returns the median (0.5) predicted sum of sales, adjusted for count of orders.
|
|
PREVIOUS_VALUE (expression)
|
Returns the value of the expression in the previous
row.
|
SUM([Profit]) + PREVIOUS_VALUE(1) computes the running total of
SUM([Profit]).
|
|
RANK(expression, [order])
RANK_DENSE,
RANK_MODIFIED,
RANK_UNIQUE,
RANK_PERCENTILE
|
Returns the standard competition rank for the current
row in the partition.
|
RANK(AVG([Test Score]))
|
|
RUNNING_SUM(expression),
RUNNING_AVG,
RUNNING_MAX, RUNNING_MIN, and RUNNING_COUNT are similar
|
Returns the running sum of the given expression, from
the first row in the partition to the current row.
|
RUNNING_SUM(SUM([Profit]) computes the running sum of
SUM([Profit])
|
|
WINDOW_AVG(expression,[start,
end]) WINDOW_SUM, WINDOW_MAX, WINDOW_MIN, WINDOW_MEDIAN, WINDOW_COUNT,
WINDOW_PERCENTILE, WINDOW_STDEV, WINDOW_STDEVP, WINDOW_VAR,
WINDOW_VARP are all similar
|
Returns the average of the expression within the
window. If the optional start and end are omitted, the entire partition is
used.
|
WINDOW_AVG(SUM([Profit]), FIRST()+1, 0) computes the average of
SUM([Profit]) from the second row to the current row.
|
|
WINDOW_CORR(expression1,
expression2, [start, end])
|
Returns the Pearson correlation coefficient of the
two expressions within the window. If the optional start and end are omitted,
the entire partition is used.
|
WINDOW_CORR(SUM([Sales]), SUM([Profit])) returns a
value from -1 to 1. The result is equal to 1 for an exact positive linear
relationship, 0 for no linear relationship, and -1 for an exact negative
linear relationship.
|
|
WINDOW_COVAR(expression1, expression2, [start, end])
|
WINDOW_COVARP is similar, but for a population, instead of a sample. Returns the
sample covariance of two expressions within the window. If the optional start
and end are omitted, the entire partition is used. If the two expressions are
the same, a value is returned that indicates how widely the variables are
distributed.
|
WINDOW_COVAR(SUM([Sales]), SUM([Profit])) returns a
positive number if the expressions tend to vary together, on average.
|
