A.7 Numeric Functions

Oracle OLAP offers the following types of numeric functions:

General numeric functions for typical mathematical processing (for example, ranking and finding logs and tangets). For listing. see Table A-9, "General Numeric Functions".
Financial functions. For listing. see Table A-10, "Financial Functions".
Statistical functions. For listing. see Table A-11, "Statistical Functions".
Time-series functions such as LAG and MOVINGMIN. For listing. see Table A-12, "Time-Series Functions".
Aggregation functions, such as COUNT and TOTAL. For listing. see Table A-13, "Aggregation Functions"

A.7.1 General Numeric Functions

Table A-9, "General Numeric Functions" lists the OLAP DML functions for calculation.

Table A-9 General Numeric Functions

Function	Description
ABS	Calculates the absolute value of an expression.
ANTILOG	Calculates the value of e (the base of natural logarithms) raised to a specific power.
ANTILOG10	Calculates the value of 10 raised to a specified power.
ARCCOS	Calculates the angle value (in radians) of a specified cosine.
ARCSIN	Calculates the angle value (in radians) of a specified sine.
ARCTAN	Calculates the angle value (in radians) of a specified tangent.
ARCTAN2	Returns a full-range (0 - 2 pi) numeric value indicating the arc tangent of a given ratio.
BITAND	Computes an AND operation on the bits of two integers.
CEIL	Returns the smallest whole number greater than or equal to a specified number.
COS	Calculates the cosine of an angle expression.
COSH	Calculates the hyperbolic cosine of an angle expression.
DECODE	Compares one expression to one or more other expressions and, when the base expression is equal to a search expression, returns the corresponding result expression; or, when no match is found, returns the default expression when it is specified, or NA when it is not.
EXP	Returns e raised to the nth power, where e equals 2.71828183....
FLOOR	Returns the largest whole number equal to or less than a specified number.
GREATEST	Returns the largest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison.
INSTRB	Calculates the integer part of a decimal number by truncating its decimal fraction.
LEAST	Returns the smallest expression in a list of expressions. All expressions after the first are implicitly converted to the data type of the first expression before the comparison.
LOG function	Computes the natural logarithm of an expression.
LOG10	Computes the logarithm base 10 of an expression.
MAX	Calculates the larger value of two expressions.
MIN	Calculates the smaller value of two expressions.
NULLIF	Compares one expression with another and returns NA when the expressions are equal, or the base expression when they are not.
REM	Returns the remainder after one numeric expression is divided by another.
ROUND (for numbers)	Returns the number rounded to the nearest multiple of a second number you specify or to the number of decimal places indicated by the second number.
SIGN	Returns a value that indicates if a specified number is less than, equal to, or greater than 0 (zero).
SIN	Calculates the sine of an angle expression. The result returned by SIN is a decimal value with the same dimensions as the specified expression.
SINH	Calculates the hyperbolic sine of an angle expression.
SQRT	Computes the square root of an expression.
TAN	Calculates the tangent of an angle expression.
TANH	Calculates the hyperbolic tangent of an angle expression.
TRUNC (for numbers)	Truncates a number to a specified number of decimal places.
WIDTH_BUCKET	Returns the bucket number into which the value of an expression would fall after being evaluated.

A.7.2 Financial Functions

Table A-10, "Financial Functions" lists the OLAP DML functions for financial calculation.

Table A-10 Financial Functions

