Using Built-in Functions in Analytic Models

This chapter provides reference information about the functions that are delivered with PeopleSoft Analytic Calculation Engine.

Built-in Function Reference

Use the following built-in functions in an analytic model's rules and user functions:

ABS

Syntax

ABS(Data)

Description

The ABS function returns the absolute (positive) value of Data.

Returns

The absolute (positive) value of Data.

Example

The following examples employ the ABS built-in function:

ABS(5) returns 5.
ABS(-5) returns 5.
ABS(0) returns 0.

ACOS

Syntax

ACOS(Data)

Description

The ACOS function returns the arc-cosine of Data. The result is the angle (in radians) whose cosine equals Data.

The value of Data must fall between –1 and 1; otherwise, ACOS returns an error value.

To convert from radians to degrees, multiply by 180 / PI( ). (The PI function returns the value of PI.)

Returns

The arc-cosine of Data.

Example

The following examples employ the ACOS built-in function:

ACOS(0.5) returns 1.0471975512 (angle in radians).
ACOS(0.5) * 180 / PI( ) returns 60 (angle in degrees).
ACOS(SQRT(2) / 2) returns 0.7853981634 (angle in radians).
ACOS(SQRT(2) / 2) * 180 / PI( ) returns 45 (angle in degrees).

ARGUMENTS Declaration

Syntax

ARGUMENTS(argument1), argument2... argumentN

Description

The ARGUMENTS declaration passes values to functions within a user function.

Use the following guidelines to make user functions more powerful by giving them arguments:

Enter an ARGUMENTS declaration at the beginning of the rule, followed by an opening parenthesis.

Enter any dimension arguments next, separated by commas. A dimension argument always begins with a $, as in $Dim. Unlike the built-in functions, which never have more than one dimension argument, user functions can have multiple dimension arguments.

Include any expression arguments next, separated by commas. An expression argument always begins with a @, as in @Expr. To give the argument a default value, follow it with := and any valid expression.

Note. All optional arguments (that is, those with default values) must appear after all required arguments.

Enter a closing parenthesis and a semicolon.

Example

