Examples for Formula Applications
...
1. Price planning
2. Price Planning with Prices in the Master Data
3. Proportional Planning
4. Copying with Condition on the Value of a Key Figure
5. Copying with Condition and Iteration over Key Figure Name
6. Plausibility Check of Data
7. Rolling Planning
8. Depreciation
9. Calculating with Data Type 'D'
10. Working with Character Strings
1. Price Planning
In version 1 it is planned, in version 2 the prices are stored in the transaction data. The characteristics to be changed are:
● Version (0VERSION)
● Fiscal Year (0FYEAR)
● Customer (0CUSTOMER)
The records have the special feature that the characteristics Customerand Fiscal Year have the value Not Assigned and must therefore be included in the group of characteristics to be changed. For every object to be planned in version 1, there must also be an object in version 2 from which the price can be determined. If no price is planned for an article, a notification is issued. The calculation is only executed when the combination {QUANTITY,1,FYEAR,CUSTOMER} is scheduled, therefore > 0.
DATA CUSTOMER TYPE 0CUSTOMER.
DATA FYEAR TYPE 0FYEAR.
DATA ARTICLE TYPE 0ARTICLE.
IF {PRICE,2,#,#} = 0.
ARTICLE = OBJV().
MESSAGE I001(/SEM/003) WITH ARTICLE.
ELSE.
FOREACH FYEAR, CUSTOMER.
IF {QUANTITY,1,FYEAR,CUSTOMER} > 0.
{REVENUE,1,FYEAR,CUSTOMER} = {QUANTITY,1,FYEAR,CUSTOMER} * {PRICE,2,#,#}.
ENDIF.
ENDFOR.
ENDIF.
It seems surprising that the Article (0ARTICLE) is missing from the list of characteristics to be changed as the prices that are used for calculating refer to the 0ARTICLE. If specifically including the 0ARTICLE characteristic, the example would look as follows:
DATA CUSTOMER TYPE 0CUSTOMER.
DATA FYEAR TYPE 0FYEAR.
DATA ARTICLE TYPE 0ARTICLE.
FOREACH ARTICLE, FYEAR, CUSTOMER.
IF {PRICE,2,#,#,ARTICLE} = 0.
MESSAGE I001(/SEM/003) WITH ARTICLE.
ELSE.
IF {QUANTITY,1,FYEAR,CUSTOMER,ARTICLE} > 0.
{REVENUE,1,FYEAR,CUSTOMER,ARTICLE} = {QUANTITY,1,FYEAR,CUSTOMER,ARTICLE} * {PRICE,2,#,#,ARTICLE}.
ENDIF.
ENDIF.
ENDFOR.
The 0ARTICLE characteristic is not actually necessary for the formula as the values of the characteristic are not changed. When calculating the planned revenue it is shown that the same article is referenced on both sides of the assignment operator.
In a case such as this, SAP recommends that you do not include the characteristic in the quantity of characteristics to be changed as it is not required for the calculation, but costs in performance when accessing the values. Another way of accelerating the formula is to save the value of the {PRICE,2,#,#} element in a help variable and then to work with this. If the value of a characteristic is needed to output an error message or to determine attribute values, the value can be read with the OBJV() function.
The formula can be made even shorter if the reference data is accessed explicitly. Apart from the key figure name there are no characteristics to be changed. The formula is executed for each data record. A FOREACH loop through characteristics to be changed is therefore not required.
DATA ARTICLE TYPE 0ARTICLE.
IF {PRICE | 0VERSION = 2,0FYEAR = #,0CUSTOMER = #} = 0.
ARTICLE = OBJV().
MESSAGE I001(/SEM/003) WITH ARTICLE.
ELSE.
REVENUE = QUANTITY * { PRICE | 0VERSION = 2,0FYEAR = #,0CUSTOMER = #}.
ENDIF.
2. Price Planning with Prices in the Master Data
What does price planning look like when the price is stored in the master data? We access the value of the article using the OBJV function. The value of the OPRICEattribute is read using the ATRV function. In a simple case the ATRVfunction has two arguments. The first argument must be the name of a master data attribute and the second a variable. The value of the attribute is read from the master data table using the variable value. Compound characteristics differ in the following ways:
● If the parent characteristics are for fields to be changed, the values of these function characteristics must be specified in the form of variables. The number of arguments of the function is determined in this case by how many fields have to be specified.
● If the parent characteristics are not included in the quantity of fields to be changes, the values are automatically removed from the values of the current package. The following example shows only the key figure name of a characteristic to be changed.
DATA ARTICLE TYPE 0ARTICLE.
ARTICLE = OBJV().
REVENUE = ATRV( '0PRICE', ARTICLE ) * QUANTITY.
3. Proportional Planning
Characteristics to be changed are key figure name, version (0VERSION), fiscal year (0FYEAR), and customer (0CUSTOMER).
DATA TOTREVENUE TYPE F.
DATA TOTQUANTITY TYPE F.
DATA CUSTOMER TYPE 0CUSTOMER.
FOREACH CUSTOMER.
TOTREVENUE = TOTREVENUE + {REVENUE,2,CUSTOMER}.
TOTQUANTITY = TOTQUANTITY + {QUANTITY,2,CUSTOMER}.
ENDFOR.
FOREACH CUSTOMER.
{REVENUE,1,CUSTOMER} = {QUANTITY,1,CUSTOMER} * TOTREVENUE / TOTQUANTITY.
ENDFOR.
4. Copying with Condition on the Value of a Key Figure
The characteristic to be changed is Version. The key figure name is selected as the characteristic for conditions. REVENUE was selected as the value for the key figure name. The following formula copies the revenue from version 1 to version 2 if the revenue in version 1 is greater than 500. We cannot implement the planning function of type Copy in this example as it is not possible to formulate conditions for key figure values here. It is generally an advantage to use the special planning functions rather than formulas as these are implemented more efficiently.
IF {1} > 500.
{2} = {1}.
ENDIF.
5. Copying with Condition and Iteration over Key Figure Name
Characteristics to be changed are key figure name and version. In contrast to the example above, all key figures are now copied to version 2 if the revenue is greater than 500. To avoid having to assign all key figures, we define a RATIO variable of type KEYFIGURE_NAME and iterate across all key figures of the planning level using the FOREACH construct.
DATA RATIO TYPE KEYFIGURE_NAME.
IF { REVENUE, 1 } > 500.
FOREACH RATIO.
{ RATIO, 2 } = { RATIO, 1 }.
ENDFOR.
ENDIF.
6. Plausibility Check of Data
Characteristics to be changed are key figure name, version, and article. We check whether the planned revenue has reached at least 90 percent of the revenue of version 1. If the boundary is exceeded, the system gives a warning message.
DATA ARTICLE TYPE 0ARTICLE.
DATA MINREVENUE TYPE F.
FOREACH ARTICLE.
MINREVENUE = { REVENUE, 1, ARTICLE } * 0.9.
IF { REVENUE, 2, ARTICLE } < MINREVENUE.
* The planned revenue for article &1 exceeds the predefined minimum revenue
MESSAGE I034(ZSEM) WITH ARTICLE.
ENDIF.
ENDFOR.
7. Rolling Planning
Characteristics to be changed are version and fiscal year/period. The actual data of the current period is copied to the plan version. The difference is spread across the remaining periods. The current period is determined from a variable.
DATA ACTPER TYPE FISCPER.
DATA FISCPER TYPE FISCPER.
DATA SUM TYPE F.
DATA DELTA TYPE F.
* Periods read from the variables with the technical name PERIOD
ACTPER = VARV( 'PERIOD' ).
* Get sum for weight
FOREACH FISCPER.
IF FISCPER > ACTPER.
SUM = SUM + { 1, FISCPER }.
ENDIF.
ENDFOR.
* Delta between planned value and actual value
DELTA = {1, ACTPER} - {0,ACTPER}.
* Set planned value to actual value
{1,ACTPER} = {0, ACTPER}.
* Weighted delta distribution
FOREACH FISCPER.
IF FISCPER > ACTPER.
{1,FISCPER} = {1,FISCPER} + DELTA * {1,FISCPER} / SUM.
ENDIF.
ENDFOR.
8. Depreciation
Characteristics to be changed are key figure name and fiscal year. Determines the value of the 0AMOUNT key figure for five years. Cost price is 1000. Net book value is 100. Depreciation percentage rate is 20 percent. It is straight-line depreciation. In this example the DECL (straight-line depreciation) function is not of importance, but the TMVL function. This function has the value of a time characteristic as its first argument and an offset as its second. The offset specifies which next-largest value the function should deliver. You can also transfer negative offsets. Then the corresponding smaller values are delivered. It is not possible to set the same effect with a FOREACH loop because not all years have to be included in the transaction data.
DATA YEARS TYPE I.
DATA FYEAR TYPE 0FISCYEAR.
FYEAR = VARV('ACTYEAR').
DO.
YEARS = YEARS + 1.
FYEAR = TMVL(FYEAR, 1).
{0AMOUNT, FYEAR} = DECL( 1000, 100, 20, YEARS).
IF YEARS = 5.
EXIT.
ENDIF.
ENDDO.
9. Calculating with Data Type 'D'
It is also possible to execute calculations using data type 'D'. A small example is shown below. In the FOREACH loop, the date of the first day is specified in D1 in FISPCER and the last day in D2 in FISCPER. In addition to this, the difference between D2 and D1 is shown in I1 and two days are subtracted. I1 is of type I. If the date format in D1 and D2 is invalid, the result 0 is returned. When calculating with fields of type D, the constant operands are also checked for valid date formats. Therefore we could not write I1 = D2 - D1 - 2. but have to use a help variable I2.
DATA D1 TYPE D.
DATA D2 TYPE D.
DATA I1 TYPE I.
DATA I2 TYPE I.
DATA FISCPER TYPE 0FISCPER.
FOREACH FISCPER.
* Calculate 1st day of FISCPER
D1 = C2DATE( FISCPER, S ).
* Calculate last day of FISCPER
D2 = C2DATE( FISCPER, E ).
* Calculate the difference between last and 1st day minus two days
I2 = 2.
I1 = D2 - D1 - I2.
MESSAGE I001(UPF) WITH 'DIFFERENCE' I1.
ENDFOR.
10. Working with Character Strings
In the following example it is checked if the characteristics fiscal year/period (0FISCPER), fiscal year (0FISCYEAR) and three-figure posting period (0FISCPER3) are completed consistently. Fiscal year/period is seven characters long, the first four represent the fiscal year and the characters five to seven represent the posting period. Using the SUBSTR function(characteristic, offset, length) the corresponding values are read from the value fiscal year/period. If the characteristic value of Fiscal year/period is initial, it is filled with the concatenated value of fiscal year and posting period. The CONCAT function is used for this.
DATA FISCPER TYPE 0FISCPER.
DATA FISCPER_CMP TYPE 0FISCPER.
DATA YEAR TYPE 0FISCYEAR.
DATA PER TYPE 0FISCPER3.
DATA PER_CMP TYPE 0FISCPER3.
DATA YEAR_CMP TYPE 0FISCYEAR.
DATA KYFNM TYPE KEYFIGURE_NAME.
YEAR = OBJV( ).
PER = OBJV( ).
FISCPER_CMP = CONCAT( YEAR, PER3 ).
FOREACH KYFNM, FISCPER.
IF FISCPER IS INITIAL.
{KYFNM,FISCPER_CMP} = {KYFNM,FISCPER}.
ELSE.
PER_CMP = SUBSTR( FISCPER, 4, 3 ).
YEAR_CMP = SUBSTR( FISCPER, 0, 4 ).
IF YEAR <> YEAR_CMP OR PER <> PER_CMP.
* Error message
MESSAGE E021(/SEM/003) WITH YEAR YEAR_CMP PER PER_CMP.
ENDIF.
ENDIF.
ENDFOR.