Creating Different Calculations for Different Groups of Members

You may want to calculate a data cube in different ways for different groups of members. To perform different calculations for different groups of members:

  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. Create an association data cube that associates each member with a group.

    See Creating Association Data Cubes.

  4. Define a formula for the result data cube.

  5. Use the CASE function to evaluate two or more Condition:Result pairs.

    Perform the following steps for each Condition:Result pair:

    See CASE.

    1. Use a member reference to compare the association data cube to one of the members in the group dimension.

      This example is a formula for the INVEST_TYPE data cube:

      [TYPES:Stock]

      See Inserting a Dimension Member Reference into a Rule.

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

    3. Enter the appropriate result for that group.

    4. To add another Condition:Result pair, enter a comma; otherwise, enter a closing parenthesis.

Example: Creating Different Calculations for Different Groups of Members

Suppose that you create an analytic model to track your investments in stocks, bonds, and rental properties, and you want to know your monthly income. Because the income for stocks, bonds, and rental properties is calculated differently, you need to perform different calculations for different groups of these investments.

Create a data cube that associates each investment with an investment type. Suppose the dimension of investment types is called TYPES, and the association data cube is called INVEST_TYPE. You can calculate the investment income for each investment as follows: 

CASE(INVEST_TYPE = [TYPES:Stock] : NUMBER_OF_SHARES * DIVIDENDS_PER_SHARE, 
INVEST_TYPE = [TYPES:Bond] : BOND_RATE * BOND_AMOUNT / 12, 
INVEST_TYPE = [TYPES:Rent] : MONTHLY_RENT)

See Creating Association Data Cubes.

The CASE function evaluates multiple conditions and returns the result for the first true condition. Each Condition:Result pair is separated by a comma.

See CASE.

In the preceding formula, the CASE function compares the invest type for an investment to each member in the TYPES dimension. The formula uses a member reference (for example, [TYPES:Stock] ) to refer to each member. When the CASE function finds the matching member from the TYPES dimension, it returns the corresponding result. For example, if the invest type for an investment is Bond, the formula returns BOND_RATE * BOND_AMOUNT / 12.