Show TOC

FIX Operator and Various Aggregations

The following examples show how the system performs a calculation if the FIX operator is in an exception aggregation or if an exception aggregation is in the FIX operator.

The FIX operator only affects the reference characteristic used in the formula of a particular exception aggregation. In the example below, we expand on the example for using the FIX operator with reference characteristic Calendar Month. The following key figure is added: SUM (Sales > 1% of sales with constanct selection by product) by calendar month. This means that we add an additional exception aggregation, SUM by calendar month. To make the table clearer, we restrict the calendar month to the months 04.2004 and 05.2004.

Table 1: FIX Operator and Various Aggregations, Table 1
Calendar Month Product Sales Sales with Constant Selection by Product 1% of Sales with Constant Selection by Product Sales > 1% of Sales with Constant Selection by Product Sales > 1% of Sales with Constant Selection by Product by Calendar Month
04.2004 A EUR 40 EUR 8,000 EUR 100 0 0
B EUR 120 EUR 8,000 EUR 80 1 1
Total EUR 160 EUR 8,000 EUR 80 1 1
05.2004 A 20 EUR 10,000 EUR 100 0 0
B 320 EUR 10,000 EUR 100 1 1
Total 340 EUR 100,000 EUR 100 1 1
Total 500 EUR 100,000 EUR 180 1 2
The formula that the analytic manager executes is as follows:
`SUM( SUM( Sales > FIX( 0.01 * (Sales with constant selection by product) ) by product ) by calendar month`

The FIX operator only removes the GROUP-BY clause for Product, as this characteristic is the reference characteristic of the subformula. The GROUP-BY clause is retained for the Calendar Month characteristic. This explains the deviating result in the total.

What would happen if an exception aggregation were defined in the FIX operator?

In the following example, the reference key figure was changed: It is now 1% of the average sales of all products per calendar month. The formula that the analytic manager executes is as follows:
`AVG( 0,01* (Sales with constant selection by product)) by calendar month`
The check is performed as in the previous example, but with the new reference characteristic:
`SUM( Sales > FIX( AVG( 0,01 * (Sales with constant selection by product)) by calendar month) ) by product`
Table 2: FIX Operator and Various Aggregations, Table 2
Product Sales Sales with Constant Selection by Product 1% of Sales with Constant Selection by Product Sales > 1% of Sales with Constant Selection by Product
A EUR 60 EUR 18,000 EUR 93 0
B EUR 440 EUR 18,000 EUR 93 1
Total EUR 500 EUR 18,000 EUR 193 1

This example illustrates that the FIX operator prevents the inner aggregation from being executed as defined by the inner formula. It does not affect the aggregation of the formula result.