Value-Related Functions

You can use Essbase value functions in your outline formulas to calculate equations on interdependent metrics such as inventory over time periods, variances used in expense reporting, and allocations.

The following topics discuss use of formulas related to values.

• Interdependent Values

• Variances Between Actual and Budget Values

• Functions that Allocate Values

Interdependent Values

Sometimes, to calculate the required results, you need an Essbase formula to apply interdependent, multiple equations to one member in the outline.

Essbase optimizes calculation performance by calculating formulas for a range of members in the same dimension simultaneously. Some formulas, however, require values from members of the same dimension, and Essbase may not yet have calculated the required values.

A good example is that of cash flow, in which the opening inventory is dependent on the ending inventory from the previous month.

The values for Opening Inventory and Ending Inventory must be calculated on a month-by-month basis. Assume you want to achieve the results shown:

Table 18-11 Data Values for Cash Flow Example

	Jan	Feb	Mar
Opening Inventory	100	120	110
Sales	50	70	100
Addition	70	60	150
Ending Inventory	120	110	160

Assuming that the Opening Inventory value for January is loaded into the cube, the following calculations are required to get the results in the grid above:

1. January Ending   = January Opening – Sales + Additions
2. February Opening = January Ending
3. February Ending  = February Opening – Sales + Additions
4. March Opening    = February Ending
5. March Ending     = March Opening – Sales + Additions

You can calculate the required results by applying interdependent, multiple equations to one member in the outline.

The following formula, applied to the Opening Inventory member in the outline, calculates the correct values:

IF(NOT @ISMBR (Jan))
   "Opening Inventory" = @PRIOR("Ending Inventory");
ENDIF;
"Ending Inventory" = "Opening Inventory" - Sales + Additions;

If you place the formula in a calculation script, you must associate the formula with the Opening Inventory member as shown:

"Opening Inventory"
(IF(NOT @ISMBR (Jan))
   "Opening Inventory" = @PRIOR("Ending Inventory");
ENDIF;)
"Ending Inventory" = "Opening Inventory" - Sales + Additions;

Essbase cycles through the months, performing the following calculations:

1. The IF statement and @ISMBR function check that the current member on the Year dimension is not Jan. This step is necessary because the Opening Inventory value for Jan is an input value.

2. If the current month is not Jan, the @PRIOR function obtains the value for the Ending Inventory for the previous month. This value is then allocated to the Opening Inventory of the current month.

3. The Ending Inventory is calculated for the current month.

Note:

To calculate the correct results, you must place the above formula on one member, Opening Inventory. If you place the formulas for Opening Inventory and Ending Inventory on their separate members, Essbase calculates Opening Inventory for all months and then Ending Inventory for all months. This organization means that the value of the Ending Inventory of the previous month is not available when Opening Inventory is calculated.

Variances Between Actual and Budget Values

The @VAR and @VARPER calculation functions enable you to calculate a variance or percentage variance between budget and actual values.

You may want variances to be positive or negative, depending on whether you are calculating expense or nonexpense items in the Accounts dimension. By default, Essbase assumes that members are nonexpense items.

Consider the impact of expense vs nonexpense accounting on variance calculations:

• Expense items. You want Essbase to show a positive variance if the actual values are less than the budget values (for example, if actual costs are less than budgeted costs).

• Nonexpense items. You want Essbase to show a negative variance if the actual values are less than the budget values (for example, if actual sales are less than budgeted sales).

By default, without using functions, Essbase assumes that members are nonexpense items and calculates the variance accordingly.

When you use the @VAR or @VARPER functions, Essbase shows a positive variance if the actual values are less than the budget values. For example, in Sample.Basic, the children of Total Expenses are expense items. The Variance and Variance % members of the Scenario dimension calculate the variance between the Actual and Budget values.

Figure 18-3 Variance Example

See Setting Variance Reporting Properties.

Functions that Allocate Values

Essbase allocation functions allow you to allocate values that are input at the parent level across child members in the same dimension, or in different dimensions. For example, you can allocate costs across products, or you can perform cross dimensional and range allocations.

The allocation is based on a variety of criteria you specify when using the calculation functions.

Table 18-12 List of Allocation Functions

Function	Allocated Values
@ALLOCATE	Values from a member, cross-dimensional member, or value across a member list within the same dimension.
@MDALLOCATE	Values from a member, cross-dimensional member, or value across multiple dimensions.

For examples of calculation scripts using @ALLOCATE, see also Allocate Costs Across Products; using @MDALLOCATE, see Allocate Values Across Multiple Dimensions.