Defining Inverse Formulas
Input-Ready Formula, Inverse Formula and Formula Group
This section provides a more precise description of the definition of inverse formulas using mathematical notation.
You can find a description of how to create input-ready and inverse formulas in BEx Query Designer, see Input-Ready Query.
You can find examples of inverse formulas under Examples: Inverse Formula and in SAP Note 1236347.
To describe an inverse formula precisely, we start by defining a notation. Let.
F = F(op1, …, opn) |
is a formula with the operands op1 to opn. If formula F is input ready, we call it the carrier of a formula group. By definition, the formula group consists of the operands op1 to opn and the formula F.
For input-ready formulas, inverse formulas and formula groups, the following rules apply and are checked in Query Designer or when the query is generated by the system:
FG1: The following objects can be used as operands opi, i = 1, …, n for an input-ready formula F : 1. Basic or restricted key figures 2. Constants or ‘reporting’ formulas. These are formulas that do not change if a manual change is made during a server roundtrip 3. Carrier of other formula group G, where F ≠ G and G are not %GT (percentage of total). FG 2 If the operand opi is input ready, you have to define an inverse formula in Query Designer that calculates opi from the following operands: F, op1, …, op(i-1), op(i+1), …, opn. FG 3 All elements in the formula group must be either in the key figure structure or in exception cells. The latter only applies for queries with two structures. |
Exception: %GT (“Percentage of Grand Total”)
If you want to calculate the percentage of the grand total, choose formula F= %GT. Only one operand is allowed in this case. This operand must be a basic key figure or a restricted key figure with disaggregation activated. You do not need to define an inverse formula.
Comments
● If you choose Create Inverse Formulas in the context menu of an input-ready formula in Query Designer, the system checks which operands inverse formulas need to be created for and creates a list of them. By double-clicking on an inverse formula, you can call the Change Formulascreen. Under Available Operands, the system displays a list of all operands allowed for converting the formula.
● The inverse formula for an input-ready operand of an input-ready formula is created by resolving the formula to the input-ready operand.
● It is also possible to define input-ready formulas in a reusable structure. All elements from the formula group then have to be in the structure.
● In a query with input-ready formulas, the key figures must not be only in the query filter.
● The system supports concatenation of formulas. Formulas can also have intersections and be contained in other formulas. Nesting must not have a cycle.
An input-ready or inverse formula cannot be nested if it contains a formula variable of type Replacement Path. This ensures that the system can always replace the variable with the master data attributes of a characteristic.
● Inverse formulas are technical objects that are needed for inversion of input-ready formulas. On the Display tab, inverse formulas therefore have the settings Always Hide as the default setting. For debugging purposes, it can be useful to show these technical formulas.
Queries with Two Structures
To provide a precise description of how to use formulas in queries with two structures, we start by defining a notation.
Let a query contain two structures, X and Y:
X contains the element X1, …, Xm and is displayed in the columns.
y contains the element Y1, …, Yn and is displayed in the rows. Y contains the key figures.
The intersection of two structural components Yi and Xk are cells, denoted by Yi/Xk = cik. However, the system ignores these values. In the cell editor in Query Designer, you can define these cells explicitly as exceptional cells. Exceptional cells can be of type Cell Reference, Selection or Formula.
The table below shows cells that are created when the two structures X and Y are used:
Y/X |
X1 |
… |
Xi |
… |
Xm |
Y1 |
C11 |
|
C1j |
|
C1m |
… |
|
|
|
|
|
Yi |
Ci1 |
|
Cij |
|
Cim |
… |
|
|
|
|
|
Yn |
Cn1 |
|
Cni |
|
Cnm |
Let Yn = F = F(Y1, …, Yk) is the carrier of the formula group, meaning that k < n and Yn is and input-ready formula. Operands Y1, …, Yk have one of the types names just mentioned under rule FG1. As the notation shows, the input-ready operands all have to be contained in row structure Y. This gives us the first rule:
C1: If a query contains two structures and no exceptional cells, input-ready formulas and inverse formulas can only be defined in one structure, meaning that all input-ready formulas and inverse formulas have to be contained in the key figure structure. |
Rule C1 is a paraphrase of rule FG3.
The actual formulas to be calculated in the cells in row Yn for column Xj in the table above can be obtained by replacing the operands Y1 … Yk with the corresponding cells c1j …ckj. The same applies for inverse formulas.
Example
Let n = 3 and Y3 = Y2 / Y1, where Y3 is Average Price, Y2 is Sales and Y1 is Quantity.. Let m = 3 and X1, X2 be of type Selection, containing restrictions by characteristic Process Type such as Sales Order and Billing Data in a Profitability Analysis scenario. X3 contains the formula X3 = X1 + X2.
Y/X |
X1 |
X2 |
X3 = X1 + X2 |
Y1 |
C11 |
C12 |
C13 = C11 + C12 |
Y2 |
C21 |
C22 |
C23 = C21 + C22 |
Yn |
C31 = C21 / C11 |
C32 = C21 / C12 |
1. C33 = C13 / C23 2. C33 = C31 + C32 |
As this example shows, we have to define whether intersections between input-ready formulas and reporting formulas can be input ready, and whether inverse formulas can be calculated there.
Using a more general notation, let Xm be a formula. A rule of precedence needs to be defined here for the intersections Yn = F and Xm. If a rule is not defined, the system takes the structure element that was saved most recently. On the Advanced tab for the structure element Yn or Xm, you can define whether the system takes this formula or the formula from the other structure for the calculation.
Yn is an input-ready formula however, and inverse formulas exist for the input-ready operands Y1…Yk. As shown in the example, it makes no difference whether the system calculates the formula from Yn or from Xm at the intersection of Yn and Xm. As Xm is a reporting formula, however, and has no inversion, inversion is not possible here. This gives us the second rule:
C2: The intersection of an input-ready formula and a non-input ready formula results in a non-input ready cell. This does not depend on the formula precedence settings for the input-ready formula or the formula from the other structure. The system therefore does not use any inverse formulas for these intersections. |
No exception cells were used in the cases shown above. We now want to describe what happens if exception cells cij are used in F = F(c1j… ckj) are used, with the meaning of the variables as follows:
i = 1, …, k are the indexes of the operands for input-ready formula F that is in row structure Y.
j is the index of a structure element in column structure X.
C3: If F is an input-ready formula in key figure structure Y, and Yi is an operand of formula F in structure Y, exception cells can be defined for the intersections Yi/Xj of Yi and Xj according to the following rules, so that the input-ready formulas cannot be reset: ... 1. If Yi is not input ready, exception cell cij can be either a non-input ready selection or a non-input ready formula 2. If Yi is an input-ready formula (or nested input ready formulas), rule 1 (above) is applied to the operands of Yi. This is possible because the operands of Yi are also contained in key figure structure Y (see rule FG3). |
With regard to rule FG3 there is another case:
C 4 If there is no input-ready formula in the key figure structure, an input-ready formula F can only be defined in an exception call. All operands of this formula then also have to be exception cells. Rules FG 1, FG 2, and FG 3 then also apply for formula F if the following replacements are made: ... 1. Basic or restricted key figure is to be replaced by an exception cell or cell reference of type Selection 2. Non-input ready formula is to be replaced by exception cell or cell reference of type (non-input ready) formula |
Comments
● The settings made for input-ready calculated key figures in Query Designer can be overwritten by other settings at runtime. At runtime, input readiness for calculated key figures is inherited from the operands in the carrier of the formula group. If all operands in an input ready formula are not input ready for example, this means that the formula cannot be input ready either.
● A formula as carrier of a formula group does not have its own disaggregation setting. If values for input ready formulas are changed, the system always calculates back the underlying basic or restricted key figures. Note that the latter might actually have disaggregation settings.. The disaggregation behavior of input-ready key figures is therefore implicitly defined by the basic key figures in the formula definition.
You are advised to avoid using nested formula groups if possible, as the end user will probably find it difficult to understand the calculation order.
Note that that system rounds up values at runtime when calculating input-ready and inverse formulas. The display settings do not affect this. This is always performed in accordance with the maximum precision specified by the relevant fields on the database.
Special Formula Group for the %GT Percent Function
The %GT percentage function (percentage of grand total) is a special formula group that no inverse formulas have to be defined for. If formula %GT(op) is used, operand op must be input ready, and either a basic or restricted key figure with disaggregation activated. Input-ready formulas containing the %GT percentage function must not be nested with one another.
Calculation Mode and Formula Priority
The calculation mode defines how the system should start calculating inverse formulas. You can set the calculation mode in BEx Query Designer in the query properties on the Planning tab page. The following options are available:
Asymmetrical and Symmetrical Calculation Mode
Calculate Inverse Formulas |
Description |
The flag is not set |
Initial calculation mode: This is the default setting. At query runtime, the system calculates inverse formulas if at least one value in an input-ready formula has been fixed or changed manually. |
The flag is set |
Symmetrical calculation mode: At query runtime, the system calculates inverse formulas if at least one element in an input-ready formula has been fixed or changed manually. |
Depending on the calculation mode chosen, you can set the formula priority for all inverse formulas in an input-ready formula as follows:
Formula Priority
Calculation Mode |
Description |
Initial Calculation Mode |
Under the input-ready formula, you can find all inverse formulas that have been created for the input-ready operands of the carrier formula. The order in which the inverse formulas in this list are processed is determined by the formula priority. The highest element has the highest priority. |
Symmetrical Calculation Mode |
Under the input-ready formula, you can find all inverse formulas that have been created for the input-ready operands of the carrier formula, together with the carrier formula. The order in which the inverse formulas in this list are processed is determined by the formula priority. The highest element has the highest priority. Unlike in initial calculation mode, where the carrier formula always has the highest priority, this means that the carrier formula can have any priority, even the lowest. |
If the system calculates a formula group at runtime, and neither manual entries nor fixed cells nor previous calculations clearly indicate the order in which the inverse formulas should be calculated, the system first calculates the inverse formula with the highest priority.
Note for Calculation
The Note for Calculation is a property of the formula group. If you use the same operands in various input-ready formulas, the order in which the formula groups are supposed to be processed might not be obvious. You can avoid this problem by setting the priority for the formula group by entering integers in the Note for Calculation field. The lower the value entered here, the higher the formula group's priority. The same value can be entered for multiple formula groups. The values entered for various priorities do not necessarily have to be consecutive numbers.
The Note for Calculation should be understood as a relative setting rather than an absolute one. How this setting defines the order in which the formula groups are processed also depends on the context, for example on the changed, fixed or calculated cell values. Normally, it is not necessary to set the priority of the formula group in Note for Calculation.