Inverse Formula
This chapter provides information about runtime aspects of inverse formulas. We assume that you can create input-ready and inverse formulas in Query Designer (see Defining Inverse Formulas)
Example 1: Average Price
As in the documentation for defining inverse formulas in Query Designer, we will begin with the Average Price example. We assume that Sales and Sales Quantity are restricted key figures (with the currency restricted to a value and an initial unit of measure for Sales and the opposite for Sales Quantity). In Query Designer, both restricted key figures use the Disaggregate Value Entered setting under Planning on Totals.
A data slice protects the products Comes after C++ and MMIX by D.E. Knuth.
As shown in the graphic below, the Tools totals row is not input ready, either for restricted key figures Sakes and Sales Quantity or for (input-ready) formula Average Price. The Average Price formula is not input ready here, as all operands of this formula are non-input ready.
Input-Ready Formulas, Inverse Formulas and Formula Groups
You can use input-ready calculated key figures in both BEx Analyser and BEx Web Analyzer.
Note that rows can only be fixed in BEx Web Analyzer.
Input-Readiness of Formulas
Calculated key figures inherit their input-readiness from their operands. If you have defined an input-ready formula F = F(op1, …, opn) in Query Designer, the system checks whether at least one operand is input ready at runtime. If this is not the case, formula F is not input ready either. If an operand opi = G is also an input-ready formula, the same rule applies.
Sequence of Calculations
Calculating inverse formulas can be seen as a kind of “inverted” reporting. As BI Integrated Planning uses input-ready queries for manual planning, the system calculates the reporting formulas for the result set that is sent to the front end. In ABAP Dynpro terminology, reporting formulas are therefore calculated in PBO (process before output). After data has been changed manually in input-ready cells in the query, a server roundtrip is triggered, known as a PAI (process after input). Inverse formulas are calculated at this point. Changes in input-ready formulas are returned to the basic key figures, while observing any disaggregation settings that have been made. This creates new delta records that are stored in the delta buffer. In the next, the OLAP processor reads the delta records in order to refresh its internal status and the result set. The reporting formulas are calculated again during this process.
For the inverse formulas, this means that there must be inversion of these input-ready formulas in the mathematical sense. Otherwise, a change in an input-ready formula would be overwritten in the next PBO after a completed PAI-PBO cycle.
The next section describes the general rules in the implemented computational model.
General Rules
Calculation of inverse formulas is only triggered by the system if cell values have been changed for input-ready calculated key figures, or if these were fixed, and other operands of input-ready key figures have been changed.
Let F = F(op1,..., opn) be an input-ready formula, and op1,..., opk with k < n be input-ready operands. In Query Designer, k inverse formulas have been created in order to calculate operands op1,..., opn:
Fi = Fi(F, op1,…, opi-1, opi+1, … , opk, … , opn) for i = 1,…, k |
If F has been changed, for example, the system needs to find the ‘best’ inverse formula Fi for its calculation. The result of Fi is passed onto opi. The new value of opi can then trigger other calculations or a disaggregation.
The system uses the following rules for formula inversion.
...
1. Formula inversions are made based on data records. The values of the characteristics in the drilldown determine the data record here. Inverse formulas are calculated for each data record for the structural components defined in Query Designer. These calculations are not affected by other data records.
2. If there are nested formula groups, input-ready formulas can be calculated back to input-ready basic or restricted key figures by inversion of other input-ready formulas. These changes trigger a disaggregation, provided that key figures use one of the disaggregation settings.
3. In a server roundtrip, no more than one inverse formula is calculated per formula group. After a calculation, all elements of this formula group are temporarily fixed, meaning that the elements of a formula group that has already been calculated cannot be overwritten by calculations from other formula groups during this roundtrip.
4. To find the inverse formula that needs to be calculated, the system first gathers the elements that trigger calculations. These are changed or fixed cell values of input-ready formulas (for each data record). If an operand in the formula group was calculated in a previous step, this operand is also treated as fixed for the new calculation, meaning that it is considered a known source. Possible targets for calculation are input-ready operands in the current formula group, which are not changed, fixed or calculated. If one of the inverse formulas cannot clearly be recognized as requiring calculation, the system takes the inverse formula with the highest formula priority defined in Query Designer.
5. Multiple formula groups can participate in the calculations triggered by manual changes or fixed cell values. As the formula priority definition relates to a single formula group, it is not possible to tell which formula group is to be calculated first. The system calculates the formula groups in ascending order following the number of “degrees of freedom”. The current degree of freedom is worked out by taking the number of operands and subtracting the number of operands already recognized. These are operands that are constants, fixed, manually changed or were calculated in a previous step. If the current degree of freedom of the formula groups is the same too, the calculation sequence remains undefined.
6. With regard to formula inversion, new rows are treated like existing ones. If the row already exists, changed or recalculated vales are aggregated by basic key figures.
The system does not try to find any old solution for the computational problem resulting from manual changes and other types of Customizing being performed on input-ready queries. The system finds a solution using the rules listed above. If this is not successful, the system informs you with an error message.
Example 2: Formula inversion triggered by manual changes
We illustrate the general rules using the example of the calculated input-ready key figure Average Price (see Example 1 above).
Here is the Average Price carrier of the formula group that contains the following elements: Average Price, Sales and Sales Quantity. The two inverse formulas below have been defined in Query Designer (see Defining Inverse Formulas):
... 1. ‘Sales’ = ‘Average price’ * ‘Sales Quantity’ 2. ‘Sales Quantity’’ = NDIV0( ‘Sales’ / ‘Average price’ ) |
a. We assume that the value for Average Price has been changed. Both inverse formulas can be used for the formula inversion. The system takes the formula with the highest formula priority, in this case the Sales formula.
b. We assume that the values for Average Price and Sales have been changed. In this case, it is clear that the system has to calculate the Sales Quantity formula.
c. We assume that the value for Average Price has been changed and that the Sales cell has been fixed. In this case, it is clear that the system has to calculate the Sales Quantity formula.
d. We assume that the value for Average Price has been changed and that the Sales cell is not input ready, for example because this key figure is protected by a data slice. In this case, it is clear that the system has to calculate the Sales Quantity formula.
e. We assume that the values for Sales or Sales Quantity have been changed. In this case, inverse formulas do not come into play. The system calculates the Average Price.
f. We assume that the value for Sales has been changed and that the Average Price cell has been fixed. In this case, it is clear that the system has to calculate the Sales formula.
Example 3: Overlapping formula groups
In this example, we use three restricted key figures Sales Plan, Sales Forecast and Sales Actual We also want to plan the percentage deviation of planned sales from forecast sales and planned sales from actual sales. We therefore create two input-ready formulas and the corresponding inverse formulas.
The input-ready formula for calculating the percentage deviation of planned from actual sales is as follows:
%Dev (P,A) = NDIV0( ‘Sales Plan’ % ‘Sales Actual’ ) |
The elements of this formula group are ‘%Dev(P,A)’, Sales Plan and Sales Actual.
The input-ready formula for calculating the percentage deviation of planned from forecast sales is as follows:
%Dev (P,A) = NDIV0( ‘Sales Plan’ % ‘Sales Forecast ) |
The elements of this formula group are ‘%Dev(P,F)’, Sales Plan and Sales Forecast.
Both formula groups contain the element Sales Plan. We assume that Sales Actual and Sales Forecast are not input ready, for example because the forecast was prepared by the planning administrator. In this case, the following elements are input ready: Sales Plan, ‘%Dev(P,A)’ and ‘%Dev(P,F)’. The system only requires inversions from ‘%Dev(P,A)’ to Sales Plan and from ‘%Dev(P,F)’ to Sales Plan.
Product |
%Dev (P,F) |
%Dev (P,A) |
Sales Plan |
Sales Forecast |
Sales Actual |
PC Medium |
0 |
10 ® 20 |
110 |
110 |
100 |
PC Small |
0 ® 10 |
10 |
220 |
220 |
200 |
PC High End |
0 ® 10 |
10 ® 20 |
330 Error |
330 |
300 |
Total |
0 |
10 |
660 |
660 |
600 |
Manual entries are represented by an arrow (®). To provide greater transparency, this table contains all of the three examples that will be explained below. However, this does not mean that the changes were made in a single interaction step.
...
1. If you change ‘%Dev(P,A)’ for ‘PC Medium’, the system first calculates the Sales Plan in the PAI (process after input) and then calculates the percentage deviation ‘%Dev(P,F)’ in the PBO (process before output).
2. If you change ‘%Dev(P,F)’ for ‘PC Small’, the system first calculates the Sales Plan in the PAI and then calculates the percentage deviation ‘%Dev(P,A)’ in the PBO.
3. If you change ‘%Dev(P,A)’ and ‘%Dev(P,F)’ in a single step for ‘PC High End’, the system attempts to calculate ‘Sales Plan‘. This is possible for one formula group. For the other formula group, however, the system also attempts to reconcile Sales Plan at the same time. This produces an error, as the other key figures are not input ready and therefore cannot be changed by calculating inverse formulas.
Apart from if you get the error described under 3, you will get the following result:
Product |
%Dev (P,F) |
%Dev (P,A) |
Sales Plan |
Sales Forecast |
Sales Actual |
PC Medium |
9.09 |
20 |
120 |
110 |
100 |
PC Small |
21.00 |
10 |
242 |
220 |
200 |
PC High End |
0.00 |
10 |
330 |
330 |
300 |
Total |
4.84 |
15.34 |
692 |
660 |
600 |
Example 4: Nested formula groups
In this example, we want to plan costs for a number of cost centers. We assume that we have two key figures: Fixed Costs and Variable Costs. Fixed Costs is not input ready, as we assume that we cannot influence these costs in this example. Variable Costs are planned. The total costs are the fixed costs plus the variable costs. We already have the total costs from the previous year in a restricted key figure Costs LY. The fixed and variable costs in this do not interest us. What we would like to do, however, is to plan the percentage deviation of the total costs from the total costs in the previous year. To do this, we can form two input-ready formulas:
‘Total Costs = ‘Variable Costs + ‘Fixed Costs’ |
The elements of this formula group are Total Costs, Variable Costs and Fixed Costs.
The other input-ready formula is:
‘%Dev(T, LY) = ‘Total Costs’ % ‘Costs LY’ |
The elements of this formula group are ‘%Dev(T,LY)’, Total Costs and Costs LY.
The two formula groups are nested with each other, as the Total Costs formula is also used in the ‘%Dev(T,LY)’ formula.
Cost Center |
%Dev (T,LY) |
Total Costs |
Variable Costs |
Fixed Costs |
Costs LY |
4711 |
10 |
110 ® 120 |
100 |
10 |
100 |
4712 |
10 ® 20 |
220 |
200 |
20 |
200 |
4713 |
10 ® 20 |
330 ® 400 |
300 Error |
30 |
300 |
Total |
10 |
660 |
600 |
60 |
600 |
Manual entries are represented by an arrow (®). To provide greater transparency, this table contains all of the three examples that will be explained below. However, this does not mean that the changes were made in a single interaction step.
...
1. If you change the Total Costs for cost center 4711, the system first calculates the Variable Costs in the PAI and then calculates back to the Total Costs and the percentage deviation with the previous year ‘%Dev(T,LY)’ in the PBO.
2. If you change the percentage deviation with the previous year ‘%Dev(T,LY)’ for cost center 4712, the system first calculates the Total Costs in the PAI, as the formula is input ready. This implicit change triggers the next formula inversion. In the result, the system also calculates the Variable Costs in the PAI. In the PBO, the system then calculates back to the Total Costs and the percentage deviation with the previous year ‘%Dev(T,LY)’.
3. If you change the percentage deviation with the previous year ‘%Dev(T,LY)’ and the Total Costs for cost center 4713 in a single step, the only input-ready operand of formula ‘%Dev(T,LY)’ will change too at the same time. This means that formula inversion is not possible for the percentage deviation with the previous year ‘%Dev(T,LY)’. The system displays error messages informing you of this.
Apart from if you get the error described under 3, you will get the following result:
Cost Center |
%Dev (T,LY) |
Total Costs |
Variable Costs |
Fixed Costs |
Cost LY |
4711 |
20 |
120 |
110 |
10 |
100 |
4712 |
20 |
240 |
220 |
20 |
200 |
4713 |
10 |
330 |
300 |
30 |
300 |
Total |
15 |
690 |
630 |
60 |
600 |
Example 5: Calculating inverse formulas and disaggregation
In this section, we will look at an even more complex example, where we change multiple values at various levels of the result set in a single step. We assume that the formulas has the highest priority.
‘Sales Quantity’’ = NDIV0( ‘Sales’ / ‘Average Price’ ) |
As shown by the arrows (®) in the table below, certain numbers on various summation levels were changed in a single step. Note that the system calculates the inverse formula for all levels first and then disaggregates changed or calculated values to the basic key figures.
Product |
Sales |
Sales Quantity |
Average Price |
PC Medium |
4000.00 |
3 |
1333.33 ® 800 |
PC Small |
4000.00 |
4 ® 3 |
1000.00 ® 500 |
PC High End |
4000.00 |
3 |
1333.33 |
Total |
12000.00 |
10 |
1200.00 ® 1000 |
These changes trigger the following calculations:
Product |
Sales |
Sales Quantity |
Average Price |
PC Medium |
4000.00 (temporarily fixed) |
4000.00 / 800 (priority) |
1333.33 ® 800 |
PC Small |
3 * 500 |
4 ® 3 |
1000.00 ® 500 |
PC High End |
4000.00 |
3 |
1333.33 |
Total |
12000.00 (temporarily fixed) |
12000.00 / 1000 (priority) |
1200.00 ® 1000 |
The intermediate result is as follows:
Product |
Sales |
Sales Quantity |
Average Price |
PC Medium |
4000.00 (temporarily fixed) |
5 (calculated) |
800 (changed) |
PC Small |
1500 (calculated) |
3 (changed) |
500 (changed) |
PC High End |
4000.00 |
3 |
1333.33 |
Total |
12000.00 (temporarily fixed)) |
12 (calculated) |
1000 (changed) |
The Sales Quantity column contains a changed Totals value and changes to values on lower levels. The system therefore starts the disaggregation of 12 – ( 5 + 3 ) = 4 for the one unchanged value of product ‘PC High End’.
As a result of calculations in the various rows, the Sales column contains temporarily fixed values and a changed value. The system therefore starts the disaggregation of 12000 – ( 4000 + 1500 ) = 6500 for the one unchanged value of product ‘PC High End’.
The result of the calculation of the inverse formulas and the disaggregation is as follows:
Product |
Sales |
Sales Quantity |
Average Price |
PC Medium |
4000.00 |
5 |
800.00 |
PC Small |
1500.00 |
3 |
500.00 |
PC High End |
6500.00 |
4 |
1625.00 |
Total |
12000.00 |
12 |
1000.00 |
Error Handling
If the system does not find any inverse formulas for calculation, it means that the entries made by the user are invalid. The system generates messages providing information about the cause of the problem, such as too many manual changes or too many restrictions from fixed values.
There are cases where all formula inversions are possible, but the subsequent disaggregtion of the values is not. This can be the case if all individual values in a subtotal, total or a hierarchy node and the subtotal, total or hierarchy node itself have been changed or calculated. The system generates messages accordingly.
Rounding Rules
Key figure values are saved in the database with a limited number of decimal places. When formulas are calculated, rounding normally takes place. Inverse formulas can be used to calculate key figures that are stored on the database. For key figures like this, every calculated key figure value or key figure value created by disaggregation is therefore rounded to the database decimals. If the system calculates aggregated values using inverse formulas and disaggregates the calculated values, serious rounding errors can occur. It is therefore important to realize that a manually changed value can deviate from the value entered after the formula inversion has been calculated and the result set updated. This also applies for fixed values.
We therefore recommend using the same number of database decimal places for all operands of inverse formulas.
Recommendations
We recommend using nested input-ready formulas. As far as possible, model input-ready formulas and formula inversions in such a way that there are not too many variants for possible calculations. The calculations made at runtime are then easier to understand.
To avoid formal division errors like divisions by zero for quotients as input-ready formulas, such as Average Price, use operator NDIV0. If no data has been planned yet for example, and the Master Data for Characteristic Relationships setting is used for a number of characteristics under Access Type for Result Values, a large number of empty data cells normally result. If you do not use data function NDIV0 here, the cells will not be input ready for Average Price. If you do use NDIV0, however, you will get the calculation NDIV0( 0 / 0 ) = 0 for these types of cells, and Average Price can be changed.
Percentage Function %GT at Runtime
We will start by discussing the runtime aspects of the Percentage example (see Defining Inverse Formulas, Example 2) and will then go on to the general rules.
Example: Percentage %GT
We assume that the amount is a basic key figure whose distribution type for disaggregation is Analog Distribution (Self-Reference).
A data slice protects the products Comes after C++ and MMIX by D.E. Knuth.
As shown in the graphic below, the total in the Percentage of the Amount with Regard to the Total (% Contribution) is not input ready, as the corresponding value is always 100%. All other cells are input ready, with the exception of the values for Comes after C++ and MMIX by D.E. Knuth and the subtotals for these products. The basic key figure Amount is protected by a data slice. In the result, input-readiness is also deactivated for Percentage (% Contribution): The “non-input ready” characteristic was inherited by the %GT formula from the Amount operand. The “non-input ready” property was also inherited from the Tools subtotal, as all children of this hierarchy node are non-input ready.
For more information about general rules on how the “non-input ready” characteristic is inherited for subtotal, totals and hierarchy nodes, see SAP Note 994853.
General Rules for the %GT Percentage Function
Input-ready formulas with the %GT percentage function (“Percentage of Grand Total”) can be used in both BEx Analyser and BEx Web Analyzer.
Note however that cells can only be fixed in BEx Web Analyzer.
At runtime, an input-ready %GT(op) formula can only really be input ready if the op Operand is input ready at runtime.
The value of %GT for the total is always 100%; meaning that the total result is not input ready. The value for %GT therefore remains at 100% if you use a filter (on the axis) for Personal Computer. Calculations with %GT are still possible if no subtotals, totals or hierarchy nodes are displayed. It is also possible to hide key figure op - Amount in our example. Operand op – Amount in our example – must be input ready though.
If both operand op – Amount in our example – and %GT are changed for the same level in a single interaction step, the system displays an error message. On the other hand, it is possible to change the total for op (Amount) and the values for %GT at a deeper level. In this case, the system takes the new value for grand total and the changed %GT value to calculate the new value for operand op (Amount) at the deeper level. These two changes trigger a disaggregation for operand op - Amount..
The grand total for Amount must not be zero. If it is, %GT cells are not input ready.
Values for %GT can also be outside the range 0 to 100, which generally produces negative values for the operand - Amount – after disaggregation.
Calculating the %GT Percentage Function in New Rows
The system also calculates inverse formulas in new rows. The produces changed values in the basic key figures: If rows already existed before, the system adds the changed values to the existing ones. There are no special checks for new rows.