Calculating an Average Price

Examine the Price, 1Quarter value in the Test worksheet. This number is the sum of the prices for January, February, and March. However, the number should reflect the average price for the three months. The following rules statement yields the desired value:

['Price']=C:['Sales']\['Units']*1000;

The restrictor C: limits this rules statement to consolidations; that is, only when one or more of the elements that identify a Price cell are consolidated elements.

Procedure

  1. Reopen the Rules Editor for the SalesCube cube.
  2. Enter the following statement without the Area definition, as the third line in the entry field.

    ['Price']=DB('PriceCube',!actvsbud,!region,!model,!month);C:['Sales']\['Units']*1000;

    You omit the Area definition because you already defined the Price area in the second line. When you want to apply different formulas to the same Area, you define the area and then specify the formulas sequentially.

  3. Click Save to save the edited rule.
  4. In the test worksheet, press F9 to recalculate and update the values.

    Observe the Price, 1Quarter value, which still displays the value 76,132.59. This is because TM1 uses the first rules formula it encounters that applies to the Price, 1 Quarter cell:

    ['Price']=DB('PriceCube',!actvsbud,!region,!model,!month);

    This statement is appropriate only for N: level cells, such as Price, Jan. Consolidated Price values should be calculated through the second Price statement. By restricting the first Price formula to N: level cells only, you enable TM1 to apply the second Price formula to consolidations.

    Note: TM1 evaluates the rules statements in the order they appear within a rule, but the first formula for a given Area takes precedence over later formulas for the same area. If you have multiple rules statements that address the same Area, you should order them least-restrictive to most-restrictive. For details, see Arranging Rules Statements.
  5. To restrict the first Price formula to N: level cells, open the SalesCube rule in the Rules Editor.
  6. Insert N: in front of the first formula portion of the first Price statement: 
    ['Price']=N:>DB('PriceCube',!actvsbud,!region,!model,!month);

    The entire rule for the Price area should now appear as follows:

    ['Price']=N:DB('PriceCube',!actvsbud,!region,!model,!month);
C:['Sales']\['Units']*1000;
  7. Click Save to save the rule.
  8. In the Test worksheet, press F9 to recalculate and display the updated values.

    All Price values should now reflect the correct calculations, with the 1 Quarter, Price value as an average of the first three months of the year.