The following example shows how you can calculate with formula variables using processing type Replacement Path with replacement from a characteristic value with the properties/values of the characteristic. The formula is replaced here in accordance with the cell context.
The replacement of formula variables with the processing type Replacement Path from a characteristic value acts differently in calculated key figures and formulas:
Note that calculation before aggregation can have a negative impact on performance.
You might want to multiply basic key figure Sales Quantity with the current value for attribute Price for characteristic Material for example. To do this, you have to use a formula variable that has been defined accordingly. Processing type Replacement Path, replacement from characteristic Material, Replace Variable with an Attribute Value, here the attribute Price.
When using formula variables, you can create a reference to the reference characteristic of the variable by taking the value of the variables from attribute [1ATTRREF] Characteristic Reference (Constant 1). This object is a dummy attribute that is available with each characteristic. It is used to create a reference to the characteristic, which is not used for aggregation. By choosing this attribute, you can influence the aggregation behavior of calculated key figures in a targeted way and can improve performance during calculation.
Master data table for material
The example query has the following objects:
Material Price (V1) is a variable that is replaced by the material price.
K1 = Material Price (V1) is a calculated key figure.
F1 = Material Price (V1) is a formula.
K2 = Material Price (V1) * Sales Quantity is a calculated key figure.
F2 = Material Price (V1) * Sales Quantity is a formula.
K3 = K1 * Sales Quantity is a calculated key figure in which calculated key figure K1 is further processed.
Query View 1
Query View 2
In the results rows, columns F1 and F2 are not specified, because the material value is not unique. The system cannot calculate F1 and F2 in the overall Query View 2 either. K1 only aggregates the prices. K3 multiplies these aggregated prices with the aggregated sales quantities. The results from K1 and K3 usually have no meaning. Calculated key figure K2 delivers the required results.