Built-in Function Reference

Example

The following examples employ the ABS built-in function:

• ABS(5) returns 5.

• ABS(-5) returns 5.

• ABS(0) returns 0.

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).

Example

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

Example

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

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).

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).

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.

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.

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.

Example

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

Example

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

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.

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).

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)

Example

The following examples provide uses of the CUMAVG function:

CUMAVG(TESTS, SCORES)

CUMAVG(MONTHS, SALES, 6)

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)

Example

The following examples provide uses of the DAVG function:

DAVG(PRODUCTS, UNITS_SOLD)

AVG(PRODUCTS, UNITS_SOLD, ADVERTISING_BY_PRODUCT >= 10000)

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

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.

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.

Example

Suppose you purchase a machine for USD 6000, 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

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.

Example

Suppose that a company awards its salespeople a 10 percent commission if their sales are at least USD 50000, an 8 percent commission if their sales are at least USD 30000, a 5 percent commission if their sales are at least USD 15000, and a 1 percent commission if their sales are less than USD 15000. 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.

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 10000:

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.

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 10000 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.

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 total units sold for all products:

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 10000 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.

Example

These examples employ the E built-in function:

• E( ) returns 2.7182818285.

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

Example

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

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

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.

Example

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

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.

Example

Suppose that you deposit USD 1000 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 1000 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 3000 in it at the start of the 8 years, use the following formula:

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

Example

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

1. EMPLOYEE_SALARY, which uses a dimension called EMPLOYEES.

   This data cube contains the salary for each employee.

2. AVERAGE_DEPARTMENT_SALARY, which uses a dimension called DEPARTMENTS.

   This data cube contains the average salaries for each department.

3. 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.

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.

Example

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

1. TRANSACTIONS, which contains a series of sales transactions.

2. PRODUCTS, which contains a dimension of products.

Define the following data cubes:

1. SALE_AMOUNT, which uses the TRANSACTIONS dimension. This data cube contains the amount of each sale.

2. An association data cube called PRODUCT_SOLD, which associates TRANSACTIONS with PRODUCTS.

   See Creating Association Data Cubes.

3. 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.

Example

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

1. TRANSACTIONS, which contains a series of sales transactions.

2. PRODUCTS, which contains a series of products.

Define the following data cubes:

1. SALE_AMOUNT, which uses the TRANSACTIONS dimension.

   This data cube contains the amount of each sale.

2. An association data cube called PRODUCT_SOLD, which associates TRANSACTIONS with PRODUCTS.

   See Creating Association Data Cubes.

3. 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.

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.

Example

The following examples provide uses of the GROUPSUM function.

GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_DEPT)

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.

Example

For example, suppose a company awards its salespeople a 10 percent commission on sales of at least USD 20000, and a 5 percent commission on sales under USD 20000. 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.

Example

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

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

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)

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.

Example

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

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.

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.

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.

Example

LEFT("StringFun", 6) returns String.

Example

LEN("StringFun") returns 9.

Example

LOWER("StringFun") returns stringfun.

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.)

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.

Example

MBR2TEXT(MONTH) returns January.

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)

Example

The following examples employ the MEMBER function:

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

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

Example

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

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.

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.

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.

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.

Example

Suppose that you deposit 1000 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 20000 USD , use the following formula for the YEARS_REQUIRED cube:

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

If you deposit the 1000 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 5000 USD in it at the start, use the following formula:

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

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.

Example

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

Example

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

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 user ID 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 user IDs 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.

The following table lists the values of the fields that are mapped to the USERID dimension and DEPT_CUBE data cube.

The following table lists the values of the fields that are mapped to the DEPT_DIM dimension and BONUS_AMT data cube.

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

1. The OPRID function returns the user ID of the current user in text format.

2. The TXT2MBR function compares the user ID with the member in the USERID dimension to determine if they match.

   If the user ID matches the member in the USERID dimension, the AT function searches for the coordinates of the user ID 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.

3. The analytic calculation performs one of these actions:

   • If the value returned from DEPT_CUBE matches the member returned from DEPT_DIM, the user ID 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 user ID cannot see the bonus amount.

Example

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

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.

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.

Example

The following examples employ the PI function:

Example

If you take out a loan for 50000 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 30000 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

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.

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.

Example

Suppose that a machine that sells for 80000 USD saves your company 11000 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 73811 USD, indicating that you might be better off investing the 80000 USD in another way. But suppose that you can sell the machine for 30000 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.

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)

Example

RAND() returns 0.938119738.

Example

Suppose that you wish to invest 5000 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 100000 USD as follows:

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

Now suppose that you initially invest 15000 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%

Example

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

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.

Example

RIGHT("StringFun", 3) returns Fun.

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.

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.

Example

The following formula sets the &Index variable to 1.

SET(&Index, 1)

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).

Example

Suppose that you purchase a machine for 6000 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

Example

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

Example

These examples employ the SQRT built-in function:

• SQRT(25) returns 5.

• SQRT(2) returns 1.4142135624.

• SQRT(-25) returns an error value.

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)

Example

Suppose that you purchase a machine for 6000 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

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).

Example

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

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%.

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)

Example

The following examples employ the TRUNC built-in function:

• TRUNC(14) returns 14.

• TRUNC(14.3) returns 14.

• TRUNC(14.7) returns 14.

Example

UPPER("StringFun") returns STRINGFUN.

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)

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.

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)