## A.7 Numeric Functions

Oracle OLAP offers the following types of numeric 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.
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.