FIX Operator and Restricted Constants
The following example illustrates another way of using the FIX operator: Restricted constants
| Product | Sales (Product A, Product B, Product C) | 10 (Product A, Product B) | SUM (10(Product A, Product B)) by Product | SUM ( (SUM(10 (Product A, Product B)) by Product) / (Sales (Product A, Product B, Product C)) ) by Product | SUM ( (SUM(10 (Product A, Product B)) by Product) / FIX (Sales (Product A, Product B, Product C)) ) by Product |
|---|---|---|---|---|---|
| A | EUR 450 | 10 | 10 | 0,022 | 0,022 |
| B | EUR 1200 | 10 | 10 | 0,008 | 0,008 |
| C | EUR 1210 | 10 | |||
| Total | EUR 2860 | 10 | 20 | 0,030 | 0,007 |
This example serves primarily to illustate the difference between a formula without exception aggregation and a formula with exception aggregation.
-
In a formula without exception aggregation, a constant is always treated as a value.
-
In a formula with exception aggregation, the system takes the restriction of a restricted key figure and aggregates accordingly. In a formula with exception aggregation, the system performs the calculation along the drilldown. In other words, the total is calculated from the subtotals. This gives us the value 0.030 for key figure SUM ( (SUM(10 (Product A, Product B)) by Product) / (Sales (Product A, Product B, Product C)) ) by Product.
If we want to have a formula with exception aggregation, but the calculation direction of a formula without exception aggregation, i.e. the total of the operands should be be the total of the formula result, we can use the FIX operator: Formula SUM ( (SUM(10 (Product A, Product B)) by Product) / FIX (Sales (Product A, Product B, Product C)) ) by Product results in 0.007, calculated from 10 (Product A) / 2860 + 10 (Product B) / 2860 + NULL (Product C) / 2860 = 20/2860. This is the same as the calculation of a formula without exception aggregation, but using a formula with exception aggregation in order to have the restricted key figure as the starting point.