Formula Extension: Examples
In this section, you find different examples for the use of the formula function. In addition to that there is one other source of information:
The
The formula editor for the parameter groups of formula functions is able to transfer the formula statements of any parameter groups available in the system into the current parameter group. As a result of this, you can use the delivered functions and adjust them for your purposes.
Revenue Planning (Prices as Transaction Data)
Planning of customer-related sales is demonstrated in this example. The prices are stored in the transaction data (key figure
PRICE). Characteristics to be changed are "Key figure name", version (0VERSION), fiscal year (0FYEAR), and customer (0CUSTOMER). You include the characteristic article in the list of condition fields, and restrict the parameter group to the desired article. Prices are already stored in version 2, which should be posted to the prices to be planned in version 1.The existing data records in version 2 have the special feature that the characteristics customer and fiscal year have the value
# (not assigned): This means the prices are valid globally and are not customer-related. For this reason, both characteristics must be included in the quantity of characteristics to be changed (alternatively, it would be conceivable to conduct a corresponding object in version 2, for every object to be planned in object 1, using which the price can be determined). If no price is planned for an article, a warning is issued. The calculation is only executed if in version 1 for the combination of customer and fiscal year, a sales volume is already planned, which is greater than 0.The example is based on the assumption, that the planning package contains few fiscal years, but many customers. In such a case, you can improve the runtime of the formula function, by running the external loop through the fiscal year and the inner loop through the characteristic customer.
DATA CUSTOMER TYPE 0CUSTOMER.
Revenue Planning (Prices as Master Data Attribute)
Different from in the previous example, in the following section it is assumed that the prices are conducted as an attribute to the characteristic article, and are therefore a part of the master data. In this case, you use the ATRV function to calculate the value of the attribute. The function expects the name of the attribute to be read (for example, 0PRICE) as parameter, and a variable, which refers to the characteristic, to which the attribute belongs (for example, 0ARTICLE). The operand for this formula has the form { key figure name, article }.
The attribute value of a characteristic that is compounded to a higher-level characteristic, can only be determined with the
DATA ARTICLE TYPE 0ARTICLE.
Proportional Planning
In the following example, the revenue per customer is planned by first aggregating the values available in version 2 for the customer-related sales and revenue to two totals. The planned revenue results by then multiplying the sales quantity per customer, which was already planned in version 1, with the quotients of total revenue and total quantity. The operand for this formula has the form { key figure name, version, customer }.
DATA TOTALREV TYPE F.
DATA TOTALQUANT TYPE F.
DATA CUSTOMER TYPE 0CUSTOMER.
Copying in Dependency on Key Figure Value
The following formula copies the revenue from version 1 to version 2 under the condition that the revenue in version 1 is greater than 500. Here you include the characteristic version in the list of fields to be changed and the generic entry "key figure name" in the list of condition fields. In the parameter group for the formula function, restrict the key figure to be edited to the revenue. { Version } is simply used as the operand of the formula.
The planning function type copying is not suitable for this application, as no conditions with reference to key figure values can be formulated (you can include the generic entry "key figure name" in the list of condition fields for a copy function; however, this only means that you can control the copying for the key figures you selected separately – but not for individual values of these key figures).
IF { 1 } > 500.
Copying with Condition and Iteration over Key Figure Name
Different from the previous example, this time not only the revenue should be copied from version 1 to version 2, but also the values of all key figures. Since, the condition does not change compared to the previous example (only copy, if the revenue is greater than 500) the appropriate check remains with the
IF statement. Since this time the values of all key figure should be copied, the formula operand must also contain the key figure names in addition to the version: { key figure name, version }.So that you do not have to explicitly write out the value assignment for every key figure contained in the planning level, the example shows an iteration over all key figures with the help of a variable of the type
KEYFIGURE_NAME. This solution has the additional advantage that the function adjusts itself dynamically to the changes of the key figure pool of the planning level, and must not be revised. However, the price for this is a loss in performance.DATA KEYFIGURE TYPE KEYFIGURE_NAME.
Plausibility Check
In this example it is checked whether the planned revenue (version 1) reaches at least 90% of the previous year’s realized revenue (version 2). If this limit is exceeded, then a warning is issued, from which the description of the corresponding article emerges. The formula operand has the structure { key figure name, version, fiscal year, article }.
This formula differs from all the other examples in that it is a pure check routine, which does not change any data. You can also implement functions for quality assurance using formula extensions.
DATA ARTICLE TYPE 0ARTICLE.