Order of operations for model calculations

In some cases, usually for ratio-related calculations, it is useful to perform the aggregation on the calculation terms prior to the mathematical operation.

For example, the following Order details fact contains information about each order:

the query items contained by the order details fact

Margin is a calculation that computes the ratio of profit:

Margin = (Revenue - Product cost) / Revenue

If we run a query to show Revenue, Product cost, and Margin for each product using the Order details fact, we get the following results:

Product number	Revenue	Product cost	Margin
1	$23,057,141	$11,292,005	61038%
2	$11,333,518	$6,607,904	49606%

Notice that the value for Margin seems to be wrong. This is because of the order of operations used in computing Margin. Margin is computed as:

Margin = sum( (Revenue - Product cost) / Revenue )

The aggregation took place after the mathematical operation and, in this case, it produces undesired results.

To produce the desired values for Margin, we need to aggregate before the mathematical operation:

Margin = ( sum(Revenue) - sum(Product cost) ) / sum(Revenue)

This produces the following results:

Product number	Revenue	Product cost	Margin
1	$23,057.141	$11,292,005	51.03%
2	$11,333,518	$6,607,904	41.70%

You can accomplish this in IBM® Cognos® Framework Manager by creating a stand-alone calculation for Margin and setting its Regular Aggregate property to Calculated. Each query item in the calculation's expression is aggregated as specified in its Regular Aggregate property. The Regular Aggregate properties for Revenue and Product cost are set to Sum and thus, when computing the calculation, sum is used to aggregate those terms.

Note: The calculated aggregation type is not supported for calculations that are embedded within query subjects. It is supported only for stand-alone calculations and for calculations that are embedded within measure dimensions and are based on measures from the same measure dimension.

For example, consider the Margin calculation that is embedded in the Sales measure dimension:

the margin calculation is shown as (sales.revenue - sales.product cost) / sales.revenue

In this example, Margin is based on the measures Product cost and Revenue that are within the same measure dimension, Sales. If the Regular Aggregate property for Margin is set to Calculated, it is rolled up as:

Margin = sum(Revenue - Product cost ) / sum(Revenue)

If Margin is based on the source query items of the measures Product cost and Revenue (Sales (model).Product cost, Sales (model).Revenue), the calculated aggregation is not supported and the aggregation behaves as automatic. In this case, Margin is rolled up as:

Margin = sum( Revenue - Product cost) / Revenue)

For more information, see Modifying how query items are aggregated.