Calculate First, Last, and Average Values
Using time balance and variance reporting tags on the accounts dimension, you can tell Essbase how to perform time balance calculations on accounts data. To use these specialized accounts tags, you must have a dimension tagged as accounts and a dimension tagged as time.
Essbase usually calculates a dimension tagged as time by consolidating or calculating the formulas on the parent’s children. However, you can use special time balance and variance reporting tags on the accounts dimension to consolidate a different kind of value. If you tag a parent member in the accounts dimension with a time balance property of First, Essbase calculates it by consolidating the value of its first child.
For example, in Sample Basic, the Opening Inventory member in the Measures dimension has a time balance property of First. This member represents the inventory at the beginning of the time period.
If the time period is Qtr1, Opening Inventory represents the inventory available at the beginning of Jan (the first member in the Qtr1 branch).
Use the First, Last, and Average time balance properties, and the Expense variance reporting property, only on members of a dimension tagged as accounts. The dimensions you tag as time and accounts can be either dense or sparse dimensions.
For cells of time balance account members, a member in any dimension other than the time dimension that is set with the ^ consolidation operator is excluded from the Average calculation. The member is, however, included in First and Last calculations.
Formulas override time balance properties. If a member with a formula uses time balance properites, the time balance properties are ignored, and the formula is used to calculate it.
Note:
If you are using Intelligent Calculation, changing accounts tags in the outline does not cause Essbase to restructure the database. You may have to tell Essbase explicitly to recalculate the required data values. See Changed Formulas and Accounts Properties: Impact on Block Status.
Specify Accounts and Time Dimensions
When you tag a dimension as accounts, Essbase knows that the dimension contains members with accounts tags. When you tag a dimension as time, Essbase knows that this dimension is the one on which to base the time periods for the accounts tags.
Using Sample Basic as an example, the Measures dimension is tagged as accounts, and the Year dimension is tagged as time.
Figure 22-1 Sample Basic Outline Showing Accounts and Time Tags
Report the Last Value for Each Time Period
For an accounts dimension member, you can set a property of Time Balance Last to tell Essbase to move the last value for each time period up to the next level.
To report the last value for each time period, set the member’s time balance property as Last.
Using Sample Basic as an example, the accounts member Ending Inventory is tagged as Time Balance Last. Ending Inventory consolidates the value for the last month in each quarter and uses that value for that month’s parent. For example, the value for Qtr1 is the same as the value for Mar.
For information on tagging an accounts member as Last, see Time Balance Properties.
By default, Essbase does not skip #MISSING or zero (0) values when calculating a parent value. You can choose to skip these values. For a discussion of how and why to skip #MISSING values, see Skip #MISSING and Zero Values in Time Balance Calculations.
Report the First Value for Each Time Period
For an accounts dimension member, you can set a property of Time Balance First to tell Essbase to move the first value for each time period up to the next level.
To report on the first value for each time period, set the member’s time balance property as First.
Using Sample Basic as an example, the accounts member Opening Inventory is tagged as Time Balance First. Opening Inventory consolidates the value of the first month in each quarter and uses that value for that month’s parent. For example, the value for Qtr1 is the same as the value for Jan.
For information on tagging an accounts member as First, see Time Balance Properties.
By default, Essbase does not skip #MISSING or zero (0) values when calculating a parent value, but you can choose to skip the empty values. See Skip #MISSING and Zero Values in Time Balance Calculations.
Report the Average Value for Each Time Period
For an accounts dimension member, you can tell Essbase to average values across time periods and consolidate the average up to the next level.
For example, you can tell Essbase to average the values for Jan, Feb, and Mar and then use that value for the Qtr1 value. To report the average value for each time period, set the member’s time balance property as Average.
For information on tagging an accounts member as Time Balance Average, see Time Balance Properties.
By default, Essbase does not skip #MISSING or zero (0) values when it calculates a parent value. Thus, when it calculates the average, Essbase aggregates the child values and divides by the number of children, regardless of whether the children have #MISSING or zero values. You can tell Essbase to skip #MISSING and zero values. See Skip #MISSING and Zero Values in Time Balance Calculations.
Skip #MISSING and Zero Values in Time Balance Calculations
You can optionally have Essbase skip empty values when doing time balance calculations.
A #MISSING value is a marker in Essbase that indicates that the data in this location does not exist, does not contain any meaningful value, or was never entered.
By default, Essbase does not skip #MISSING or 0 (zero) values when calculating a parent value.
You can override this default by setting a skip property. See Skip Properties for Time Balance.
For example, if you tag an accounts dimension member as Time Balance Last and Skip Missing, then Essbase consolidates the last non-missing child to the parent. Consider the example below:
Table 22-1 Example of the Effects of the Skip Missing
| Accounts -> Time | Jan | Feb | Mar | Qtr1 |
|---|---|---|---|---|
|
Accounts Member (Last, Skip Missing) |
60 |
70 |
#MI |
70 |
Tagging an account as Average and Skip Missing may produce different results from tagging that account as Average and Skip None. A calculation performed with Average and Skip None produces correct results because no data is skipped. But because grandparents with children are consolidated by summing the averages, results of a calculation on an account with Average and Skip Missing is incorrect unless you use Dynamic Calc or Two-Pass tags.
Effects of First, Last, and Average Tags
The following example shows how Essbase consolidates the time dimension based on the time balance First, Last, and Average tags on accounts dimension members.
Table 22-2 Example of the Effects of (TB) First, Last and Average
| Accounts -> Time | Jan | Feb | Mar | Qtr1 | Consolidation |
|---|---|---|---|---|---|
|
Accounts Member1 |
11 |
12 |
13 |
36 |
Value of Jan + Feb + Mar |
|
Accounts Member2 (TB First) |
20 |
25 |
21 |
20 |
Value of Jan |
|
Accounts Member3 (TB Last) |
25 |
21 |
30 |
30 |
Value of Mar |
|
Accounts Member4 (TB Average) |
20 |
30 |
28 |
26 |
Average of Jan, Feb, Mar |