Function	Description
DEPRDECL	Calculates the depreciation expenses for a series of assets. DEPRDECL uses the declining balance method to depreciate the assets over the specified lifetime of the assets.
DEPRDECLSW	Calculates the depreciation expenses for a series of assets. DEPRDECLSW uses a variation on the declining balance method to depreciate assets over the specified lifetime of the assets.
DEPRSL	Calculates the depreciation expenses for a series of assets. DEPRSL uses the straight-line method to depreciate the assets over the specified lifetime of the assets.
DEPRSOYD	Calculates the depreciation expenses for a series of assets. DEPRSOYD uses the sum-of-years'-digits method to depreciate the assets over the specified lifetime of the assets.
FINTSCHED	Calculates the interest portion of the payments on a series of fixed-rate installment loans that are paid off over a specified number of time periods.
FPMTSCHED	Calculates a payment schedule (principal plus interest) for paying off a series of fixed-rate installment loans over a specified number of time periods.
GROWRATE	Calculates the growth rate of a time-series expression, based on the first and last values of the series.
IRR	Computes the internal rate of return associated with a series of cash flow values. Each value of the result is calculated to be the discount rate for a period that makes the net present value of the corresponding cash flows equal to zero.
NPV	Computes the net present value of a series of cash flow values.
VINTSCHED	Calculates the interest portion of the payments on a series of variable-rate installment loans that are paid off over a specified number of time periods.
VPMTSCHED	Calculates a payment schedule (principal plus interest) for paying off a series of variable-rate installment loans over a specified number of time periods.

A.7.3 Statistical Functions

Table A-11, "Statistical Functions" lists the OLAP DML functions for statistical calculation.

Table A-11 Statistical Functions

Statement	Description
CATEGORIZE	Groups the values of a numeric expression into categories.
CORRELATION	Returns the correlation coefficients for the pairs of data values in two expressions.
NORMAL	Returns a random value from a normal distribution with a specified mean and standard deviation. The result returned by NORMAL is dimensioned by all the dimensions of the mean and standard deviation expressions.
RANDOM	Produces a number that is randomly distributed between specified low and high boundaries.
STDDEV	Calculates the standard deviation of the values of an expression.

A.7.4 Time-Series Functions

Table A-12, "Time-Series Functions" lists the OLAP DML time-series functions.

Table A-12 Time-Series Functions

Function	Description
CUMSUM	Computes cumulative totals over a dimension.
LAG	Returns the values of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension.
LAGABSPCT	Returns the percentage difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression.
LAGDIF	Returns the difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression.
LAGPCT	Returns the percentage difference between the value of a dimensioned variable or expression at a specified offset of a dimension prior to the current value of that dimension and the current value of the dimensioned variable or expression.
LEAD	Returns the values of a dimensioned variable or expression at a specified offset of a dimension subsequent to the current value of that dimension.
MOVINGAVERAGE	Computes a series of averages for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGAVERAGE computes the average of the data in the range specified, relative to the current dimension value.
MOVINGMAX	Returns a series of maximum values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMAX searches the data for the maximum value in the range specified, relative to the current dimension value.
MOVINGMIN	Returns a series of minimum values for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGMIN searches the data for the minimum value in the range specified, relative to the current dimension value.
MOVINGTOTAL	Computes a series of totals for the values of a dimensioned variable or expression over a specified dimension. For each dimension value in status, MOVINGTOTAL computes the total of the data in the range specified, relative to the current dimension value.

A.7.5 Aggregation Functions

Table A-13, "Aggregation Functions" lists the OLAP DML aggregation functions. The OLAP DML also provides an aggmap object that you can use to aggregate data, see "Aggregation Statements" for a list of related OLAP DML statements.

Table A-13 Aggregation Functions

Statements	Description
ANY	Returns YES when any values of a Boolean expression are TRUE, or NO when none of the values are TRUE.
AVERAGE	Calculates the average of the values of an expression.
COUNT	Retrieves the number of TRUE values of a Boolean expression, or 0 (zero) if no values of the expression are TRUE.
EVERY	Returns YES when every value of a Boolean expression is TRUE, or NO if any value of the expression is FALSE.
LARGEST	Returns the largest value of an expression. You can use this function to compare numeric values or date values.
MEDIAN	Calculates the median of the values of an expression.
MODE	Returns the mode (the most frequently occurring value) of a numeric expression; or NA when there are no duplicate values in the data.
NONE	Returns YES when no values of a Boolean expression are TRUE; or NO when any value of the expression is true.
PERCENTAGE	Computes the percent of total for each value in a numeric expression.
SMALLEST	Returns the smallest value of an expression. You can use this function to compare numeric values or date values.
TCONVERT	Converts data from one dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to another dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. You can specify an aggregation method or an allocation method to use in the conversion.
TOTAL	Calculates the total of the values of an expression.