ARGUMENTS($Dim, @ExprToLookup, @Condition, @Direction := #FORWARD);
FORMEMBERS($Dim, @Direction,
	IF(@Condition, RETURN(@ExprToLookup))
);
RETURN(0)

ASC

Syntax

ASC(Text)

Description

The ASC function returns the first character of the Text argument to its ASCII equivalent number (for example, a number between 0-255). Use this function to convert a character into its ASCII value.

Returns

The first character of the Text argument to its ASCII equivalent number.

Example

For a cube formatted as a number, ASC("ABC") returns the 65.

ASIN

Syntax

ASIN(Data)

Description

The ASIN function returns the arc-sine of Data. The result is the angle (in radians) whose sine equals Data.

The value of Data must fall between –1 and 1; otherwise, ASIN returns an error value.

To convert from radians to degrees, multiply by 180 / PI( ). (The PI function returns the value of PI.)

Returns

The arc-sine of Data.

Example

The following examples employ the ASIN built-in function:

ASIN(0.5) returns 0.5235987756 (angle in radians).
ASIN(0.5) * 180 / PI( ) returns 30 (angle in degrees).
ASIN(SQRT(2) / 2) returns 0.7853981634 (angle in radians).
ASIN(SQRT(2) / 2) * 180 / PI( ) returns 45 (angle in degrees).

AT

Syntax

AT(Dimension, Member, Data)

Description

The AT function looks up the value of Data for a particular member in a dimension.

You can use the AT function in the following ways:

You can look up a value at a particular position in the dimension.
You can look up a value for a particular member by name.
You can associate members in one dimension with members in another dimension, and then look up an associated value for each member in the first dimension. For example, you can associate each employee with a job, and then look up the job salary for each employee.

Looking Up a Value at a Position

To look up a value at a particular position, use the position number for the member argument. You can also use an expression that returns the position number.

For example, to look up the value of SALES for the first member in the PRODUCTS dimension, use the following formula:

AT(PRODUCTS, 1, SALES)

To look up the value of SALES for the last member in the PRODUCTS dimension, use the following formula:

AT(PRODUCTS, NUMMEMBERS(PRODUCTS), SALES)

This works because the NUMMEMBERS function returns the number of members in the Products dimension, which is the position of the last member.

See NUMMEMBERS.

Looking Up a Value for a Member by Name

To look up a value for a particular member by name, use a member reference for the member argument.

For example, the following formula returns UNIT_COST divided by UNIT_PRICE for the Monitors product:

AT(PRODUCTS, [PRODUCTS:Monitors], UNIT_COST / UNIT_PRICE)

You can achieve the same result using member references after the data cube names, as follows:

UNIT_COST [PRODUCTS:Monitors] / UNIT_PRICE [PRODUCTS:Monitors]

To evaluate a complex expression for a single member, the AT function is more concise because you are not required to repeat the member reference for every data cube.

Looking Up an Associated Value

You can associate members in one dimension with members in another dimension, and then look up an associated value for each member in the first dimension. For example, suppose that you would like to associate each employee with a job, and then look up the job salary for each employee. To do this, perform the following:

Create a dimension called JOBS.
Create a data cube called EMPLOYEE_JOB. Format this data cube as a member of the JOBS dimension.
Create a dimension called EMPLOYEE. Attach this dimension to the EMPLOYEE_JOB data cube.
Create a data cube called SALARY_BY_JOB, which contains the salary for each job.
Create a data cube called EMPLOYEE_SALARY. Create the following formula for this data cube. You can look up the salary for each employee by using the name of the association data cube as the member argument:
```
AT(JOBS, EMPLOYEE_JOB, SALARY_BY_JOB)
```
For each employee, the formula looks up the number in SALARY_BY_JOB that is at the member indicated by EMPLOYEE_JOB.

Pushing Down Parent Member Data

The following are examples of user functions that push down parent member data:

AT (DIMENSION, Parent(DIMENSION), THISCUBE() * 0.2)
AT (DIMENSION, Parent(DIMENSION), THISCUBE() / CHILDCOUNT(DIMENSION, #DIRECT))

ATAN

Syntax

ATAN(Data)

Description

The ATAN function returns the arc-tangent of Data. The result is the angle (in radians) whose tangent equals Data.

To convert from radians to degrees, multiply by 180 / PI( ). The PI function returns the value of PI

Returns

The arc-tangent of Data

Example

The following examples employ the ATAN built-in function:

ATAN(0.5) returns 0.463647609 (angle in radians).
ATAN(0.5) * 180 / PI( ) returns 26.5650511771 (angle in degrees).
ATAN(1) returns 0.7853981634 (angle in radians).
ATAN(1) * 180 / PI( ) returns 45 (angle in degrees).

BREAK

Syntax

BREAK()

Description

The BREAK function causes an immediate break out of the current loop.

Example

SET(&Value, 1);
WHILE(&Value < THE_ABSOLUTE_MAXIMUM,
	SET(&Value, &Value * 2);
	IF(&Value = ENOUGH_ALREADY, BREAK());
	INC(&Value)
);
IF(&Value > ENOUGH_ALREADY, "More than enough", "Just right")

You normally use the BREAK function within an IF function to break out of a loop when a specified condition is achieved. To return Just right from the formula, ENOUGH_ALREADY must contain a value from the sequence 2, 6, 14, 30, and so on.

CASE

Syntax

CASE(Condition A : Result A, Condition B : Result B {,...})

Description

The CASE function returns the Result that corresponds to the first true Condition; if none of the conditions is true, it returns zero.

Returns

The Result that corresponds to the first true Condition; if none of the conditions is true, it returns zero.

Example

Suppose a company awards its salespeople the following commissions:

A 10 percent commission if their sales are at least 50,000 USD.
An 8 percent commission if their sales are at least 30,000 USD.
A 5 percent commission if their sales are at least 15,000 USD.

You can calculate the commission rate for a salesperson with the following formula:

CASE(SALES >= 50000 : 0.10, SALES >= 30000 : 0.08, SALES >= 15000 : 0.05)

If SALES is 45000, this formula returns 0.08. Notice that the CASE function returns the result for the first true condition, even if some of the remaining conditions are true.

The above formula returns zero if SALES is less than 15000. Suppose that the company awards a 3 percent commission on all sales under 15,000 USD. You can model this with the following formula:

CASE(SALES >= 50000 : 0.10, SALES >= 30000 : 0.08, SALES >= 15000 : 0.05, #DEFAULT : 0.03)

The last condition (#DEFAULT) is always equivalent to TRUE, so the CASE function returns 0.03 if SALES is less than 15000. If you want the CASE function to return a default value other than zero, use #DEFAULT as the last condition.

CHANGE

Syntax

CHANGE(Dimension, Data, {Count})

Description

The CHANGE function returns the difference between the value of Data for the member being calculated and the value of Data for Count members back. If Count is omitted, it is assumed to be 1.

Example

Suppose you wish to calculate the monthly and yearly change in a data cube called SALES. If SALES uses a dimension called MONTHS, use the following formula to calculate the MONTHLY_CHANGE data cube:

CHANGE(MONTHS, SALES)

Because the Count argument is omitted, the program assumes it to be 1. Consequently, the program calculates the change in SALES from the previous month to the month being calculated.

Calculate the YEARLY_CHANGE data cube by using 12 for the third argument:

CHANGE(MONTHS, SALES, 12)

This formula calculates the change in SALES from 12 months ago to the month being calculated.

CHILDCOUNT

Syntax

CHILDCOUNT(Dimension, {#DIRECT/#ALL/#DETAILS, {Parent Member}})

Description

The CHILDCOUNT function returns the number of a Parent member's children. This function takes the following one required argument and two optional arguments:

Dimension: The dimension to use.
For the second optional argument, select from these predefined constants:
- #DIRECT.
- #ALL.
- #DETAILS.
  
  See Understanding the Elements of Rules.
Parent Member: This is an optional argument. If you do not use this optional argument, the function applies to the member that is currently being evaluated.

Returns

The number of a parent member's children.

Example

CHILDCOUNT (Region, #DIRECT, [Region:All_regions])

See Also

Pushed Down Data.

CHR

Syntax

CHR(Number)

Description

The CHR function returns the equivalent ASCII character of the number argument. The number must be in range from 0 to 255; otherwise, an invalid type error with be thrown.

Returns

The equivalent ASCII character of the number argument.

Example

For a cube formatted as text, CHR(65) returns the character A.

CONSOL

Syntax

CONSOL(Dimension, Data)

Description

The CONSOL function returns the value of Data for the total member of Dimension.

Returns

The value of Data for the total member of Dimension

Example

Suppose an analytic model contains a data cube called SALES that uses a dimension called PRODUCTS. Use the following formula to calculate each product's sales as a percentage of total sales:

SALES / CONSOL(PRODUCTS, SALES)

This formula divides each product's sales by the consolidated value for SALES.

COS

Syntax

COS(Data)

Description

The COS function returns the cosine of Data, where Data represents an angle in radians.

To convert from degrees to radians, multiply by PI( ) / 180. The PI function returns the value of PI

Example

The following examples employ the COS built-in function:

COS(PI( ) / 3) returns 0.5 (cosine of PI / 3 radians).
COS(PI( ) / 2) returns 0 (cosine of PI / 2 radians).
COS(45 * PI( ) / 180) returns 0.7071067812 (cosine of 45 degrees).

CUBEID

Syntax

CUBEID(Cube)

Description

The CUBEID function returns the internal ID of the cube in the analytic calculation engine. Note that the actual ID for the cube may vary in the analytic calculation engine when the model has been changed. For example, when a part is added or deleted. Do not use absolute numbers to compare the return of the CUBEID function. The CUBEID function may be useful if you have a generic user function and you want to pass different data cubes as expression arguments.

Returns

The internal ID of the cube in the analytic calculation engine.

Example

IF(CUBEID(@MyCube) = CUBEID(REVENUE), SPECIAL_CONDITION_CALCULATION, DEFAULT_CALCULATION)

This is an example of incorrect usage of the CUBEID function:

IF( CUBEID(@MYCUBE) = 512, SPECIAL_CONDITION, DEFAULT_CONDITION)

CUMAVG

Syntax

CUMAVG(Dimension, Data, {Count})

Description

The CUMAVG function returns the cumulative average of Data for the last Count members of Dimension. If Count is omitted, CUMAVG returns the cumulative average of all members up to the member being calculated.

Example

The following examples provide uses of the CUMAVG function:

Example 1

Suppose an analytic model contains a data cube called SCORES that uses a dimension called TESTS. Use the following formula to compute the average of all test scores up to the test being calculated:

CUMAVG(TESTS, SCORES)

This formula calculates Cum_Avg_Score for Test 2 by averaging the scores for Test 1 and Test 2; it calculates Cum_Avg_Score for Test 3 by averaging the scores for Test 1, 2, and 3; and so on. Because the third argument is omitted, the function averages the scores for all tests up to the test being calculated.

Example 2

Suppose an analytic model contains a data cube called SALES that uses a dimension called MONTHS. Compute the average sales for the last six months as follows:

CUMAVG(MONTHS, SALES, 6)

Note that for the first five months, the CUMAVG function cannot look back six months, because this would go back before the first month in the MONTHS dimension. The program solves this problem by averaging the sales for all months up to the month being calculated. After the first five months, the function averages the sales for the six months up to the month being calculated.

CUMSUM

Syntax

CUMSUM(Dimension, Data, {Count})

Description

The CUMSUM function returns the cumulative sum of Data for the last Count members of Dimension. If Count is omitted, CUMSUM returns the cumulative sum of all members up to the member being calculated.

Returns

The cumulative sum of Data for the last Count members of Dimension.

If Count is omitted, CUMSUM returns the cumulative sum of all members up to the member being calculated.

Example

Suppose an analytic model contains a data cube called PROFIT that uses a dimension called MONTHS. Use the following formula to calculate the cumulative profit for all months up to the month being calculated:

CUMSUM(MONTHS, PROFIT)

Use the following formula to calculate the cumulative profit for the three months up to the month being calculated:

CUMSUM(MONTHS, PROFIT, 3)

DAVG

Syntax

DAVG(Dimension, {Data}, {Condition})

Description

The DAVG function returns the average of Data for the members in Dimension where Condition is True. If Condition is omitted, DAVG returns the average of Data for all members in Dimension. If Data is omitted, DAVG returns the average of the data cube being calculated, for all members up to the current member in Dimension.

Example

The following examples provide uses of the DAVG function:

Example 1

Suppose an analytic model contains a data cube called ADVERTISING_BY_PRODUCT and a data cube called UNITS_SOLD. Both data cubes use a dimension called PRODUCTS. Use the following formula to calculate the average units sold for all products:

DAVG(PRODUCTS, UNITS_SOLD)

The DAVG function does not include a condition, so the function averages UNITS_SOLD for all members in the PRODUCTS dimension. Use the following formula to calculate the average units sold for all products with advertising of at least USD 10,000:

AVG(PRODUCTS, UNITS_SOLD, ADVERTISING_BY_PRODUCT >= 10000)

In this case, the function averages UNITS_SOLD only for the products where ADVERTISING_BY_PRODUCT is greater than or equal to 10000.

Example 2

You can make the analysis more flexible by creating a dimension called RANGES and attaching it to AVG_UNITS_SOLD. Define a new data cube called AD_COST_MIN that uses the RANGES dimension. Each number in AD_COST_MIN defines the minimum value for the range, while the next number defines the upper limit for the range. Calculate the average units sold for each range as follows:

DAVG(PRODUCTS, UNITS_SOLD, ADVERTISING_BY_PRODUCT >= AD_COST_MIN .AND. ADVERTISING_BY_PRODUCT < NEXT(RANGES, AD_COST_MIN))

For each range, the formula averages only those products whose advertising cost is greater than or equal to the current AD_COST_MIN and less than the next AD_COST_MIN. (The NEXT function returns AD_COST_MIN for the next member in the RANGES dimension.)

See Also

NEXT.

DAY

Syntax

DAY({Date})

Description

The DAY function returns the day of the specified date. If Date is omitted, DAY returns the day of the calculation date.

Example

If A = 2004/03/15 and B = 2005/06/22, then DAY(A) returns 15 and DAY(B) returns 22.

Now suppose an analytic model contains a data cube called DAY_EXAMPLE that uses a dimension called DAYS, and contains the formula DAY_EXAMPLE = DAY( ). Because the argument is omitted, DAY returns the day for each date in the DAYS dimension.

Following is a more useful example of the DAY function: suppose you define a data cube called DAILY_RECEIPTS that uses a dimension called DAYS. You want to calculate the average receipts for each day of the month. In other words, you want to know the average receipts for the first day of each month, the average receipts for the second day of each month, and so on. To do this, create a dimension called DAY_NUM that contains members numbered 1 to 31. Then define a data cube called AVG_RECEIPTS_BY_DAY that uses the DAY_NUM dimension. Finally, enter the following formula for the AVG_RECEIPTS_BY_DAY data cube:

DAVG(DAYS, DAILY_RECEIPTS, DAY( ) = MEMBER(DAY_NUM))

For each DAY_NUM member in AVG_RECEIPTS_BY_DAY, the formula averages all DAILY_RECEIPTS where the day of the month equals the index of the DAY_NUM member. Thus, if the program is calculating the fifth DAY_NUM member for AVG_RECEIPTS_BY_DAY, it averages the receipts for the dates 2005/01/05, 2005/02/05, 2005/03/05, 2005/04/05, and so on, because these are the dates where the DAY( ) function returns 5.

See Also

DAVG, MEMBER.

DCOUNT

Syntax

DCOUNT(Dimension, {Condition})

Description

The DCOUNT function returns the number of members in Dimension for which Condition is true. If Condition is omitted, DCOUNT returns the number of members in Dimension.

Returns

The number of members in Dimension for which Condition is true. If Condition is omitted, DCOUNT returns the number of members in Dimension.

Example

Suppose an analytic model contains a data cube called UNITS_SOLD that uses a dimension called PRODUCTS. Use the following formula to find the number of products that sold more than 5000 units:

DCOUNT(PRODUCTS, UNITS_SOLD > 5000)

For an example of how to tabulate data for a series of ranges, see the entry for the DAVG function.

See Also

DAVG.

DDB

Syntax

DDB(Cost, Salvage, Life, Period)

Description

The DDB function returns the depreciation on an asset using the Double Declining Balance method. This is an accelerated depreciation method.

Parameters

Cost	The cost of the asset.
Salvage	The worth of the asset at the end of its useful life.
Life	The number of periods in the asset's useful life.
Period	The period for which you wish to determine the depreciation.

Returns

The depreciation on an asset using the Double Declining Balance method.

Example

Suppose you purchase a machine for USD 6,000, and you plan to sell it for USD 500 after 5 years. You can calculate the depreciation for each year as follows:

DDB(6000, 500, 5, 1) = 2400
DDB(6000, 500, 5, 2) = 1440
DDB(6000, 500, 5, 3) = 864
DDB(6000, 500, 5, 4) = 518
DDB(6000, 500, 5, 5) = 278

DEC

Syntax

DEC(Number Original Value, Number Amount to Decrement)

Description

The DEC function returns an decremented value based on an original value and the amount to increment.

Returns

An decremented value based on an original value and the amount to increment.

Example

DEC(&NumMonths, &EndMonth - &StartMonth - 1)

This formula subtracts the months between the start and end month to the variable &NumMonths. DEC function is useful in FOR or WHILE functions to decrement loop variables.

See Also

FOR, WHILE.

DLOOKUP

Syntax

DLOOKUP(Dimension, Data, Condition, {Direction})

Description

The DLOOKUP function returns Data for the first Member in Dimension where Condition is true. If Direction is omitted or zero, the function scans forward from the first member. If Direction is nonzero, the function scans backward from the last member. If no member in Dimension fulfills the Condition, the function returns zero.

Returns

Data for the first member in Dimension where Condition is true. If Direction is omitted or zero, the function scans forward from the first member. If Direction is nonzero, the function scans backward from the last member. If no member in Dimension fulfills the Condition, the function returns zero.

Example

Suppose that a company awards its salespeople a 10 percent commission if their sales are at least USD 50,000, an 8 percent commission if their sales are at least USD 30,000, a 5 percent commission if their sales are at least USD 15,000, and a 1 percent commission if their sales are less than USD 15,000. One way to calculate the commission is to create a lookup table. Define a dimension called RANGES and attach it to data cubes called SALES_MINIMUM and LOOKUP_RATE. Each number in SALES_MINIMUM defines the minimum value for the sales range, while the next number defines the upper limit for the range. LOOKUP_RATE holds the commission rate for each range. Use the following formula to calculate the commission rate:

DLOOKUP(RANGES, LOOLUP_RATE, SALES >= SALES_MINIMUM, 1)

Because the last argument of DLOOKUP is 1, the function starts with the last member of RANGES and scans backwards until SALES is greater than or equal to SALES_MINIMUM. It is important to scan backwards to find the highest lookup rate for which the condition is true. Otherwise, the formula returns the lowest lookup rate no matter how high the value of SALES is.

DMAX

Syntax

DMAX(Dimension, Data, {Condition})

Description

The DMAX function returns the maximum of Data for the members in Dimension where Condition is True. If Condition is omitted, DMAX returns the maximum of Data for all members in Dimension. If Data is omitted, DMAX returns the maximum of the data cube being calculated, for all members up to the current member in Dimension.

Returns

The maximum of Data for the members in Dimension where Condition is True. If Condition is omitted, DMAX returns the maximum of Data for all members in Dimension. If Data is omitted, DMAX returns the maximum of the data cube being calculated, for all members up to the current member in Dimension.

Example

Suppose that an analytic model contains a data cube called ADVERTISING_BY_PRODUCT and a data cube called UNITS_SOLD. Both data cubes use a dimension called PRODUCTS. Use the following formula to calculate the maximum units sold for all products:

MAX(PRODUCTS, UNITS_SOLD)

The DMAX function does not include a condition, so the function finds the maximum of UNITS_SOLD for all members in the PRODUCTS dimension. Use the following formula to calculate the maximum units sold for all products with advertising under USD 10,000:

DMAX(PRODUCTS, UNITS_SOLD, ADVERTISING_BY_PRODUCT < 10000)

In this case, the function finds the maximum units sold only for the products where ADVERTISING_BY_PRODUCT is less than 10000.

For an example of how to tabulate data for a series of ranges, see the entry for the DAVG function.

See Also

DAVG.

DMIN

Syntax

DMIN(Dimension, Data, {Condition})

Description

The DMIN function returns the minimum of Data for the members in Dimension where Condition is True. If Condition is omitted, DMIN returns the minimum of Data for all members in Dimension. If Data is omitted, DMIN returns the minimum of the data cube being calculated, for all members up to the current member in Dimension.

Returns

The minimum of Data for the members in Dimension where Condition is True. If Condition is omitted, DMIN returns the minimum of Data for all members in Dimension. If Data is omitted, DMIN returns the minimum of the data cube being calculated, for all members up to the current member in Dimension.

Example

Suppose that an analytic model contains a data cube called ADVERTISING_BY_PRODUCT and a data cube called UNITS_SOLD. Both data cubes use a dimension called PRODUCTS. Use the following formula to calculate the minimum units sold for all products:

DMIN(PRODUCTS, UNITS_SOLD)

The DMIN function does not include a condition, so the function finds the minimum of UNITS_SOLD for all members in the PRODUCTS dimension. Use the following formula to calculate the minimum units sold for all products with advertising of at least 10,000 USD:

MIN(PRODUCTS, UNITS_SOLD, ADVERTISING_BY_PRODUCT >= 10000)

In this case, the function finds the minimum units sold only for the products where ADVERTISING_BY_PRODUCT is greater than or equal to 10000.

For an example of how to tabulate data for a series of ranges, see the entry for the DAVG function.

See Also

DAVG.

DSUM

Syntax

DSUM(Dimension, {Data}, {Condition})

Description

The DSUM function returns the sum of Data for the members in Dimension where Condition is True. If Condition is omitted, DSUM returns the sum of Data for all members in Dimension. If Data is omitted, DSUM returns the sum of the data cube being calculated for all members up to the current member in Dimension.

Example

DSUM(PRODUCTS, UNITS_SOLD)

The DSUM function does not include a condition, so the function computes the sum of UNITS_SOLD for all members in the PRODUCTS dimension. Use the following formula to calculate the sum of units sold for all products with advertising of at least 10,000 USD:

DSUM(PRODUCTS, UNITS_SOLD, ADVERTISING_BY_PRODUCT >= 10000)

In this case, the function finds the sum of UNITS_SOLD only for the products where ADVERTISING_BY_PRODUCT is greater than or equal to 10000.

For an example of how to tabulate data for a series of ranges, see the entry for the DAVG function.

You can use the DSUM function without the Data argument to exercise complete control over the calculation of dimension totals for a particular data cube.

See Also

DAVG.

E

Syntax

E( )

Description

Use the E function to return the value of e, which is the base of natural logarithms.

Returns

The value of e.

Example

These examples employ the E built-in function:

E( ) returns 2.7182818285.
E( ) ^ 5 returns 148.4131591026 (e raised to the 5th power).

FIND

Syntax

FIND(Text Original String, Text Sub String, Number Starting Position)

Description

Use the FIND function to find a substring in the original string passed in starting from a specified start position in the original string.

Parameters

Text Original String	The text of the original string.
Text Sub String	The substring text to find.
Number Starting Position	The start position in the original string.

Returns

The position of the substring in the original string. The index is 1-based.

Example

The following formula finds the account name that begins with Expense:

IF(FIND(ACCOUNT_NAME, "Expense", 1) = 1, #TRUE, #FALSE)

FIRST

Syntax

FIRST(Dimension)

Description

Use the FIRST function to test for special cases that occur when the first member of a dimension is being calculated.

Parameters

Dimension

The dimension to test.

Returns

The FIRST function returns the first detail member regardless if the detail member is created out of a tree that is attached to the dimension. The FIRST function also returns True if the first member of Dimension is being calculated; otherwise, it returns False.

FOR

Syntax

FOR(Index, Start, Finish, Loop Body)

Description

The FOR function loops through a series of values.

Parameters

Index	The name of the variable that holds the index number.
Start	The index value at which to start the loop.
Finish	The index value at which to finish the loop.
Loop Body	The action to take at the current index.

Example

The following formula raises a base to an integral exponent without using the ^ operator:

IF(EXPONENT <> TRUNC(EXPONENT), RETURN(0.0));
SET(&Result, 1);
FOR(&Index, 1, ABS(EXPONENT),
	SET(&Result, &Result * BASE)
);
IF(EXPONENT >= 0, &Result, 1 / &Result)

In this formula, the FOR function sets the specified variable to each value at the beginning of the loop, counting up if Finish is greater than Start, and counting down if Start is greater than Finish.

FORCHILDREN

Syntax

FORCHILDREN(Dimension, Expression, {#DIRECT/#ALL/#DETAILS, {Parent Member}})

Note. The third and fourth arguments are optional.

Description

The FORCHILDREN function loops through all child members of a dimension's parent member, unless you interrupt the FORCHILDREN function with a BREAK function.

Parameters

The FORCHILDREN function takes two required arguments and two optional arguments. The first and second arguments are required. The third and fourth arguments are optional.

Dimension	The dimension to use.
Expression	The expression to evaluate for each iteration.
#DIRECT, #ALL, #DETAILS	This argument is optional. Select from one of these predefined constants. Note. #DIRECT is the default constant.
Parent Member	This argument is optional. If you do not use this argument, the function applies to the member that is currently being evaluated.

Example

FORCHILDREN(Region, 
		  IF(Sales > & MaxSales,
			&MaxSales := Sales;
			&Region:= Member;
		),
		#DIRECT,
		[Region:USA]
	);
  &Region;

See Also

Pushed Down Data.

FORMEMBERS

Syntax

FORMEMBERS(Dimension, Direction, Expression)

Description

The FORMEMBERS function loops through all of the members of a dimension (unless you interrupt it with the BREAK function).

Parameters

Dimension	The dimension to use.
Direction	The direction to loop through the members (#FORWARD or #REVERSE).
Expression	The expression to evaluate for each iteration.

Example

Consider the following formula that uses DLOOKUP:

DLOOKUP(RANGES, COMMISSION_RATE, SALES >= SALES_LEVEL, #REVERSE)

You could achieve the same thing with the FORMEMBERS function:

FORMEMBERS(RANGES, #REVERSE,
	IF(SALES >= SALES_LEVEL, RETURN(COMMISSION_RATE))
);
RETURN(0)

Of course, in this case it is simpler just to use the DLOOKUP function, but the FORMEMBERS function makes it possible to perform more sophisticated lookups and tabulations. For example, the following formula returns the product that has the highest sales:

FORMEMBERS(PRODUCTS, #FORWARD,
	IF(SALES > &MaxSales,
		SET(&MaxSales, SALES);
		SET(&Product, MEMBER(PRODUCTS))
	)
);
&Product

Following is how you would have to do it without procedural logic:

DLOOKUP(PRODUCTS, MEMBER(PRODUCTS), SALES = DMAX(PRODUCTS, SALES))

The above version is shorter, but it is much less efficient than the version that uses procedural logic, because it calculates the DMAX repeatedly for every product.

You could eliminate some of the redundancy by using an expression block and a variable:

SET(&MaxSales, DMAX(PRODUCTS, SALES));
DLOOKUP(PRODUCTS, MEMBER(PRODUCTS), SALES = &MaxSales)

The previous version is more effective than the version that does not use procedural logic, but it is not as effective as the version that uses procedural logic. This is because in the version that does not use procedural logic, the FORMEMBERS function only loops through the products once. In the previous version, it loops through them twice—once for the DMAX and once for the DLOOKUP—although the DLOOKUP stops when it finds the right product.

FV

Syntax

FV(Rate, NPer, Pmt, PV, {Type})

Description

The FV function returns the Future Value of an investment with a present value of PV, where Pmt is invested for NPer periods at Rate per period. If Type is omitted or zero, FV assumes the investment is an ordinary annuity. If Type is nonzero, FV assumes the investment is an annuity due.

Note. Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Example

Suppose that you deposit USD 1,000 at the end of each year in a savings account that earns 6 percent per year. To determine the value of the account after 8 years, use the following formula:

FV(0.06, 8, -1000, 0) = 9897

If you deposit the USD 1,000 at the start of each year, use the following formula for the VALUE_OF_ACCT data cube. The 1 for the Type argument indicates an annuity due:

FV(0.06, 8, -1000, 0, 1) = 10491

If the account already has USD 3,000 in it at the start of the 8 years, use the following formula:

FV(0.06, 8, -1000, -3000, 1) = 15273

GROUPAVG

Syntax

GROUPAVG(Dimension to Group, Expression, Association 1, {Association 2 ...})

Description

Use the GROUPAVG function to average information by group. Expression contains the data to sum. The Assocation(s) indicate for which group(s) to average.

Example

Suppose that you want to average employee salaries by department. Create an analytic model definition that contains the following data cubes:

EMPLOYEE_SALARY, which uses a dimension called EMPLOYEES.

This data cube contains the salary for each employee.
AVERAGE_DEPARTMENT_SALARY, which uses a dimension called DEPARTMENTS.

This data cube contains the average salaries for each department.
An association data cube called EMPLOYEE_DEPT by performing the following:
- Create the EMPLOYEE_DEPT data cube.
- Format the EMPLOYEE_DEPT data cube as a member of the DEPARTMENTS dimension.
- Attach the EMPLOYEES dimension to the EMPLOYEE_DEPT data cube.

Calculate Department Salary with the following formula:

GROUPAVG(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_DEPT)

You can read this formula as follows: Average the employees' salaries by department.

To calculate group averages of all members that meet a condition, use an IF function as the expression, with #N/A as the third argument. For example, to calculate average officer salaries by department, you could use IF(IS_OFFICER, EMPLOYEE_SALARY, #N/A) instead of EMPLOYEE_SALARY in the formula above.

GROUPBY

Syntax

GROUPBY(Association)

Description

Use the GROUPBY function in a condition to group detail members by summary members (for example, employees by department). The argument must be an association data cube; otherwise, the function returns an error.

Example

Suppose that an analytic model contains an association data cube called DEPARTMENTS, which associates each employee with a particular department. The following formula for the EMPLOYEES_IN_DEPT cube uses DCOUNT and GROUPBY to calculate the number of employees in each department:

DCOUNT(EMPLOYEES, GROUPBY(DEPARTMENTS))

The following formula for the AVG_SALARY_BY_DEPT data cube uses DAVG and GROUPBY to calculate the average salary for each department :

DAVG(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS))

You can combine the GROUPBY function with other conditions. For example, the following formula for the OFFICER_SALARIES_BY_DEPT cube uses the DSUM function to calculate the total officer salaries in each department. By combining IS_OFFICER with the GROUPBY function, the formula ensures that only officers are included in the sum:

DSUM(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS) .AND. IS_OFFICER)

Note that DSUM(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS)) is equivalent to GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, DEPARTMENTS). Using DSUM with GROUPBY is more flexible, because you can include other conditions, as shown in the formula above. On the other hand, the GROUPSUM function calculates significantly faster. For this reason, if you want to sum by group and you do not need to include other conditions, use the GROUPSUM function.

See Also

DCOUNT, DAVG, DSUM, GROUPSUM.

GROUPMAX

Syntax

GROUPMAX(Dimension to Group, Expression, Association 1, {Association 2 ...})

Description

Use the GROUPMAX function to maximize information by group. Expression contains the data to maximize. The Association(s) indicate for which group(s) to maximize.

Example

Suppose that you want to maximize sales information by product. Create an analytic model definition that contains the following dimensions:

TRANSACTIONS, which contains a series of sales transactions.
PRODUCTS, which contains a dimension of products.

Define the following data cubes:

SALE_AMOUNT, which uses the TRANSACTIONS dimension. This data cube contains the amount of each sale.
An association data cube called PRODUCT_SOLD, which associates TRANSACTIONS with PRODUCTS.

See Creating Association Data Cubes.
MAXIMUM_SALES_BY_PRODUCT, which uses the PRODUCTS dimension.

Calculate this data cube with the following formula:
```
GROUPMAX(TRANSACTIONS, SALE_AMOUNT, PRODUCT_SOLD)
```

You can read this formula as follows: Find the maximum transactions' sale amounts by product.

To calculate group maximums of all members that meet a condition, use an IF function as the expression, with #N/A as the third argument. For example, use IF(VALID, SALE_AMOUNT, #N/A) instead of SALE_AMOUNT in the formula above.

GROUPMIN

Syntax

GROUPMIN(Dimension to Group, Expression, Association 1, {Association 2 ...})

Description

Use the GROUPMIN function to minimize information by group. Expression contains the data to minimize. The Association(s) indicate for which group(s) to minimize.

Example

Suppose that you want to minimize sales information by product. Create an analytic model definition that contains the following dimensions:

TRANSACTIONS, which contains a series of sales transactions.
PRODUCTS, which contains a series of products.

Define the following data cubes:

SALE_AMOUNT, which uses the TRANSACTIONS dimension.

This data cube contains the amount of each sale.
An association data cube called PRODUCT_SOLD, which associates TRANSACTIONS with PRODUCTS.

See Creating Association Data Cubes.
MINIMUM_SALES_BY_PRODUCT, which uses the PRODUCTS dimension. Calculate this data cube with the following formula:
```
GROUPMAX(TRANSACTIONS, SALE_AMOUNT, PRODUCT_SOLD)
```

You can read this formula as follows: Find the maximum transactions' sale amounts by product.

GROUPSUM

Syntax

GROUPSUM(Dimension to Group, Expression, Association 1, {Association 2 ...})

Description

Use the GROUPSUM function to sum information by group. Expression contains the data to sum. The Association(s) indicate what group(s) to sum by.

Example

The following examples provide uses of the GROUPSUM function:

Example 1

For example, suppose you want to sum employee salaries by department. Create an analytic model definition that contains the following data cubes:

EMPLOYEE_SALARY, which uses a dimension called EMPLOYEES.

This data cube contains the salary for each employee.
DEPARTMENT_SALARY, which uses a dimension called DEPARTMENTS.

This data cube contains the total salaries for each department.
An association data cube called EMPLOYEE_DEPT, which associates each employee with a particular department.

Calculate DEPARTMENT_SALARY with the following formula:

GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_DEPT)

You can read this formula as follows: Sum the employees' salaries by department.

Example 2

The GROUPSUM function can also perform more complex groupings. For example, suppose you want to sum employee salaries by branch and department. To do this, perform the following additional steps:

Create a dimension called BRANCHES, which contains a dimension of the branches.
Create an association data cube called EMPLOYEE_BRANCH, which associates the EMPLOYEES dimension with the BRANCHES dimension.
Define a data cube called SALARY_BY_BRANCH_AND_DEPT, which uses both the BRANCHES and DEPARTMENTS dimensions. Calculate this data cube with the following formula:
```
GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_BRANCH, EMPLOYEE_DEPT)
```

You can read this formula as follows: Sum the employees' salaries by branch and department.

As this example demonstrates, you can summarize detail information for a combination of dimensions by using an association for each dimension.

Example 3

In many cases, it is useful to summarize information by date. In these cases, use a data cube with a Date format instead of an association.

For example, suppose you want to summarize sales information by product and month. Create an analytic model definition that contains the following dimensions:

TRANSACTIONS, which contains a series of sales transactions.
PRODUCTS, which contains a dimension of products.
MONTHS, which contains a series of months.

Define the following data cubes:

SALE_AMOUNT, which uses the TRANSACTIONS dimension.

This data cube contains the amount of each sale.
An association data cube called PRODUCT_SOLD, which associates TRANSACTIONS with PRODUCTS.
SALE_DATE, which uses the TRANSACTIONS dimension and the YYYY/MM/DD format. This data cube contains the date for each transaction.
MONTHLY_SALES, which uses the PRODUCTS and MONTHS dimension. Calculate this data cube with the following formula:
```
GROUPSUM(TRANSACTIONS, SALE_AMOUNT, PRODUCT_SOLD, SALE_DATE)
```

You can read this formula as follows: Sum the transactions' sale amounts by product and sale date. Because SALE_DATE is Date formatted (YYYY/MM/DD), the GROUPSUM function knows to sum by date.

To calculate group sums of all members which meet a condition, use an IF function as the expression, with #N/A as the third argument. For example, use IF(Valid, SALE_AMOUNT, #N/A) instead of SALE_AMOUNT in the MONTHLY_SALES formula above.

GROW

Syntax

GROW(Dimension, Start Value, Growth Rate)

Description

The GROW function returns a number representing the specified Growth Rate per Member from Start Value. This is a straight line growth function.

Example

Suppose that an analytic model contains single value data cubes called SALES_START and ANNUAL_GROWTH. You can project the monthly sales with the following formula:

GROW(MONTHS, SALES_START, ANNUAL_GROWTH / 12)

Note that you must divide ANNUAL_GROWTH by 12, because the GROW function expects a growth rate per member, and the members in this case are months.

Note. For the GROW function to return meaningful results, the Start Value and Growth Rate arguments should not use the dimension indicated by the dimension argument. For example, if you are calculating monthly values, the Start Value and Growth Rate arguments should not use the MONTHS dimension.

IF

Syntax

IF(Condition, Result if True, Result if False)

Description

The IF function returns Result if True if Condition is true; otherwise, the function returns Result if False.

Returns

Result if True if Condition is true; otherwise, the function returns Result if False.

Example

For example, suppose a company awards its salespeople a 10 percent commission on sales of at least USD 20,000, and a 5 percent commission on sales under USD 20,000. You create a COMMISSION cube and can compute the commission for each person as follows:

IF(SALES >= 20000, 0.1 * SALES, 0.05 * SALES)

The IF function in this formula tests whether SALES is greater than or equal to 20000. If so, the function returns 10 percent of SALES; otherwise, the function returns 5 percent of SALES.

INC

Syntax

INC(Original Value, Amount to Increment)

Description

The INC function returns an incremented value based on an original value and the amount to increment.

Returns

An incremented value based on an original value and the amount to increment.

Example

INC(&NumMonths, &EndMonth - &StartMonth - 1)

This formula adds the months between the start and end month to the variable &NumMonths.

INCDATE

Syntax

INCDATE(Date, Months, Years)

Description

The INCDATE returns the value of Date incremented by Months and Years.

Returns

The value of Date incremented by Months and Years.

Example

If Date contains the date 2001/04/18, INCDATE(Date, 3, 2) returns the date 2003/07/18.

If Date falls on the last day of a month, INCDATE returns a date that falls on the last day of a month, even if it has to change the day. For example, if Date contains the date 2003/04/30, then INCDATE(Date, 3, 2) returns the date 2005/07/31 rather than 2005/07/30. Because Date contains the last day of April, INCDATE returns the last day of July.

Suppose that an analytic model contains a data cube called HIRE_DATE that uses a dimension called EMPLOYEES. Company policy starts benefits for an employee three months after the hire date. The following formula calculates the benefits date for each employee as follows:

INCDATE(HIRE_DATE, 3, 0)

See Also

IF.

INPUT

Syntax

INPUT()

Description

The INPUT function returns the value that an end user has entered into a cell, and supports both calculated cells and input cells in a single data cube.

Returns

The value that an end user has entered into a cell.

Example

You can use the INPUT function in both an IF function and a CASE function. For example:

IF([SCENARIOS:Actual], INPUT( ), FORECAST_REVENUE_CALCULATION)

This formula returns 88 if the Scenario value is Actual and the end user enters 88. This formula causes all cells for the Actual dimension member to become input cells, leaving the remaining cells to be calculated.

When a formula uses the INPUT function, the analytic calculation engine evaluates the formula for a particular cell to determine whether it should be an input cell. As long as the input condition in the formula refers to input cubes and member references, no recalculation is necessary to ensure that the correct cells are treated as input cells.

The INPUT function works a lot like the RETURN function; it causes the analytic calculation engine to stop evaluating the formula and to immediately return a value, which in this case is the current value of the cell. The INPUT function works like RETURN(SELF( )), and additionally makes the cell editable.

See Also

Input Data Cubes, ISINPUT.

INSUBTREE

Syntax

INSUBTREE(Dimension, Parent Member, {Child Member})

Description

The INSUBTREE function returns a boolean value identifying whether a child member is in a subtree that contains a parent member as its root.

Parameters

The INSUBTREE function takes two required arguments and one optional argument. The first and second arguments are required. The third argument is optional.

Dimension	The dimension to use.
Parent Member	The parent member to use.
Child Member	This optional argument tests whether the child member lies within a subtree that contains the parent member (as supplied in the second argument) as its root.

Example

INSUBTREE(Region, [Region:USA], [Region:Oakland]);

ISINPUT

Syntax

ISINPUT(Cube)

Description

The ISINPUT function returns True if the user has entered the current value of the cube.

Example

ISINPUT(Cube with no formula) returns True.

ISINPUT(Cube with formula) returns False.

The ISINPUT function provides an easy way to filter tables so that they show input cells. The ISINPUT function takes a single argument, which must be a cube.

To work well with filter functions, the function maps totals to the first member of the dimension if a first member exists. Because the row filters are not aware of members in the columns—and vice versa—the analytic calculation engine usually evaluates totals in formulas. The analytic calculation engine already bends the total mapping rules for filters for this reason; the behavior of ISINPUT is just an extension of this behavior.

INTERCEPT

Syntax

INTERCEPT(Dimension, Y, X, {Condition})

Description

The INTERCEPT function returns the Y-intercept of the line that has the closest fit to the points represented by Y and X. (The Y-intercept is the point at which the line crosses the Y axis.) If Condition is omitted, the function fits the line to all of the members in Dimension. If Condition is included, the function fits the line only to those members that meet the condition.

Use the INTERCEPT function together with the SLOPE function to find the line with the closest fit to a set of points. You can use these functions to analyze a historical trend, and then use the trend to make forecasts. For interesting examples of how to use these functions, see the entry for the SLOPE function.

Returns

The Y-intercept of the line that has the closest fit to the points represented by Y and X. (The Y-intercept is the point at which the line crosses the Y axis.) If Condition is omitted, the function fits the line to all of the members in Dimension. If Condition is included, the function fits the line only to those members that meet the condition.

See Also

SLOPE.

IRR

Syntax

IRR(Dimension, Cash Flow, {Guess}, {Condition})

Description

The IRR function returns the Internal Rate of Return for Cash Flow. Guess can be omitted (or zero) in most cases. If IRR is unable to find the Internal Rate of Return, it returns an error value. In such cases, you can use a nonzero Guess to nudge IRR toward the correct answer.

Note. Some cash flows have no valid Internal Rate of Return, in which case IRR returns an error value for any Guess.

If Condition is omitted, the function uses all values of Cash Flow. If Condition is included, the function uses only those values of Cash Flow for which Condition is True.

The initial values in the cash flow should be negative to represent a cash outflow. The remaining values may be all positive (representing cash inflows) or a combination of positive and negative.

Returns

The Internal Rate of Return for Cash Flow. Guess can be omitted (or zero) in most cases. If IRR is unable to find the Internal Rate of Return, it returns an error value. In such cases, you can use a nonzero Guess to nudge IRR toward the correct answer.

Example

You can calculate the internal rate of return for a data cube called IRR_OF_CASH_FLOW with this formula:

IRR(MONTHS, CASH_FLOW)

You can calculate the internal rate of return for the first 12 months for a data cube called RR_FOR_1ST_12_MONTHS with this formula:

RR(MONTHS, CASH_FLOW, 0, MEMBER(MONTHS) <= 12)

The Condition ensures that the IRR function uses only those values for which the month index is 12 or less.

LN

Syntax

LN(Data)

Description

The LN function returns the natural logarithm of Data. The value of Data must be greater than zero; otherwise, LN returns an error value.

Example

These examples employ the LN built-in function:

LN(7)returns 1.9459101491.
LN(E( ) ^ 5) returns 5.
LN(25) / LN(5) returns 2.
LN(-7)returns an error.

LEFT

Syntax

LEFT(Text, Count)

Description

The LEFT function returns the first Count characters of Text.

Returns

The first Count characters of Text.

Example

LEFT("StringFun", 6) returns String.

LEN

Syntax

LEN(Text)

Description

The LEN FUNCTION returns the number of characters in text string.

Returns

The number of characters in text string.

Example

LEN("StringFun") returns 9.

LOWER

Syntax

LOWER(Text)

Description

The LOWER FUNCTION returns text converted to lower case.

Returns

Text converted to lower case.

Example

LOWER("StringFun") returns stringfun.

MATCH

Syntax

MATCH(Text Expression or Text Cube, Pattern, {Case Sensitive}, {Match Type})

Description

The MATCH function returns True if Text Expression or Text Cube matches Pattern.

Pattern can be any of the following:

A text value in quotes (for example, "hello").
A data cube with a Text format.
The VALUE function, as in VALUE("Name").

If Case Sensitive is omitted or zero, the function ignores case. If Case Sensitive is nonzero, the function performs a case sensitive match.

Match Type can be one of these values:

0: Text contains Pattern.
1: Text matches Pattern exactly.
2: Text begins with Pattern.
3: Text ends with Pattern.

If the Match Type argument is omitted, it is assumed to be zero (text contains Pattern).

Returns

True if Text Expression or Text Cube matches Pattern.

Example

Suppose that Title = "A Quick Brown Fox" and Pattern = "brown." These results occur:

MATCH(Title, "A quick brown fox") returns True. (Case Sensitive argument is omitted, so the case does not have to match.)
MATCH(Title, "a quick brown fox", 1) returns False. (Case Sensitive argument is 1, and the case does not match.)
MATCH(Title, "A Quick Brown", 1, 1) returns False. (Match Type argument is 1, and the pattern does not match exactly.)
MATCH(Title, "brown") returns True. (Title contains Brown.)
MATCH(Title, Pattern) returns True. (Pattern equals brown, and Title contains the word Brown.)
MATCH(Title, "a quick", 0, 2) returns True. (Title begins with A Quick.)
MATCH(Title, "fox", 0, 2) returns False. (Title does not begin with fox.)
MATCH(Title, "fox", 0, 3) returns True. (Title ends with Fox.)
MATCH(LEFT(Title, 6), "Brown Fox", 1, 2) returns False (Title does not begin with Brown Fox.)
MATCH(MID(Title, 0, 7), "A Quick", 1) returns True (Title contains A Quick.)
MATCH(RIGHT(Title, 9), "Brown Fox", 1, 3) returns True (Title ends with Brown Fox.)

MAX

Syntax

MAX(arg1, arg2, . . . arg16)

Description

The MAX Function returns the maximum of a series of values. The MAX Function accepts up to 16 arguments.

Example

Given A = 4, B = 3, C = 2, D = 1

MAX(A, B, C, D) returns A.

You can sometimes simplify formulas by using the MAX function instead of the IF function. For example, suppose an analytic model contains data cubes called CASH_BALANCE and CASH_MINIMUM. You might be tempted to calculate the CASH_NEEDED cube by using the following formula:

IF(CASH_BALANCE < CASH_MINIMUM,CASH_MINIMUM - CASH_BALANCE, 0)

In other words, if CASH_BALANCE is less than CASH_MINIMUM, return the amount required to attain the minimum cash level; otherwise, return zero. Although the IF function does the job, it is simpler to use the MAX function:

MAX(CASH_MINIMUM - CASH_BALANCE, 0)

If CASH_BALANCE is greater than CASH_MINIMUM, the first argument is negative, so the formula returns zero for CASH_NEEDED. If CASH_BALANCE is less than CASH_MINIMUM, the first argument is positive, so the formula returns the amount required to attain the minimum cash level.

See Also

IF, MIN.

MBR2TEXT

Syntax

MBR2TEXT(Dimension, {Member})

Description

The MBR2TEXT function converts a member to text by returning its name. The Member argument can be an association data cube, a member reference, a member index, or a function or expression that returns a member. If Member is omitted, the function returns the name of the current member in Dimension. In other words, it is equivalent to MBR2TEXT(DIMENSION, MEMBER(DIMENSION)).

Example

MBR2TEXT(MONTH) returns January.

MEDIAN

Syntax

MEDIAN(Dimension, Values, {Condition})

Description

The MEDIAN function returns the median of Values. If Condition is omitted, the function uses all Values. If Condition is included, the function uses only those Values for which Condition is true.

Returns

The median of Values. If Condition is omitted, the function uses all Values. If Condition is included, the function uses only those Values for which Condition is true.

Example

Suppose that a cube collection contains a data cube called SALES that uses dimensions called PEOPLE and MONTHS. It also contains a data cube called MEDIAN_OF_SALES that contains the following formula for calculating the median over time for each person:

MEDIAN(MONTHS, SALES)

The cube collection also contains a data cube called MEDIAN_OF_SALES_IN_FIRST_6_MONTHS that contains this formula:

MEDIAN(MONTHS, SALES, MEMBER(MONTHS) <= 6)

MEMBER

Syntax

MEMBER(Dimension)

Description

The MEMBER function returns the Member being calculated.

Returns

The member being calculated.

Example

The following examples employ the MEMBER function:

Example 1

You can perform different calculations for different ranges of members by comparing the MEMBER function to a number. For example, to perform a special calculation for the first six months, use the MEMBER function with the IF function:

IF(MEMBER(MONTHS) <= 6, EXPR_FOR_1ST_6_MONTHS, EXPR_FOR_OTHER_MONTHS)

Example 2

You can perform special calculations for a particular member in a dimension by comparing the MEMBER function to a member reference. For example, suppose that your company allocates the Administration department's expense equally to all of the other departments. Your analytic model contains a DEPARTMENTS dimension, of which Admin is a member. Your analytic model also contains a data cube, TOTAL_EXPENSE, that contains the total expense for each department. The following formula describes how you would calculate the administration allocation for each department in a data cube called ADMIN_ALLOCATION:

IF(MEMBER(DEPARTMENTS) = [DEPARTMENTS:Admin], -TOTAL_EXPENSE, TOTAL_EXPENSE [DEPARTMENTS:Admin] / (NUMMEMBERS(DEPARTMENTS)
- 1))

The NUMMEMBERS function returns the number of members in a dimension. The key to this formula is that the allocation for each department is the same except for Admin. The IF function checks which department is being calculated. If the department is Admin, the result is minus Total Expense so that it backs out the expense for the Admin department. If the department is not Admin, the result is Total Expense for Admin—notice the data cube slice—divided by the number of departments other than Admin.

See Also

IF, NUMMEMBERS.

MID

Syntax

MID(Text, Start, {Count})

Description

The MID function returns Count characters from text, beginning with Start. If Count is omitted, returns all characters to the end of text.

Example

MID("StringFun", 6, 3) returns Fun.

MIN

Syntax

MIN(X, Y)

MIN(arg1, arg2, . . . arg16)

Description

The MIN function returns the minimum of a series of values. It accepts up to 16 arguments.

Example

Given A = 4, B = 3, C = 2, D = 1

MIN(A, B, C, D) returns D.

You can sometimes simplify formulas by using the MIN function instead of the IF function. For example, suppose that an analytic model contains data cubes called CASH_NEEDED, CREDIT_BALANCE, and MAX_CREDIT. You might be tempted to calculate the CREDIT_DRAW by using the following formula:

IF(CASH_NEEDED <= MAX_CREDIT - CREDIT_BALANCE, CASH_NEEDED, MAX_CREDIT - CREDIT_BALANCE)

In other words, if CASH_NEEDED is less than or equal to the available credit, draw the full CASH_NEEDED; otherwise, draw the available credit. Although the IF function does the job, the MIN function is simpler:

MIN(CASH_NEEDED, MAX_CREDIT - CREDIT_BALANCE)

If CASH_NEEDED is less than the available credit, the formula returns CASH_NEEDED; otherwise, the formula returns the available credit.

See Also

IF, MAX.

MOD

Syntax

MOD(X, Y)

Description

The MOD function returns the remainder of X divided by Y. If Y is zero, MOD returns an error value.

Returns

The remainder of X divided by Y. If Y is zero, MOD returns an error value.

Example

The following examples employ the MOD built-in function:

MOD(10, 4) returns 2.
MOD(15, 10) returns 5.
MOD(15, 5) returns 0.
MOD(15, 0) returns an error value.

MONTH

Syntax

MONTH({Date})

Description

The MONTH function returns the month of Date. If Date is omitted, the function returns the month of the calculation date.

Returns

The month of Date. If Date is omitted, the function returns the month of the calculation date.

Example

If A = 2004/03/15 and B = 2005/06/22, MONTH(A) returns 3 and MONTH(B) returns 6.

Now suppose that an analytic model contains a data cube called MONTH_EXAMPLE that uses a dimension called MONTHS and has the formula MONTH_EXAMPLE = MONTH( ). Because the argument is omitted, MONTH returns the month for each date in the MONTHS dimension.

Following is a useful example of the MONTH function. Suppose that you define a data cube called MONTHLY_SALES that uses a dimension called MONTHS. You want to calculate the average sales for each month of the year. In other words, you want to know the average sales for the first month of each year, the average sales for the second month of each year, and so on. To do this, create a dimension called MONTH_NUM that contains members numbered 1 to 12. Then define a data cube called AVG_SALES_BY_MONTH that uses the MONTH_NUM dimension. Finally, enter the following formula for the AVG_SALES_BY_MONTH cube:

DAVG(MONTHS, MONTHLY_SALES, MONTH( ) = MEMBER(MONTH_NUM))

See the entries for DAVG and MEMBER if you are unfamiliar with these functions. For each MONTH_NUM member in AVG_SALES_BY_MONTH, the formula averages all Monthly Sales for which the month of the year equals the index of the MONTH_NUM member. Thus, if the analytic calculation engine calculates the fifth MONTH_NUM member for AVG_SALES_BY_MONTH, it averages the sales for the dates 2004/05/03, 2004/05/04, and 2004/05/05, because these are the dates for which the MONTH( ) function returns 5.

See Also

DAVG, MEMBER.

Syntax

NEXT(Dimension, Data, {Count})

Description

The NEXT function returns the value of Data from Count members forward in Dimension. If Count is omitted, it is assumed to be 1.

Note. The NEXT function operates on detail member names that are persisted in the main record. This function does not use trees to determine the order of members.

Returns

The value of Data from Count members forward in Dimension. If Count is omitted, it is assumed to be 1.

Example

To refer to the next month's sales in a rule, use NEXT(MONTHS, SALES).

The NEXT function can be used together with the CUMAVG function to calculate a centered moving average , such as the average sales for the six months before and after a given month. The centered moving average gives a sense of the normal monthly value for the year surrounding a particular month. You can then compare the actual monthly value to the normal monthly value to see how seasonality affected the sales. Thus, if the actual monthly value for August is higher than the normal monthly value for the year surrounding August, this may indicate that sales tend to be higher than average in August.

Suppose that the actual monthly sales are stored in a data cube called ACTUAL_SALES. Calculate the CENTERED_AVG_SALES cube as follows:

NEXT(MONTHS, CUMAVG(MONTHS, ACTUAL_SALES, 13), 6)

This formula looks six months ahead (NEXT(MONTHS, ..., 6)), and then calculates the cumulative average of the 13 months of sales preceding that time (CUMAVG(MONTHS, ACTUAL_SALES, 13)). For example, when the analytic calculation engine calculates CENTERED_AVG_SALES for 2005/03, it looks ahead six months to 2005/09, and then calculates the average sales for the 13 months preceding 2005/09. Thus, the analytic calculation engine calculates the average sales for 2004/09 to 2005/09, which is the year surrounding 2005/03.

Actually, this formula is not quite complete. You cannot calculate accurate results for the first six months or the last six months of the analytic model because the analytic calculation engine is unable to look six months back and six months ahead during those months. Therefore, the formula should return zero for those months:

IF(MEMBER(MONTHS) > 6 .AND. MEMBER(MONTHS) <= NUMMEMBERS(MONTHS) - 6, NEXT(MONTHS, CUMAVG(MONTHS, ACTUAL_SALES, 13), 6), 0)

The condition of the IF statement ensures that the month being calculated is after the first six months and before the last six months of the analytic model. If the condition is true, the IF function returns the centered moving average calculated by the second argument; otherwise, the IF function returns zero.

See Also

DAVG, MEMBER, NUMMEMBERS.

NPER

Syntax

NPER(Rate, Pmt, PV, FV, {Type})

Description

The NPER function returns the number of payment periods required to accumulate a future value of FV when the present value is PV, the payment is Pmt, and the rate is Rate. If Type is omitted or zero, NPER assumes that the investment is an ordinary annuity. If Type is nonzero, NPER assumes that the investment is an annuity due.

Note. Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Example

Suppose that you deposit 1,000 USD at the end of each year in a savings account that earns 6 percent per year. To determine how many years it takes before the account is worth 20,000 USD , use the following formula for the YEARS_REQUIRED cube:

NPER(0.06, -1000, 0, 20000) = 13.53

Note. The decimal part of the answer is not particularly meaningful; you cannot be sure of getting the 20,000 USD until the end of the 14th year.

If you deposit the 1,000 USD at the start of each year, use the following formula. The 1 for the Type argument indicates an annuity due:

NPER(0.06, -1000, 0, 20000, 1) = 12.99

If the account already has 5,000 USD in it at the start, use the following formula:

NPER(0.06, -1000, -5000, 20000, 1) = 8.72

NPV

Syntax

NPV(Dimension, Rate, Cash Flow, {Type}, {Condition})

Description

The NPV function returns the Net Present Value for Cash Flow, where Rate is the rate per period. If Type is zero or omitted, NPV treats the investment as an ordinary annuity; otherwise, NPV treats the investment as an annuity due. If Condition is omitted, the function uses all values of Cash Flow. If Condition is included, the function uses only those values of Cash Flow for which Condition is true.

The initial values in the cash flow should be negative to represent a cash outflow. The remaining values may be all positive (representing cash inflows), or a combination of positive and negative values.

Example

You can create a data cube called NET_PRESENT_VALUE and calculate the net present value for a data cube called CASH_FLOW with the following formula:

NPV(MONTHS, ANNUAL_RATE / 12, CASH_FLOW)

You can calculate the net present value for the first 12 months with the following formula:

NPV(MONTHS, ANNUAL_RATE / 12, CASH_FLOW, 0, MEMBER(MONTHS) <= 12)

The Condition ensures that the NPV function uses only those values for which the month index is 12 or less.

See Also

MEMBER.

NUM2TEXT

Syntax

NUM2TEXT(Number, {Decimal Places})

Description

The NUM2TEXT function converts Number to Text. Decimal Places specifies the number of decimal places that are used to convert the number to text.

Example

NUM2TEXT(SALESPRICE, 3) for SALESPRICE's value of 10.23457 as the string 10.234.

NUMMEMBERS

Syntax

NUMMEMBERS(Dimension)

Description

The NUMMEMBERS function returns the number of members in Dimension.

Returns

Returns the number of members in Dimension.

Example

If a dimension called PRODUCTS contains eight members; NUMMEMBERS(Products) returns 8.

OPRID

Syntax

OPRID()

Description

The OPRID function returns the userID of the user who currently has the analytic instance checked out.

Use the OPRID function within a filter user function whose purpose is to limit userID access to only certain rows of data.

Example

IF(AT(USERID, TXT2MBR(USERID, OPERID()), DEPT_CUBE) =  MEMBER(DEPT_DIM),RETURN(1), RETURN(0))

This filter user function restricts user access to bonus amount data. Each userID has access to only the bonus amount that pertains to them. The filter user function contains these data cubes and dimensions:

USERID dimension, which is mapped to the USERID field.

The USERID field contains the userIDs of the users that currently have the analytic instance loaded.
DEPT_CUBE data cube, which is mapped to the DEPT_CUBE field.

This data cube is formatted as a member of the DEPT_DIM dimension.
DEPT_DIM dimension, which is mapped to the DEPT_DIM field.

Note. The filter user function is applied to this dimension.
BONUS_AMT data cube, which is mapped to the BONUS_AMT field.

These are the values of the fields that are mapped to the USERID dimension and DEPT_CUBE data cube:

USERID	DEPT_CUBE
Juan	Doc
Albert	Dev
Nigel	PM

These are the values of the fields that are mapped to the DEPT_DIM dimension and BONUS_AMT data cube:

DEPT_DIM	BONUS_AMT
Dev	5000
Doc	4000
PM	7000

The analytic calculation engine performs these steps to calculate the filter user function:

The OPRID function returns the userID of the current user in text format.
The TXT2MBR function compares the userID with the member in the USERID dimension to determine if they match.

If the userID matches the member in the USERID dimension, the AT function searches for the coordinates of the userID member that is returned by TXT2MBR and returns the corresponding value of DEPT_CUBE.

On the right-hand side of the equation, the MEMBER function returns the corresponding member of DEPT_DIM.
The analytic calculation performs one of these actions:
- If the value returned from DEPT_CUBE matches the member returned from DEPT_DIM, the userID can see the bonus amount.
  
  For example, the Dev value returned from DEPT_CUBE matches the Dev member returned from DEPT_DIM. For this reason, Albert can see his bonus amount of 5000.
- If the value returned from DEPT_CUBE does not match the member returned from DEPT_DIM, the userID cannot see the bonus amount.

PARENT

Syntax

PARENT(Dimension, {Child Member})

Description

The PARENT function returns the member reference to the parent of the specified member.

Parameters

The PARENT function takes one required argument and one optional argument. The first argument is required. The second argument is optional.

Dimension

The dimension to use.

Child Member

If this optional argument is not supplied, use the current calculated member for this dimension.

Note. If the child member is the root, this function returns –1.

Returns

The member reference to the parent of the specified member.

Example

PARENT (Region, [Region:West]) returns a reference to the parent of [Region:West], which is [Region:USA].

See Also

Pushed Down Data.

PCT

Syntax

PCT(Dimension, Data, {Count})

Description

The PCT function returns the percentage change between the value of Data for the Member being calculated and the value of Data for Count members back. If Count is omitted, it is assumed to be 1.

Returns

The percentage change between the value of Data for the Member being calculated and the value of Data for Count members back. If Count is omitted, it is assumed to be 1.

Example

Suppose that you wish to calculate the monthly and yearly percentage change in a data cube called SALES. If SALES uses a dimension called MONTHS, use the following formula:

PCT(MONTHS, SALES)

Because the Count argument is omitted, the program assumes it to be 1. Thus, the program calculates the percentage change in sales from the previous month to the month being calculated. Calculate the YEARLY_PERCENT_CHANGE cube by using 12 for the third argument:

PCT(MONTHS, SALES, 12)

This formula calculates the percentage change in SALES from 12 months ago to the month being calculated.

PERCENTILE

Syntax

PERCENTILE(Dimension, Values, Percentile, {Type}, {Condition})

Description

The PERCENTILE function returns a percentile of Values. The Percentile argument sets which percentile is calculated. If Type is zero or omitted, PERCENTILE calculates a population percentile; otherwise, PERCENTILE calculates a sample percentile. If Condition is omitted, the function uses all Values. If Condition is included, the function uses only those Values for whichCondition is true.

Example

Suppose that an analytic model contains a data cube called SCORES that uses dimensions called STUDENTS and TESTS.

The following formula calculates the 90th percentile of the scores for each test:

PERCENTILE(STUDENTS, SCORES, 90%)

The following formula calculates the 50th percentile of the first 10 students:

PERCENTILE(STUDENTS, SCORES, 50%, MEMBER(STUDENTS) <= 10)

This formula calculates the 50th percentile (also knows as median) of the first 10 students for each test.

PI

Syntax

PI( )

Description

The PI function returns the value of PI (3.1415926536), the ratio of a circle's circumference to its diameter.

The following formula calculates the area of a circle:

PI( ) * RADIUS ^ 2

Returns

The value of PI (3.1415926536), the ratio of a circle's circumference to its diameter.

Example

The following examples employ the PI function:

Example 1

PI( ) * 7 = 21.99 (circumference of a circle with a diameter of 7).

Example 2

PI( ) * 36 ^ 2 = 4071.50 (area of a circle with a radius of 36).

PMT

Syntax

PMT(Rate, NPer, PV, FV, {Type})

Description

The PMT function returns the payment required to repay a loan of PV, at an interest rate of Rate, where there are NPer payments and an ending balance of FV. If Type is omitted or zero, PMT assumes that the loan is an ordinary annuity. If Type is nonzero, PMT assumes that the loan is an annuity due.

Note. Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Returns

The payment required to repay a loan of PV, at an interest rate of Rate, where there are NPer payments and an ending balance of FV. If Type is omitted or zero, PMT assumes that the loan is an ordinary annuity. If Type is nonzero, PMT assumes that the loan is an annuity due.

Note. Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Example

If you take out a loan for 50,000 USD at a rate of 14 percent per year and 120 monthly payments, you can create a PAYMENT cube and compute the payment required to repay the loan as follows:

PMT(0.14 / 12, 120, 50000, 0) = -776.33

If the loan has a balloon payment of 30,000 USD at the end of the 120 months, compute the payment as follows:

PMT(0.14 / 12, 120, 50000, -30000) = -660.53

If the payments are made at the start of the month rather than the end of the month, use the following formula:

PMT(0.14 / 12, 120, 50000, -30000, 1) = -652.92

Syntax

PREV(Dimension, Data, {Count})

Description

The PREV function returns the value of Data from Count members back in Dimension. If Count is omitted, it is assumed to be 1.

Note. The PREV function operates on detail member names that are persisted in the main record. This function does not use trees to determine the order of members.

Returns

The PREV function returns the value of Data from Count members back in Dimension. If Count is omitted, it is assumed to be 1.

Example

To refer to the previous month's sales in a formula, use PREV(MONTHS, SALES).

Suppose that you want to forecast the total monthly receipts for a company, assuming that some of each month's sales are received immediately, some are received in one month, some are received in two months, and some are received in three months. First, define data cubes that contain the estimated percentage of sales received for each time period: PCT_RECV_IMMEDIATELY, PCT_RECV_IN_1_MONTH, PCT_RECV_IN_2_MONTHS, PCT_RECV_IN_3_MONTHS. Next, define a monthly data cube called SALES that contains the sales forecast for each month. Calculate the TOTAL_MONTHLY_RECEIPTS data cube with these formulas:

RECV_IMMEDIATELY data cube formula:
```
PCT_RECV_IMMEDIATELY * SALES
```

RECV_IN_1_MONTH data cube formula:

PCT_RECV_IN_1_MONTH * PREV(MONTHS, SALES)

RECV_IN_2_MONTHS data cube formula:

PCT_RECV_IN_2_MONTHS * PREV(MONTHS, SALES, 2)

RECV_IN_3_MONTHS data cube formula:

PCT_RECV_IN_2_MONTHS * PREV(MONTHS, SALES, 3)

TOTAL_MONTHLY_RECEIPTS data cube formula:

RECV_IMMEDIATELY + RECV_IN_1_MONTH + RECV_IN_2_MONTHS + RECV_IN_3_MONTHS

RECV_IMMEDIATELY contains the amount received from the current month's sales, RECV_IN_1_MONTH contains the amount received from the previous month's sales, and so on. Add all of these amounts together to calculate the total receipts for the month.

PREVSELF

Syntax

PREVSELF(Dimension, {Start Value}, {Count})

Description

The PREVSELF function returns the value of the current data cube from Count members back in Dimension. When the program is calculating the first Count members of Dimension, PREVSELF returns Start Value. If Start Value is omitted, it is assumed to be 0. If Count is omitted, it is assumed to be 1.

Note. The PREVSELF function operates on detail member names that are persisted in the main record. This function does not use trees to determine the order of members.

Returns

Example

Suppose that you want to forecast sales. For each month, you want to add an estimated Sales Growth to the previous month's sales. When calculating the first month, you want to add sales growth to starting sales. You can do this with the following formula for the SALES cube:

PREVSELF(MONTHS, STARTING_SALES) + SALES_GROWTH

For the first month, this formula returns the starting sales plus sales growth. For every other month, the formula returns the previous month's sales plus sales growth.

The PREVSELF function is useful for keeping a running balance of transactions. For example, suppose that an analytic model contains monthly data cubes called DEPOSITS, WITHDRAWALS, and BALANCE, and a single value data cube called START_BALANCE. You can calculate the BALANCE cube with the following formula:

PREVSELF(MONTHS, START_BALANCE) + DEPOSITS - WITHDRAWALS

This formula calculates the ending balance for each month by adding DEPOSITS and subtracting WITHDRAWALS from the ending balance for the previous month. Because no previous balance is available for the first month, the PREVSELF function returns the value of Start Balance.

PV

Syntax

PV(Rate, NPer, Pmt, FV, {Type})

Description

The PV function returns the Present Value of an investment with a future value of FV, where Pmt is received for NPer periods and is discounted at the rate of Rate per period. If Type is omitted or zero, PV assumes that the investment is an ordinary annuity. If Type is nonzero, PV assumes that the investment is an annuity due.

Note. Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Example

Suppose that a machine that sells for 80,000 USD saves your company 11,000 USD a year for 10 years. Assuming that the money saved could be invested at 8 percent per year, you can calculate the PRESENT_VALUE cube as follows:

PV(0.08, 10, 11000, 0) = -73811

The present value of the machine is 73,811 USD, indicating that you might be better off investing the 80,000 USD in another way. But suppose that you can sell the machine for 30,000 USD at the end of the 10 years. You can calculate the PRESENT_VALUE cube as follows:

PV(0.08, 10, 11000, 30000) = -87707

In this case, the present value is higher than the cost of the machine, indicating a profitable investment.

QUARTILE

Syntax

QUARTILE(Dimension, Values, Quartile, {Type}, {Condition})

Description

The QUARTILE function returns a quartile of Values.

The Quartile argument sets which quartile (0, 1 ,2 , 3, or 4) is calculated. If Type is zero or omitted, QUARTILE calculates a population quartile; otherwise, QUARTILE calculates a sample quartile. If Condition is omitted, the function uses all Values. If Condition is included, the function uses only those Values for which Condition is true.

Returns

The QUARTILE function returns a quartile of Values. The Quartile argument sets the quartile (0, 1 ,2 , 3, or 4) that is calculated. If Type is zero or omitted, QUARTILE calculates a population quartile; otherwise, QUARTILE calculates a sample quartile. If Condition is omitted, the function uses all Values. If Condition is included, the function uses only those Values for which Condition is true.

Example

For example, suppose that an analytic model contains a data cube called SCORES that uses dimensions called STUDENTS and TESTS.

The following formula calculates the third quartile of the scores for each test.

QUARTILE(STUDENTS, SCORES, 3)

The following formula calculates the second quartile (also known as the median) of the first ten students:

QUARTILE(STUDENTS, SCORES, 2, MEMBER(STUDENTS) <= 10)

RAND

Syntax

RAND()

Description

The RAND function returns a random decimal number greater than or equal to zero and less than one.

The RAND function uses an industrial strength random number generator with an extremely long period. Thus, it is suitable for use in statistical simulation.

The PeopleSoft Analytic Calculation Engine RAND function does not cause a cube to be calculated during every recalculation, unlike in Microsoft Excel. Formulas that use RAND typically refer to some other data in the analytic model, and the analytic calculation engine recalculates the cube only when the other data changes. If you use the RAND function to populate a cube with data for demos or testing — and you do not refer to other cubes — the data in the cube does not change unless you edit the formula or calculate the data cube.

Returns

A random decimal number greater than or equal to zero and less than one.

Example

RAND() returns 0.938119738.

RATE

Syntax

RATE(NPer, Pmt, PV, FV, {Type})

Description

The RATE function returns the rate required to accumulate a future value of FV when the present value is PV, the number of periods is NPer, and the payment is Pmt. If Type is omitted or zero, RATE assumes that the investment is an ordinary annuity. If Type is nonzero, RATE assumes that the investment is an annuity due.

Note. Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Returns

Note. Enter negative amounts for money out of your pocket, or positive amounts for money coming to you.

Example

Suppose that you wish to invest 5,000 USD at the end of each year for 10 years. You can create a data cube called RATE_REQUIRED and calculate the rate of return required to earn 100,000 USD as follows:

RATE(10, -5000, 0, 100000) = 14.69%

Now suppose that you initially invest 15,000 USD in addition to the yearly payments. Use the following formula:

RATE(10, -5000, -15000, 100000) = 7.23%

Finally, suppose that you make the payments at the start of the year. You can use the following formula:

RATE(10, -5000, -15000, 100000, 1) = 6.50%

REPLACE

Syntax

REPLACE (Text, Old, New)

Description

The REPLACE function replaces all occurrences of Old with New in text and returns the result.

Example

REPLACE("StringFun", "Fun", "Number") returns StringNumber.

RETURN

Description

The RETURN function stops the evaluation of a rule and returns the value of the RETURN function's argument.

Example

WHILE(&Balance < TARGET_BALANCE,
	IF(&Month > NUMMEMBERS(MONTHS), RETURN(#N/A));
	INC(&Month);
	INC(&Balance, AT(MONTHS, &Month, CASH_FLOW))
);
RETURN(&Month)

This formula calculates the number of months required to accumulate a target balance, but returns an error value if the maximum number of months is exceeded. This makes it unnecessary to repeat the condition at the end of the formula.

Note. The RETURN at the end of the formula is not necessary; however, you can use it for clarity.

RIGHT

Description

The RIGHT function returns the right most Count characters of Text.

Returns

The right most Count characters of Text.

Example

RIGHT("StringFun", 3) returns Fun.

ROUND

Syntax

ROUND(Data, Integer)

Note. The Integer argument is optional.

Description

The ROUND function—when you use only the first argument—returns the value of Data rounded to the nearest whole number. If you use the optional second argument, the ROUND function returns the value of Data rounded to the number of decimal places that you specify with the Integer argument.

The default value for the Integer argument is zero.

Example

The following examples employ the ROUND built-in function:

ROUND(14) returns 14.
ROUND(14.3) returns 14.
ROUND(14, 0) returns 14.
ROUND(14.3, 0) returns 14.
ROUND(14.5, 0) returns 15.
ROUND(14.7, 0) returns 15.
ROUND(34.56789, 4) returns 34.5679.

SELF

Syntax

SELF( )

Description

The SELF function returns the current value of the data cube that is being calculated. The SELF function recalculates the data cube only if a certain condition is true; otherwise, the data cube retains its current value.

Returns

The current value of the data cube being calculated.

Example

Suppose that you would like to update your sales forecast on a monthly basis, but you also would like to save the original forecast. If the current forecast is stored in a data cube called SALES_FORECAST, you can calculate the ORIGINAL_SALES_FORECAST cube as follows:

IF(UPDATE_ORIGINAL, SALES_FORECAST, SELF( ))

(See the entry for the IF built-in function if you are unfamiliar with this function.) UPDATE_ORIGINAL is a single value data cube that contains either a true or false value. If UPDATE_ORIGINAL is false, the SELF function returns the current value of ORIGINAL_SALES_FORECAST, thereby leaving the original forecast unchanged. If UPDATE_ORIGINAL is true, the IF function returns the value of SALES_FORECAST, thereby updating the original forecast.

See Also

IF.

SET

Syntax

SET(Variable, Expression)

Note. The second argument of the SET function can be any valid expression.

Description

The SET function sets a value to a variable.

Example

The following formula sets the &Index variable to 1.

SET(&Index, 1)

SIN

Syntax

SIN(Data)

Description

The SIN function returns the sine of Data, where Data represents an angle in radians.

To convert from degrees to radians, multiply by PI( ) / 180. (The PI function returns the value of PI.)

Returns

The sine of Data, where Data represents an angle in radians.

Example

The following examples employ the SIN built-in function:

SIN(PI( ) / 6) returns 0.5 (sine of PI / 6 radians).
SIN(PI( ) / 2) returns 1 (sine of PI / 2 radians).
SIN(45 * PI( ) / 180) returns 0.7071067812 (sine of 45 degrees).

SLN

Syntax

SLN(Cost, Salvage, Life)

Description

The SLN function returns the depreciation on an asset by using the straight line method, which is a single programming statement. This function returns the same depreciation for each period.

Parameters

Cost	The cost of the asset.
Salvage	The worth of the asset at the end of its useful life.
Life	The number of periods in the asset's useful life.

Returns

The depreciation on an asset using the Straight Line method. This function returns the same depreciation for each period.

Example

Suppose that you purchase a machine for 6,000 USD, and you plan to sell it for 500 USD after five years. You can calculate the depreciation for each year as follows:

SLN(6000, 500, 5) = 1100

SLOPE

Syntax

SLOPE(Dimension, Y, X, {Condition})

Description

The SLOPE function returns the slope of the line that has the closest fit to the points represented by Y and X. (The slope is the change in Y divided by the change in X.) If Condition is omitted, the function fits the line to all of the members in Dimension. If Condition is included, the function fits the line only to those members that meet the condition.

Use the SLOPE function together with the INTERCEPT function to find the line with the closest fit to a set of points. You can use these functions to analyze a historical trend, and then use the trend to make forecasts. You can also use these functions to analyze the relationship between different variables, such as sales and travel expense.

Analyzing a Historical Trend

To analyze a historical trend:

Calculate the slope for the trend line with this formula for the TREND_SLOPE cube:
```
SLOPE(DATE_DIMENSION, HISTORICAL_DATA, MEMBER(DATE_DIMENSION), MEMBER(MONTHS) <= LAST_ACTUAL_DATE)
```
HISTORICAL_DATA is the data cube that you want to analyze. DATE_DIMENSION is the dimension used by the data cube, which is normally a date dimension. Because you want to know how HISTORICAL_DATA is affected by time, use the date index—MEMBER(DATE_DIMENSION)—as the independent (X) argument. LAST_ACTUAL_DATE is a data cube containing the last date that you want to analyze. If you want to analyze all of the dates in DATE_DIMENSION, you may omit the condition.

See MEMBER.

Calculate the intercept for the trend line with the following formula for the TREND_START cube:

INTERCEPT(DATE_DIMENSION, HISTORICAL_DATA, MEMBER(DATE_DIMENSION), MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

You can now calculate the values for the trend line with the following formula for the TREND_VALUES cube:
```
TREND_START + TREND_SLOPE * MEMBER(DATE_DIMENSION)
```

Analyzing the Relationship Between Two Data Cubes

To analyze the relationship between two data cubes:

Calculate the slope for the relationship line with this formula for the RELATION_SLOPE cube:
```
SLOPE(DIMENSION, DEPENDENT_VARIABLE, INDEPENDENT_VARIABLE)
```
DEPENDENT_VARIABLE is the variable whose values are influenced by INDEPENDENT_VARIABLE. For example, if you want to know how sales are influenced by advertising, SALES is the dependent variable and ADVERTISING is the independent variable. If necessary, you may restrict the analysis to selected members of DIMENSION by using a condition for the fourth argument.
Calculate the intercept for the relationship line with this formula for the RELATION_START cube:
```
INTERCEPT(DIMENSION, DEPENDENT_VARIABLE, INDEPENDENT_VARIABLE)
```
If you included a condition in the formula for RELATION_SLOPE, be sure to include it in this formula as well.
Given an independent variable, you can now estimate a corresponding dependent value with this formula for the DEPENDENT_VALUE cube:
```
RELATION_START + INDEPENDENT_VALUE * RELATION_SLOPE
```

Returns

The slope of the line that has the closest fit to the points represented by Y and X. (The slope is the change in Y divided by the change in X.) If Condition is omitted, the function fits the line to all of the members in Dimension. If Condition is included, the function fits the line only to those members that meet the condition.

Example

The following sections provide examples of analyzing a historical trend and analyzing a relationship between data cubes.

Example 1: Analyzing a Historical Trend

Suppose that you want to analyze the trend in historical sales to forecast future sales. The historical sales are stored in a data cube called ACTUAL_SALES that uses a dimension called MONTHS. The date of the last actual value is stored in a data cube called LAST_ACTUAL_DATE. Calculate the sales trend with the following formulas:

TREND_SLOPE data cube formula:

SLOPE(MONTHS, ACTUAL_SALES, MEMBER(MONTHS), MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

TREND_START data cube formula:

INTERCEPT(MONTHS, ACTUAL_SALES, MEMBER(MONTHS), MEMBER(MONTHS) <= LAST_ACTUAL-DATE)

SALES_TREND data cube formula:
```
TREND_START + TREND_SLOPE * MEMBER(MONTHS)
```
Note. TREND_SLOPE and TREND_START do not use the MONTHS dimension.

Example 2: Analyzing the Relationship Between Data Cubes

Suppose that you want to analyze how UNITS_SOLD has affected SUPPORT_COSTS. Both of these data cubes use a dimension called MONTHS. The date of the last actual value is stored in a data cube called LAST_ACTUAL_DATE. Enter the estimates for future unit sales in a data cube called SALES_FORECAST, and then calculate the resulting SUPPORT_FORECAST data cube as follows:

RELATION_SLOPE data cube formula:

SLOPE(MONTHS, SUPPORT_COSTS, UNITS_SOLD, MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

RELATION_START data cube formula:

INTERCEPT(MONTHS, SUPPORT_COSTS, UNITS_SOLD, MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

SUPPORT_FORECAST data cube formula:

IF(DATE( ) > LAST_ACTUAL_DATE, RELATION_START + SALES_FORECAST * RELATION_SLOPE, 0)

Notice that this example uses a different approach than the previous example. In the first example, you analyzed how sales were affected by time, and then used the results to predict future sales based on the passage of time. In this example, you analyzed how support was affected by sales, and then used the results to predict future support costs based on future sales.

SQRT

Syntax

SQRT(Data)

Description

The SQRT function returns the square root of Data. If the value of Data is negative, SQRT returns an error value.

Returns

The square root of Data. If the value of Data is negative, SQRT returns an error value.

Example

These examples employ the SQRT built-in function:

SQRT(25) returns 5.
SQRT(2) returns 1.4142135624.
SQRT(-25) returns an error value.

STDEV

Syntax

STDEV(Dimension, Values, {Type}, {Condition})

Description

The STDEV function returns the standard deviation of Values. If Type is zero or omitted, STDEV calculates a population standard deviation; otherwise, STDEV calculates a sample standard deviation. If Condition is omitted, the function uses all Values. If Condition is included, the function uses only those Values where Condition is true.

Example

Suppose that an analytic model contains a data cube called SALES that uses dimensions called PEOPLE and MONTHS.

Use this formula to calculate the standard deviation over time for each person:

STDEV(MONTHS, SALES, 0)

Use this formula to calculate the standard deviation of sales over 5000 for each month:

STDEV(PEOPLE, SALES, 0, SALES > 5000)

SYD

Syntax

SYD(Cost, Salvage, Life, Period)

Description

The SYD function returns the depreciation on an asset using the Sum-of-the-Years'-Digits method, an accelerated depreciation method. The SYD function takes these arguments:

Parameters

Cost	The cost of the asset.
Salvage	The worth of the asset at the end of its useful life.
Life	The number of periods in the asset's useful life.
Period	The period for which you wish to determine the depreciation.

Example

Suppose that you purchase a machine for 6,000 USD , and you plan to sell it for 500 USD after five years. You can calculate the depreciation for each year as follows:

SYD(6000, 500, 5, 1) = 1833
SYD(6000, 500, 5, 2) = 1467
SYD(6000, 500, 5, 3) = 1100
SYD(6000, 500, 5, 4) = 733
SYD(6000, 500, 5, 5) = 367

TAN

Syntax

TAN(Data)

Description

The TAN function returns the tangent of Data, where Data represents an angle in radians.

To convert from degrees to radians, multiply by PI( ) / 180. (The PI function returns the value of PI.)

Returns

Returns the tangent of Data, where Data represents an angle in radians.

Example

These examples employ the TAN built-in function:

TAN(PI( )) returns 0 (tangent of p radians).
TAN(PI( ) / 4) returns 1 (tangent of p / 4 radians).
TAN(45 * PI( ) / 180) returns 1 (tangent of 45 degrees).

See Also

PI.

TEXT2MBR

Syntax

TEXT2MBR(Dimension, Text)

Description

Converts text to the member with that name in Dimension. If there is no member with that name, returns 0.

Note. This is essentially a linear lookup function, so be careful when using it with dimensions that have a lot of members.

Example

TEXT2MBR(MONTHS, "January") returns a new member, January, in the MONTHS dimension.

TEXT2NUM

Syntax

TEXT2NUM (Text)

Description

Converts Text to a number. This performs a forgiving conversion. For example, dollar signs and commas are ignored, parentheses or a minus sign make the number negative, and % causes the number to be converted as a percentage. If there is no number in the text, the function returns 0.

Example

These examples employ the TEXT2NUM built-in function:

TEXT2NUM("TEN") returns 10.
TEXT2NUM("$TEN") returns 10.
TEXT2NUM("-TEN") returns -10.
TEXT2NUM("100,000") returns 100000.
TEXT2NUM("10%") returns 10%.

THIS

Syntax

THIS(Expression)

Description

The THIS function returns the value of Expression for the members being calculated, even if Expression is used in a database function. This function enables you to perform complex calculations that relate other members in a dimension to the member being calculated.

To understand the THIS function, you need to understand how database functions work. A database function scans the members in a dimension to calculate a result. For example, suppose that you define the following formula:

DAVG(PRODUCTS, ADVERTISING, SALES > 50000)

This formula calculates the average advertising for products with sales over 50,000 USD. The DAVG function scans the PRODUCTS dimension and evaluates the condition SALES > 50000 for each product. If the condition is true, the DAVG function includes the Advertising for that product in the average. The important point here is that the DAVG function evaluates the condition (SALES > 50000) and the expression (Advertising) for the product being scanned.

Now suppose that you want to calculate the following result for each product: the average advertising for products whose sales are greater than the product being calculated.

Create a data cube called AVG_ADVERTISING_FOR_BETTER_PERFORMERS that uses the PRODUCTS dimension. Its formula should look similar to:

DAVG(PRODUCTS, ADVERTISING, SALES > "Sales for the product being calculated")

To get the sales for the product being calculated, remember that the DAVG function uses the sales for the product being scanned. The solution is to use the THIS function:

DAVG(PRODUCTS, ADVERTISING, SALES > THIS(SALES))

The THIS function forces the DAVG function to use the sales for the product being calculated. Thus, the condition compares the sales for the product being scanned to the sales for the product being calculated. If the condition is true, the DAVG function includes the Advertising for the product being scanned.

Returns

The value of Expression for the members being calculated, even if Expression is used in a database function.

THISCUBE

Syntax

THISCUBE()

Description

The THISCUBE function returns a reference to the current calculating data cube in a user function.

Returns

A reference to the current calculating data cube in a user function.

Example

CHANGE(MONTHS, THISCUBE())

The user function in this example calculates the monthly change for each data cube and is used inside an aggregate override user function that affects the SALES, COST_OF_GOODS, and GROSS_MARGIN data cubes.

In this example, the analytic calculation engine performs the same as if you entered these three functions:

CHANGE(MONTHS, SALES)
CHANGE(MONTHS, COST_OF_GOODS)
CHANGE(MONTHS, GROSS_MARGIN)

TRUNC

Syntax

TRUNC(Data)

Description

The TRUNC function returns the value of Data with the decimals truncated.

Returns

The value of Data with the decimals truncated.

Example

The following examples employ the TRUNC built-in function:

TRUNC(14) returns 14.
TRUNC(14.3) returns 14.
TRUNC(14.7) returns 14.

UPPER

Syntax

UPPER(Text)

Description

The UPPER function returns Text converted to uppercase.

Returns

Text converted to upper case.

Example

UPPER("StringFun") returns STRINGFUN.

VAR

Syntax

VAR(Dimension, Values, {Type}, {Condition})

Description

The VAR function returns the variance of Values. If Type is zero or omitted, VAR calculates a population variance; otherwise, VAR calculates a sample variance. If Condition is omitted, the function uses all Values. If Condition is included, the function uses only those Values where Condition is true.

Example

Suppose that an analytic model contains a data cube called SCORES that uses dimensions called STUDENTS and TESTS.

Use the following formula to calculate the variance of the tests for each student:

VAR(TESTS, SCORES)

Use the following formula to calculate the variance of scores over 75 percent for each test:

VAR(STUDENTS, SCORES, 0, SCORES > 0.75)

WHILE

Syntax

WHILE(Condition, Expression)

Description

The WHILE function supports looping and takes two arguments: a condition that determines whether to continue looping and an expression to evaluate for each iteration.

Example

WHILE(&Balance < TARGET_BALANCE .AND. &Month < NUMMEMBERS(MONTHS),
	INC(&Month);
	INC(&Balance, AT(MONTHS, &Month, CA)));
IF(&Month <= NUMMEMBERS(MONTHS), &Month, #N/A)

This formula calculates the number of months required to accumulate a target balance.

The IF function returns the value of &Month, or an error code if the target balance is not achieved. Notice that it is not necessary to initialize &Balance and &Month because they are initialized to zero before the formula is evaluated.

YEAR

Syntax

YEAR({Date})

Description

The YEAR function returns the year of Date. If Date is omitted, the function returns the year of the calculation date.

Example

Suppose that an analytic model contains a data cube called YEAR_EXAMPLE that uses a dimension called MONTHS, and has the following formula: YEAR( ). Because the argument is omitted, YEAR returns the year for each date in the MONTHS dimension.

Now suppose that you plan to build a new building in 2006, and you want to spread the building costs over the quarters of that year. On the other hand, you do not want to allocate the building costs to any other years. If the year and building costs are stored in data cubes called BUILDING_YEAR and TOTAL_BUILDING_COSTS, you can calculate the QTRLY_BUILDING_COSTS data cube as follows:

IF(YEAR( ) = BUILDING_YEAR, TOTAL_BUILDING_COSTS / 4, 0)

See Also

IF.