Cell Calculation Order: Example 3

In this example, these conditions are true:

Table 70 shows a subset of the cells in a data block:

Table 70. Calculation Order Example 3: Input Cells and #MISSING Values

Year-MarketNew YorkMassachusettsEast
Jan#MISSING#MISSING181099
Feb#MISSING#MISSING211431
Mar#MISSING#MISSING205690
Qtr1#MISSING#MISSING 

The cells are calculated in the same order as in Cell Calculation Order: Example 2. Qtr1 -> East is calculated on both the Year and Market consolidation paths.

Because the setting for consolidating #MISSING values is turned off, Essbase does not consolidate the #MISSING values. Thus, the data that is loaded at parent levels is not overwritten by the #MISSING values below it.

However, if any of the child data values are not #MISSING, these values are consolidated and overwrite the parent values. For example, if Jan -> New York contains 50000.00, this value overwrites the values loaded at parent levels.

The results, as shown in Table 71, show that Essbase first correctly calculates the Qtr1 -> East cell by consolidating Jan -> East, Feb -> East, and Mar -> East, and then calculates on the Market consolidation path. However, it does not consolidate the #MISSING values in Qtr1 -> New York and Qtr1 -> Massachusetts; therefore, the value in Qtr1 -> East is not overwritten.

Table 71. Calculation Order Example 3: Results

Year-MarketNew YorkMassachusettsEast
Jan#MISSING#MISSING181099
Feb#MISSING#MISSING211431
Mar#MISSING#MISSING205690
Qtr1#MISSING#MISSING598220

Essbase must calculate the Qtr1 -> East cell twice to ensure that a value is calculated for the cell. If Qtr1 -> East is calculated according to only the last consolidation path, the result is #MISSING, which is not the required result.