Start of Content Area

This graphic is explained in the accompanying text Examples: Inverse Formulas at Runtime   Locate the document in its SAP Library structure

This chapter contains a number of examples that provide information about runtime aspects of inverse formulas. We assume that you can create input-ready and inverse formulas in Query Designer (see Input-Ready Queries, Examples: Inverse Formulas and 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.

This graphic is explained in the accompanying text

Example 2: Formula inversion triggered by manual changes

We illustrate the general rules using the example of the input-ready formula Average Price (see Example 1 above).

We assume that the initial (asymmetrical) calculation mode is used.

Here is the Average Price carrier of the formula group that contains the following elements: Average Price, Sales and Sales Quantity. In Query Designer, the following inverse formulas have been defined:

...

       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.

We assume that the symmetrical calculation mode is used.

Here is the Average Price carrier of the formula group that contains the following elements: Average Price, Sales and Sales Quantity. In Query Designer, the following inverse formulas have been defined, and the carrier formula has the lowest priority:

...

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

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

       3.      ‘Average price’ (carrier formula)

...

                            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 have been changed. This triggers calculation of the formula group. Because Quantity has higher priority, Average Price is kept, and an inverse calculation is performed on Quantity.

                              f.      We assume that the values for Quantity have been changed. This triggers calculation of the formula group. Because Sales has the highest priority, Average Price is kept, and an inverse calculation is performed on Sales.

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

Costs 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

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

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

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

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

Example 6: Percentage %GT

We provide an explanation of the runtime aspects of the percentage %GT example (see Examples: Inverse Formulas, Example 2).

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.

This graphic is explained in the accompanying text

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.

 

End of Content Area