Example: Inverse Formula  

This document contains a few simple examples to show how the system calculates values in accordance with the inverse formulas defines for an input-ready formula in BEx Query Designer 

You can find the procedure for creating input-ready and inverse formulas in BEx Query Designer under Input-Ready Query. For a description including mathematical notation and detailed rules, see Defining Inverse Formulas. More information: SAP Note 1236347.

Example 1: Average Price

The first scenario is as follows: You want to plan with the following key figures: Quantity, Sales and Average Price. Key figure Average Price is calculated as follows:

‘Average price’ = NDIV0( ‘Sales’ / ‘Quantity’ )

Data function NDIV0 is used here to avoid division by 0 errors.

As key figure Average Price is a calculated key figure and should be input-ready, the system needs rules that describe how a change to the value of the average price is calculated back to either quantity or sales. These rules are defined using inverse formulas. The system needs an inverse formula for every operand in input-ready formula Average Price.

Let both operands, Quantity and Sales, be input-ready key figures. If this is the case, you need to define the following inverse formulas:

‘Sales’ = ‘Average price’ * ‘Sales Quantity’

and

‘Sales Quantity’’ = NDIV0( ‘Sales’ / ‘Average Price’ )

If only the value for the average price is changed, it is not clear at first whether the system should calculate back to Quantity or Sales. The system will then take the inverse formula with the highest priority. The priority of the formula is determined by the order of the inverse formulas in the formula group. The formula with the highest priority is at the top of the list of inverse formulas.

Example 2 - Percentage 

The second scenario is as follows: You want to plan with the following key figures: Amount and Percentage of the grand total. The percentage should also be input ready.

As with example one, this case can also be modeled with an input-ready formula and the definition of an inverse formula. You can also use the ‘%GT’ function (percentage of grand total) with a number of additional functions:

• If you use the %GT function, you do not need to create an inverse formula, as it is already defined in the system.
• The total for the operand of %GT is fixed automatically during calculation of the inverse formula. This ensures that the changed % value is still the same after the server roundtrip.

The %GT function is useful if the underlying basic key figure uses one of the supported disaggregation settings. For more information about disaggregation settings, see Input-Ready Query.

In Query Designer, define the following formula:

‘% Percentage’ = %GT ‘Amount’

Choose Input-Ready. You do not need to create an inverse formula. 

Example 3: Symmetrical Calculation Mode and Average Price  

Formulas are always calculated when the OLAP Processor calculates a new result.

If the value of an input-ready formula is changed, the value of the calculated key figure Average Price for example, the system has to calculate the inverse formula. In this case, the system calculates either the formula for Sales or the formula for Quantity.

If the value of an operand in an input-ready formula is changed however, key figure Sales for example, the system does not calculate inverse formulas. The system calculates a new average price when the result is updated by the OLAP Processor.

There are certain scenarios where a different system reaction is required. If the value of an operand such as key figure Sales is changed, the value for Average Price should be retained, and an inverse calculation performed on key figure Quantity. You can achieve this using symmetrical calculation mode.

To activate symmetrical calculation mode, select Calculation of Inverse Formulas in the Query properties on the Planning tab page in BEx Query Designer.

This mode is particularly useful for depicting the required business logic in complex scenarios that work with a large number of nested input-ready formulas. In this kind of scenario, it can be useful to be able to explicitly set the order that the formula groups will be calculated in. The Note for Calculation is used for this purpose.

Once you have activated symmetrical calculation mode, you can set the formula priority for every input-ready operand, including the input-ready formulas that use them. You do this in the query component properties on the Planning tab page. In the Note for Calculation field, you can also define the formula group’s priority in relation to other formula groups.