Numeric Functions

Aggregate

returns a calculated value by using the appropriate aggregate function, which is based on the aggregation type of the member.

Aggregate(<Set[,<Numeric
                              Expression>])

Avg

returns the average value of a numeric expression that is evaluated over a set. Avg(<Set>[,<Numeric Expression>])Example: The following example shows a moving average across all dimensions of time.

Avg(time.currentmember.lag (if(time.currentmember.level
                              is time.month_num,2, if(time.currentmember.level is time.quarter,1,0)))
                              :time.currentmember, measures.[total_retail_pricesum])

The Total_Retail_PriceSUM is included in the following query to see the difference between the moving average and the total retail price.

SELECT  {[measures].[movingaverage],[measures]. [total_retail_pricesum]
                              } ON COLUMNS  , {[time].[yqm].[all yqm].children } ON ROWS   FROM
                              [orionstar]

CoalesceEmpty

returns a coalesced value. This value is derived when an empty cell value is coalesced to a number or string.

CoalesceEmpty(<Numeric Expression>[,<Numeric
                              Expression>])

Correlation

returns the correlation of two series that are evaluated over a set.

Correlation(<Set>,<Numeric
                              Expression>[,<Numeric Expression>])

Count

depending on the collection, returns the number of items in a collection. <Dimension>|<Hierarchy>.Levels.Count<Tuple>.Count<Set>.CountCount(<Set>[,ExcludeEmpty | IncludeEmpty])

Covariance

returns the population covariance of two series that are evaluated over a set by using the biased population formula.

Covariance(<Set>,<Numeric
                              Expression>[,<Numeric Expression>])

CovarianceN

returns the sample covariance of two series that are evaluated over a set by using the unbiased population formula.

CovarianceN(<Set>,<Numeric
                              Expression>[,<Numeric Expression>])

DistinctCount

returns the number of distinct, non-empty tuples in a set. DistinctCount(<Set>)

IIf

returns one of two numeric or string values that are determined by a logical test.

IIF(<Logical Expression>, <Numeric Expression1>, <Numeric
                              Expression2>)

Note: If a string is returned, then it is a string function, not a numeric function.

LinRegIntercept

calculates the linear regression of a set and returns the value of b in the regression line y = ax + b.

LinRegIntercept(<Set>,<Numeric
                              Expression>[,<NumericExpression>])

LinRegPoint

calculates the linear regression of a set and returns the value of y in the regression line y = ax + b.

LinRegPoint(<NumericExpression>,<Set>,<NumericExpression>
                              [,<Numeric Expression>])

LinRegR2

calculates the linear regression of a set and returns R² (the coefficient of determination).

(Set, Numeric Expression[,
                              Numeric Expression])

LinRegSlope

calculates the linear regression of a set and returns the value of a in the regression line y = ax + b. LinRegSlope(<Set>,<NumericExpression>[,<NumericExpression>])

LinRegVariance

calculates the linear regression of a set and returns the variance associated with the regression line y = ax + b. (Set, Numeric Expression[, Numeric Expression])

Max

returns the maximum value of a numeric expression that is evaluated over a set. Max(<Set>[,<Numeric Expression>])

Median

returns the median value of a numeric expression that is evaluated over a set. Median(<Set>[,<Numeric Expression>])

Min

returns the minimum value of a numeric expression that is evaluated over a set. Min(<Set>[,<Numeric Expression>])

Ordinal

returns the zero-based ordinal value that is associated with a level. <Level>.Ordinal

Range

returns the range, which is the difference between the maximum and minimum value of a numeric expression that is evaluated over a set.

Range
                              (<Set>[,<Numeric Expression>])

Rank

returns the one-based rank of a specified tuple in a specified set.

Rank(<Tuple>,<set>[,<Calc
                              Expression>])

RollupChildren

returns a value that is generated by rolling up the values of the children of a specified member by using the specified unary operator.

RollupChildren(<Member>,<String
                              Expression>)

Stdev

using the unbiased population formula, returns the sample standard deviation of a numeric expression that is evaluated over a set.

Stdev(<set>[,<Numeric
                              Expression>])

StdevP

using the biased population formula, returns the population standard deviation of a numeric expression that is evaluated over a set.

StdevP(<set>[,<Numeric
                              Expression>])

StrToValue

returns a value from a string expression. StrToValue(<StringExpression>)

Sum

returns the sum of a numeric expression that is evaluated over a set.

Sum(<Set>[,<Numeric
                              Expression>])

Value

returns the value of a measure. <Member>.Value

Var

using the unbiased population formula, returns the sample variance of a numeric expression that is evaluated over a set.

Var(<Set>[,<Numeric
                              Expression>])

VarP

using the biased population formula, returns the population variance of a numeric expression that is evaluated over a set.

VarP(<Set>[,<Numeric
                              Expression>])