Spreading data using a relative proportion

In IBM® Cognos® Insight, use the relative proportional spread method to spread values to the leaves (children) of a consolidation proportional to the leaves of a reference cell. The reference cell can be located in the cube from which you initiate spreading or in a separate cube. However, the reference cell must share the same consolidations as the cell from which you initiate spreading.

The following example illustrates relative proportional spreading where the initial cell and the reference cell are in the same cube.

Table 1. A crosstab that illustrates relative proportional spreading
Country or region 1 Quarter Jan Feb Mar
Argentina 0 0 0 0
Belgium 0 0 0 0
Brazil 100 10 20 70
Canada 0 0 0 0
Chile 0 0 0 0
Denmark 0 0 0 0
The previous example shows a single consolidated value of 100 in the cell at the intersection of 1 Quarter and Brazil. The leaves of 1 Quarter are Jan, Feb, and Mar. In this example, the following things are true:
  • Jan contains the value 10, which is 10% of 100.
  • Feb contains the value 20, which is 20% of 100.
  • Mar contains the value 70, which is 70% of 100.

If you initiate relative proportional spreading from the intersection of 1 Quarter and Argentina, and specify an Update Action of Replace when spreading the value 400, the leaves of 1 Quarter, Argentina are populated as proportional to the leaves of 1 Quarter, Brazil:

  • Jan contains the value 40, which is 10% of 400.
  • Feb contains the value 80, which is 20% of 400.
  • Mar contains the value 280, which is 70% of 400.

Procedure

  1. Select the consolidated cell from which you want to initiate the data spreading.
  2. Right-click the cell and click Data Spread > Relative Proportional Spread.
  3. In the Cube list, select the cube in which the reference cell is located.
  4. If necessary, click any of the Dimension buttons to select a different dimension element that identifies the reference cell.

    When you click a Dimension button, the Subset Editor opens and displays all elements of the dimension. You can then select a single element and click OK.

    For example, click account1, select a different element in the Subset Editor, and click OK.

    Note: The Dimension buttons for consolidated elements are unavailable because the cell from which spreading is initiated and the reference cell must share common consolidated elements.
  5. Click Select.

    The Reference Cell field on the Relative Proportional Spread window is now populated with the cell you selected.

  6. In the Value field, enter the value to be spread.
  7. Select an Update Action.
  8. Click Apply.