Create Different Calculations for Different Members

This topic provides an overview of the calculation of only one member and the calculation of more than one member and discusses how to:

  • Create a calculation for only one member.

  • Create a calculation for more than one member.

Understanding the Calculation of Only One Member

The following example describes the reason for and process of creating a special calculation for one member.

Suppose your company must allocate the Administration department's expense equally to all of the other departments. To ensure proper allocation, the Administration department requires a different calculation than the other departments. To create this allocation, you must back out the expense for Administration and divide that expense equally among the other departments.

Your company's analytic model contains data cubes called EXPENSE and ADMIN_ALLOCATION. The DEPARTMENTS dimension is attached to both data cubes. Create the following formula to calculate ADMIN_ALLOCATION:

IF([DEPARTMENTS:Administration], - EXPENSE, EXPENSE[DEPARTMENTS:Administration] / 
(NUMMEMBERS(DEPARTMENTS) - 1))

The formula uses the IF function to calculate one result if a condition is true, and another result if the condition is false. Here is how the formula works:

The analytic calculation engine uses the [DEPARTMENTS:Administration] member reference to check whether Administration is the department that is being calculated.

  • If Administration is the department that is being calculated, the formula returns minus EXPENSE, backing out the expense for Administration.

  • If Administration is not the department that is being calculated, the formula returns the expense for Administration divided by the number of departments minus one.

    In other words, the formula divides the Administration expense equally among the other departments.

    Note:

    The formula uses the data cube slice EXPENSE[DEPARTMENTS:Administration] to refer to the expense for Administration. The NUMMEMBERS function returns the number of members in the specified dimension.

Understanding the Calculation of More Than One Member

The following formula provides an example of a calculation for more than one member. The formula returns one result for Administration, another result for Data Processing, and a third result for all other departments:

CASE([DEPARTMENTS:Administration] : ADMINISTRATION_RESULT, 
[DEPARTMENTS:Data Processing] : DATA_PROCESSING_RESULT, 
#DEFAULT : RESULT_FOR_ALL_OTHER_DEPARTMENTS)

Creating a Calculation for Only One Member

To create a calculation for only one member:

  1. Select Start, and then Programs, and then PeopleTools 8.5x, and then Application Designer to access PeopleSoft Application Designer.

  2. After signing in to the PeopleSoft Application Designer, open an analytic model definition.

  3. Select the data cube whose formula you want to define.

  4. Define a formula for the result data cube.

  5. Enter IF and an opening parenthesis.

    You are using the IF function to return different results, depending on a condition.

    See IF.

  6. Insert the member reference for the exceptional member.

    Note:

    When you use a member reference as a condition, it returns True if the analytic calculation engine is calculating values for that member; otherwise, it returns False.

    See Inserting a Dimension Member Reference into a Rule.

  7. Enter a comma, and then enter the result that should be returned if the exceptional member is being calculated.

  8. Enter another comma, and then enter the result that should be returned if one of the nonexceptional members is being calculated.

  9. Enter a closing parenthesis.

    Note:

    To perform the same calculation for several members, combine two or more member references with .OR. operators. For example: [DEPARTMENTS:Administration] .OR. [DEPARTMENTS:Data Processing].

Creating a Calculation for More Than One Member

To create a calculation for more than one member:

  1. Select Start, and then Programs, and then PeopleTools 8.5x, and then Application Designer to access PeopleSoft Application Designer.

  2. After signing in to the PeopleSoft Application Designer, open an analytic model definition.

  3. Select the data cube whose formula you want to define.

  4. Define a formula for the result data cube.

  5. Enter CASE and an opening parenthesis.

    You are using the CASE function to evaluate a Condition:Result pair for each special case.

    See CASE.

  6. Enter a Condition:Result pair for each special calculation:

    1. Insert a member reference for one of the members in the dimension.

      For example: [DEPARTMENTS:Administration].

      This condition tests whether results are being calculated for the specified member.

    2. Enter a colon to separate the condition from the result.

    3. Enter the appropriate result for the specified member.

    4. Enter a comma.

  7. Perform these steps to enter a final Condition:Result pair to return a result for all other members in the dimension:

    1. Enter #DEFAULT as the condition.

      #DEFAULT instructs the function to return the final result for all other members.

    2. Enter a colon to separate the condition from the result.

    3. Enter the result for all other members in the dimension.

    4. Enter a closing parenthesis.