FIX Operator
The FIX operator is a data function that fixes the context of the aggregation. It prevents the exception aggregation from passing on its reference characteristics to the GROUP BY clause of the preceding aggregation and calculation steps. This allows execution of the constant selection before calculation of the formula, as expected.
There is a correlation between exception aggregation and constant selection: The data that flows into the exception aggregation is grouped by the reference characteristic of the exception aggregation, for example by characteristic Product. In conjunction with a constant selection, this behavior means that the constant selection by Product is basically rendered ineffective. This makes sense in scenarios where the exception aggregation and constant selection are performed using different characteristics.
- Constant selection: 1% of sales with constant selection by all products,
- Exception aggregation: Number of years in which the sales per product is greater than 1% of total sales,
This is different is the exception aggregation and constant selection are performed using the same characteristic.
- Constant selection: 1% of sales with constant selection by all products,
- Exception aggregation: Sales by product > percentage of sales with constant selection by all products
The following example serves to illustrate that, in this case, the correct execution of aggregation and constant selection in the analytic manager can cause unexpected results from the customer's perspective. The customer might want to specifically influence the result on which the formula exception aggregation is executed at the end.
A query is used to calculate how many products have sales of over 1% of the total sales for all products.
- The basic key figure is Sales.
- The second key figure is the restricted key figure Sales with constant selection on characteristic Product. This means that the key figure shows the sales for all products, not just for the product in the filter (Product A).
- The third key figure is calculated using the formula 1% of sales with constant selection by Product. The key figure Sales with constant selection on characteristic Product is multiplied by 0.01 here.
- The fourth key figure is also calculated using a formula: This is a binary comparative operation between Sales and 1% of sales with constant selection by Product. The system should return 1 if Sales is greater than 1% of sales with constant selection by Product. Otherwise it should return 0. This key figure contains the exception aggregation SUM by Product.
The following table shows the query result with an unexpected result from the customer's perspective for key figure Sales > Sales with constant selection by Product:
| Product | Sales | Sales with Constant Selection by Product | 1% of Sales with Constant Selection by Product | Sales > Sales with Constant Selection by Product |
|---|---|---|---|---|
| A | EUR 450 | EUR 100,000 | EUR 1000 | 1 |
| B | EUR 1200 | EUR 100,000 | EUR 1000 | 1 |
| Total | EUR 1650 | EUR 100,000 | EUR 1000 | 2 |
The analytic manager returns 1 as the result for Product A. As the sales value of 450 is less than als 1000, the expected result would actually be 0. How did this happen?
The analytic manager calculates according to the following formula:
SUM( Sales > ( 0.01 * (Sales with constant selection by product) ) by product
As the fourth key figure is calculated using a formula, there is a formula exception aggregation here: The exception aggregation SUM is executed on the result of the formula calculation. In accordance with the sequence of aggregation calculations in the analytic manager, formula exception aggregations are executed at the end. This means that reference characteristic Product in the GROUP-BY clause is contained in every part of the formula subtree. The exception aggregation therefore overwrites the constant selection here. This is because the following rules applies for the constant selection:
The constant selection only affects the selection and has no effect on the order in which the aggregation and formula calculation are processed. The constant selection is not executed before aggregation.
- For Product A: 450 > 450*0,01. In this case, the result value "1" is not as expected, as the individual product sales value is smaller than the percentage sales with constant selection over all products.
- For Product B: 1200 > 1200*0.01. In this case, the result value "1" is only as expected because as the individual product sales value is greater than the percentage sales with constant selection over all products.
To prevent the constant selection from being ineffective in scenarios like this, you should use the FIX operator. The FIX operator has the task of excluding the GROUP-BY clause during calculation of the exception aggregation. This allows execution of the constant selection before calculation of the formula. This ensures that the expected result is achieved.
| Product | Sales | Sales with Constant Selection by Product | 1% of Sales with Constant Selection by Product | Sales > Sales with Constant Selection by Product |
|---|---|---|---|---|
| A | EUR 450 | EUR 100,000 | EUR 1000 | 0 |
| B | EUR 1200 | EUR 100,000 | EUR 1000 | 1 |
| Total | EUR 1650 | EUR 100,000 | EUR 1000 | 1 |
If the FIX operator is used in the example above, the analytic manager calculates according to the following formula:
SUM ( Sales > FIX ( 0.01 * (Sales with constant selection by product) ) by product
The system works as follows:
The system checks which exception aggregation rule is next in the sequence and asks for the reference characteristic.
It aggregates by the characteristic in accordance with the aggregation rule.
This single value then serves as the basis for the following calculations:
The query result now provides the expected values.
Note that the FIX operator only influences one individual exception aggregation. It does this by excluding the GROUP-BY information for the reference characteristic used in the formula. With nested formulas, other formulas are not affected.