1. Analytical Application Infrastructure User Guide
  2. Unified Analytical Metadata
  3. References
  4. Function Types and Functions

Function Types and Functions

You select the type of function for your expression from the Type list.

The choices are:

  • Mathematical Functions
  • Date Functions
  • String Functions
  • Other Functions

The type of function you select determines the choices available in the Function box. These unique functions in the Functions Sub-container enable you to perform various operations on the data.

The following table lists each available function and Detail on the operations of each function in which it appears.

Function Type Function Name Notation Description Syntax Example
Mathematical Absolute ABS(a) Returns the positive value of the database column {ABS( } followed by {EXPR1 without any embedded or outermost left-right parentheses pair} followed by { )} ABS (-3.5) = 3.5.ABS(F), ABS(F + C), ABS(F + C * R + F) are possible. However, ABS((F + C + R)), ABS((F + (MAX * CEILING))) are not possible.
Ceiling Ceiling (a) Rounds a value to the next highest integer Ceiling(column or expression) 3.1 becomes 4.0, 3.0 stays the same
Greatest

Greatest(a,b)

GREATEST(column or expression, column or expression)

 Returns the greater of 2 numbers, formulas, or columns Greatest(column or expression, column, or expression Greatest(1.9,2.1) = 2.1
Least

Least (a,b)

LEAST(column or expression, column or expression)

 Returns the lesser of 2 numbers, formulas, or columns Least(column or expression, column or expression Least(1.9,2.1) = 1.9
Natural Log

LN(number)

LN(a)

 Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904). LN(number) where number is the positive real number for which you want the natural logarithm

LN(86) equals 4.454347

LN(2.7182818) equals 1
Minimum Min(a) Returns the minimum value of a -database column Max(Column)
Maximum Max(a) Returns the maximum value of a -database column Max(Column)
Power

Power(a,b)

POWER(coefficient, exponent)

 Raises one value to the power of a second {POWER(} followed by {EXPR1 without any embedded or outermost left-right parentheses pair followed by {,} followed by {EXPR1 without any embedded or outermost left-right parentheses pair} followed by { )}

Valid examples:

POWER(F, R)

POWER(F + C * R, F / R)

Invalid examples:

POWER((F/R), F + R)

POWER((F + C), (C * R))

POWER(F + POWER, R)

POWER( MAX, C)

Round

Round(a,b)

ROUND (number, precision)

 Rounds a value to a number of decimal places Round(x, n) returns x rounded to n decimal places Round(10.52354,2)=10.52
Sum Sum(a)

Sums the total value of a database column. Sum is a multi-row function, in

contrast to +, which adds 2 or more values in a given row (not column)

 Sum(Column)
Weighted Average

WAvg(a,b)

WAvg (column being averaged, weight column)

Takes a weighted average of one database column by a second Column.

WAvg cannot appear in any expression.

If you have two formulas called F1 and F2, both of which are WAvg functions,

then you can form a third formula F3 as F1 + F2. If F3 is chosen as a calculated column, then an error message appears and the SQL code is not generated for that column. This is similar for nested WAvg functions if F3 is WAvg and it has F1 or F2 or both as its parameters.

 WAvg(Column A, Column B) WAvg(DEPOSITS.CUR_NET_RATE,DEPOSITS.CUR_BOOK_BAL)
Note : You cannot use the Maximum and Minimum functions as calculated columns or in Data Correction Rules. The Maximum, Minimum, Sum, and Weighted Average functions are multi-row formulas. They use multiple rows in calculating the results.
Date Build Date BuildDate(year,month,days)

Requires three parameters, (CCYY,MM,DD) (century and year, month, day). It returns a valid data and enables you to build a date from components.

CAUTION: If the parameters are entered incorrectly, the date is invalid.

BUILDDATE(CCYY,MM,DD)

BuildDate(95,11,30) is invalid (invalid century).

BuildDate(1995,11,30) is valid.
Go Month GoMonth(date,months) Advances a date by x number of months. Go Month does not know the calendar. For example, it cannot predict the last day of a month. Typical functionality is illustrated in the following table: GOMONTH(Date column, Number of months to advance)

GOMONTH(DEPOSITS.ORIGINATION_DATE,DEPOSITS.ORG_TERM)

Valid examples:

GOMONTH(F, F + R + C)

GOMONTH(F, R)

Invalid examples:

GOMONTH(F + (R + C), MAX)

GOMONTH((F * C), F)

For Example:

Figure 8-50 Example


This is an example.
Year Year(date) Year(x) returns the data for year x. Year(Column) returns the year in the column, where the column is a date column. Year(Origination Date) returns the year of the origination date.
Month Month(date) Month(x) returns the month in x, where x is a numbered month. Month(Column) returns the month in the column, where the column is a date column.

Month(9) returns September.

Month(Origination Date) returns the month of the origination date.
String Trim All AllTrim(a)

Trims leading and following spaces, enabling the software to recognize numbers (entered in

All Trim) as a numeric value, which can then be used in calculating
Other If statement If(a=b,c,d)

The IF function should always have odd number of parameters separated by commas. The first parameter is an expression followed by a relational operator, which is in turn followed by an expression.

Note: Avoid embedding multiple individual formulas in subsequent formulas. This can create an invalid formula.

If(Condition, Value if True, Value if False).

{IF( } followed by EXPR2 followed by {> | < | <> | = | >= | <=} followed by EXPR2 followed by {{,} followed by EXPR followed by ),} followed by EXPR}n followed by {)} where n = 1, 2, 3, .....

If(LEDGER_STAT.Financial= 110, LEDGER_STAT.Month 1 Entry,0)

IF(((MAX + SUM) >= 30), F, POWER) is valid.
Lookup Lookup(OrigCol,LookupCol,…,ReturnedCol)

Enables you to assign values equal to values in another table for data correction.

LOOKUP function should always have an odd number of parameters separated by commas and with a minimum of 3 parameters.

Note: Lookup is used exclusively for data correction.

Lookup(O1,L1,O2,L2,...On,Ln,R) where O=Column from Original table

L=Column from Lookup table

R=Column to be Returned

So the previous statement would read:

where O1=L1 and O2=L2... Returned value R

Valid examples:

LOOKUP(F, R, R)

LOOKUP(F, R, F, F, F)

Invalid examples:

LOOKUP(F)

LOOKUP(F, R)

LOOKUP(F + R, (F + R), MAX)