Creating Formulas

Objective

After completing this lesson, you will be able to create a Formula in SAP Incentive Management.

Formulas

Formulas are reusable objects that contain an expression, such as a mathematical equation. A formula can be used in compensation rules, rate tables, lookup tables, and other formulas.

Formulas can reference many other objects, such as fixed values, lookup tables, values from data fields, and other formulas. Formulas can have the following output types:

  • Currency
  • Boolean
  • Date
  • Integer
  • Percent
  • Quantity
  • String

Because you can add many types of input to a formula, the Rule Usage box displays the types of rules in which the formula can be used, depending on where the data is coming from. 

A formula, with the Rule Usage section highlighted.

Functions

Functions are predefined formulas that you can use to calculate and specify values or conditions. Types of functions include date functions, sum functions, and text functions. When using a function, you will be prompted for the parameters the function requires.

Some common functions you may use may include:

  • Concatenate Two Strings (string1, string2)
    • Combines two text strings into a single string.
    • Output type: string
    • Example: Concatenate Two Strings (Participant.First Name, Participant.Last Name)
  • isNull (string)
    • Test if a field has no value
    • Output type: Boolean
    • Example: isNull (Transaction.Channel)
  • Fiscal Date (Period Type, Period Offset, Start Date or End Date)
    • Returns the start or end date of a fiscal period, such as a month
    • Output type: date
    • Example: Fiscal Date (Month, 0, End Date)

Watch the video on Measuring Date and Time Using Functions.

Best Practices for Formulas and Functions

  • Keep formulas readable and straight forward.
  • Use the Description field to describe what the formula does.
  • Don’t leave a formula expression blank. Instead, populate with a value that returns nothing in the result, depending on the result type. For example if the formula has a Boolean result, populate the formula with false.

Exercise: Create Formulas

Business Example

In this exercise, you will create two formulas that reference a lookup table to calculate the amount of the annual bonus. Since lookup tables need an exact date, you will first create a Formula that uses the Fiscal Date function to extract the last day of the current month. You will then create a Formula that references the lookup table created previously.

ExerciseStart Exercise

Steps

  1. Create the Formula to extract the last day of the current month.

    1. Select Compensation Elements → Formulas from the Manage Plans tile.

    2. Select Create.

    3. Accept the default Effective Dates.

    4. Name the Formula F_LastDayOfPeriod.

    5. Set the Return Type to Date.

    6. Open the Window Editor.

      A Formula record, with the Window Editor icon highlighted.

    7. Choose the Function button and select function:Fiscal Date.

    8. In the Period Type parameter, enter per and select reference:Period Type from the list.

    9. In the flashing cursor after the colon, enter mo and select month.

    10. In the Period Offset parameter, enter 0 and set the unit type to integer.

    11. In the last parameter, enter End and select End Date from the list.

    12. Select the checkmark to close the window.

    13. Select Create.

  2. Create a Formula called F_Bonus_Lookup that calculates the bonus using the Lookup Table. In this formula, we will tell the Lookup Table where to retrieve the three parameters: Date, Sales Status and Bonus Level.

    1. Select the New icon and accept the default Effective Dates.

    2. Name the Formula F_Bonus_Lookup.

    3. Set the Return Type to Currency.

    4. Open the Window Editor.

    5. Select Reference and select reference: Lookup Table from the list.

    6. Enter LT and select LT_Bonus Lookup from the list.

    7. In the Date parameter, type For and select reference:Formula from the list.

    8. Enter f_ and select F_LastDayOfPeriod from the list.

      Note

      This is the formula you created in the previous step.

    9. In the Sales Status parameter, enter part and select Participant.GA1 :Sales Status.

    10. In the Bonus Level parameter, enter part and select Participant.GN1 :Bonus Level.

    11. Select the checkmark to close the window.

    12. Select Create.

    Your formula should look like this:

    A formula, with the expression highlighted.

Summary

  • Formulas are reusable objects, often used in compensation rules that contain reusable expressions.
  • Formulas can reference multiple object types, including fixed values, lookup tables, and other formulas.
  • Functions are predefined formulas for calculating values or conditions, with parameters for user-input.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn