Show TOC

Defining Inverse FormulasLocate this document in the navigation structure

Use

Input-Ready Formulas, Inverse Formulas and Formula Groups

This section provides a more precise description of the definition of inverse formulas using mathematical notation.

Note

The procedure for creating input-ready and inverse formulas in BEx Query Designer is described in Input-Ready Queries.

For examples of inverse formulas, see Examples: Inverse Formulas and 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. Carriers of another formula group G, where F ≠G and G is not %GT (Percentage of Grand Total), %XT (Scale to Result Along the Columns), %YT (Scale to Result Along the Rows)

FG2: If operand opi is input-ready, an inverse formula needs to be defined in Query Designer, calculating the opi from the following operands: F, op1, ..., op(i-1), op(i+1), ..., opn.

FG3: 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), %XT (Scale to Result Along the Columns), %YT (Scale to Result Along the Rows)

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. The same rules apply for %XT and %YT.

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 Formula screen. 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 of the formula group then have to be in the reusable 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.

    Caution

    Note that input-ready or inverse formulas containing a formula of variable type Replacement Path cannot be nested, thus always allowing the system to replace the variable with the system 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 elements X1, …, Xm and is displayed in the columns.

Y contains the elements 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. These cells are always generated by the system. 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

C

nm

Let Yn = F = F(Y1, …, Yk) be the carrier of the formula group, meaning that k < n and Yn is an input-ready formula. Operands Y1, …, Yk have one of the types named above 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 the example shows, it makes no difference at the intersection of Yn and Xm whether the system calculates the formula from Yn or from Xm: Cell Yn/Xm cannot be input ready, as this would also make it necessary to have a formula inversion for this cell. 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 the formula F = F(c1j, …, ckj), 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 have one of the following types: non-input ready selection, 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:

C4: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 in the formula must be exception cells. For formula F and its operands, rules FG 1, FG 2 and FG 3 apply 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 formulas in Query Designer can be overwritten by other settings at runtime. Input-readiness at runtime is inherited from the operands of the formula group carrier. 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 behaviour of input-ready key figures is therefore implicitly defined by the basic key figures in the formula definition.

    Recommendation

    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.

    Caution

    Note that the 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 Percentage Functions %GT, %XT, %YT

Percentage function %GT (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 percentage function %GT must not be nested with one another.

The same rules apply for the percentage functions %XT (Scale to Result Along the Columnsl) and %YT (Scale to Result Along the Rows). These functions calculate the percentage of the operand, taking into account the next higher subtotal on the X (column) axis or on the Y (row) axis.

Example Let us assume that a company sells two products (product 1 and product 2) to two customers (customer A and customer B):
Table 1: Sales
Product 1 Product 2 Total Sales for All Products per Customer
Customer A 20 40 60
Customer B 80 40 120
Total Sales for All Customers per Product 100 80 180
  • The value 20 corresponds to a share of 33.3 % of the total for sales of all products for customer A (60). This is calculated by formula %XT (along the columns).
  • The value 20 corresponds to a share of 20 % of the total for sales of all customers for product 1 (100). This is calculated by formula %YT (along the rows).
Caution

If a BW hierarchy is used on the X axis, formula %XT calculates the percentage of the operand on the root node of the hierarchy and does not take into account the next parent node in the hiearchy. The same applies if a hierarchy is on the Y axis, and formula %YT is used.

Calculation Mode and Formula Priority

The calculation mode defines how the system should go about calculating inverse formulas. You can set the calculation mode in BEx Query Designer in the Query properties on the Planning tab page. You have the following options:

Calculating inverse formulas

Description

Flag 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.

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 selected, you can set the formula priority for all inverse formulas in an input-ready formula as follows:

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 in the carrier formula. The order of the inverse formulas in this list determines the formula priority. The uppermost 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 in the carrier formula, and the carrier formula itself. The order of the inverse formulas in this list determines the formula priority. The uppermost 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 the Note for Calculation.