Examples: Inverse Formula

Use

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

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')

As the 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

'Quanity'= NDIV0('Sales'/ 'Average Price')

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 format 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 calculated 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.

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 Calcualtion is used for this purpose.