Calculating with Formula Variables
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:
- If you use a formula variable with "Replacement from the Value of an Attribute" in a calculated key figure, the system automatically adds the drilldown according to the reference characteristic for the attribute. The system then evaluates the variables for each characteristic value for the reference characteristic. The calculated key figure is then calculated and, subsequently, all of the other operations are executed (all additional, calculated key figures, aggregations, and formulas). Before aggregation with the reference characteristic, the system therefore only calculates the operators which are assembled in the calculated key figure itself.Caution
Note that calculation before aggregation can have a negative impact on performance.
- If you are using a formula variable with Replacement from the Value of an Attribute in a formula element, the variable is only calculated if the reference characteristic is uniquely specified in the corresponding row, column, or in the filter.
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
InfoProviders
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.