Administrator Topic

Compensation Custom Formulas

Custom compensation formulas enable administrators to customize the way amounts are calculated in Adjustment Guidelines for any compensation component. Custom formulas can contain conditional statements and perform arithmetic operations using user, compensation, performance, and competency fields. The custom formulas then update compensation fields in real-time during execution of the compensation planning task.

Custom formulas can be created as a single equation or they can be based on an IF/THEN/ELSE statement that can be customized using any available tags. See Conditions for in depth information on creating a IF/THEN/ELSE condition statement.

The tags that are used in the Compensation module are managed in Compensation Tag Management. To view the available compensation tags, click the View Tags link.

Equations

The equation is built in the Equation field. The equation can be created using tags and functions (Min, Max, Avg). To add a tag to the equation, click the Import Field icon. All imported fields must be enclosed in curly brackets (e.g., {2009.REVIEW}). See Functions below for additional information on using functions. After setting the equation, click the Save icon to the right of the field to validate and save the equation. All equations must be saved before saving the adjustment guideline. Adjustment values are rounded to two decimal places during the planning task.

Dynamically update value in Compensation Task - Select this option to have the values in the formula dynamically updated as they change within the compensation task.

  • Only non-discretionary fields can be dynamically updated in a compensation task.
  • Only data that has not been submitted can be updated (i.e., data in draft status). Data that has been submitted is final.

Functions

A function can be added to a formula by clicking the Add Function icon. This opens a pop-up where you can select the appropriate function. The following options are available:

  • MIN - This selects the lowest value from the list of tags.
  • MAX - This selects the highest value from the list of tags.
  • AVG - This calculates the average of the list of tags.

When a function is selected, it appears in the text box (e.g., MAX( ; )). In the parenthesis, enter the appropriate tags, separated by a semicolon. For example, to determine the lowest performance review score the user has had over the past three years, you could use the MIN function and then add the previous three performance review tags, separated by a semicolon (MIN({2009.REVIEW};{2010.REVIEW};{2011.REVIEW})). Note: Only numeric fields can be added to these functions.

Imported fields and mathematical operations can be performed inside a function to serve as a parameter value as long as each parameter is separated by a semicolon (MAX({2009.REVIEW}*2; {2010.REVIEW}*2; {2011.REVIEW}).

Conditions

IF/THEN/ELSE statements allow you to set a condition, and if that condition is met, then Result A occurs, otherwise (else), Result B occurs. For example, IF the employee's performance review score is greater than 3/5, THEN their recommended adjustment is $2000. ELSE, their recommended adjustment is $0.

You can also add additional conditions to the statement using AND or OR logic. Adding an AND condition indicates that both conditions must be met to satisfy the criteria. Adding an OR condition indicates that either of the conditions must be met to satisfy the criteria. For example, IF the employee's performance review score is greater than 3/5 OR the employee's competency assessment score is greater than 3/5, THEN their recommended adjustment is $2000. ELSE, their recommended adjustment is $0. In this example, OR logic is used, so either of the employee's performance review or competency assessment scores must be greater than 3/5 to receive the $2000 adjustment. If AND logic were used, both of the employee's scores must be greater than 3/5 to receive the $2000 adjustment.

Adjustment values are rounded to two decimal places during the planning task.

  1. Add Condition - Click this link to select a tag to set a condition for the formula. Note: Tag names are universal for all languages. When a tag is selected, additional options appear, depending on the type of condition tag selected.
    • Branched Drop-down - Displays a drop-down list of the options available for the field. An option must be selected.
    • Checkbox - Displays the checkbox for the field, which can be selected or deselected.
    • Multiple Checkbox - Displays all checkbox options. Multiple options can be selected.
    • Date Fields - Create a condition using an operator and a specific date.
      • The following operators are available: is on, is not on, is after, is on or after, is before, is on or before.
      • After selecting an operator, a date must be entered or selected using the calendar tool.
    • Drop-down - Displays a drop-down list of the options available for the field. An option must be selected.
    • Numeric Field - Create a condition using an operator and a specific numeric value.
      • The following operators are available: equals, not equal to, greater than, greater than or equal to, less than, less than or equal to.
      • After selecting an operator, a numeric value must be entered.
    • Percentage Field - Create a condition using an operator and a specific percentage.
      • The following operators are available: equals, not equal to, greater than, greater than or equal to, less than, less than or equal to.
      • After selecting an operator, a numeric value must be entered. This value should already be in percentage form. One to three decimals may be entered, depending on the decimal precision. The decimal precision for percentages in compensation is controlled by a backend setting.
    • Radio Button - Displays all radio button options. An option must be selected.
    • Short Text Box - Create a condition using an operator and specific text.
      • The following operators are available: equals, not equal to, contains.
      • After selecting the operator, specific text must be entered. The text is not case sensitive.
  2. After completing the IF statement, you may choose to add an additional "AND" or "OR" criteria by clicking the appropriate link after the condition if necessary. This enables you to add an additional criteria to the statement using another tag. Multiple AND or OR criteria can be added, but they all must use the same logic (all AND criteria or all OR criteria).
  3. Once all necessary AND or OR criteria are added, you must specify the adjustment value for the IF portion of the statement. Click the Edit icon to the right of the THEN field. This makes the field editable. This value can be calculated using tags and functions (Min, Max, Avg). To add a tag to the equation, click the Import Field icon. All imported fields must be enclosed in curly brackets (e.g., {2009.REVIEW}). See Functions below for additional information on using functions. After setting the value, click the Save icon to the right of the field to save the value.
  4. An additional IF/THEN condition can be added to the statement, if necessary, by repeating steps 1-4. Adding an additional condition enables you to set another condition before the default result occurs. For example, IF Score A is greater than 4/5, THEN Adjustment equals 5000. Otherwise, IF Score A is greater than 3/5, THEN Adjustment equals 3000. ELSE, Adjustment equals 0. Once an IF/THEN statement is satisfied, the system does not check the remaining IF/THEN statements.
  5. After setting all of the necessary IF/THEN conditions, you must set the default result, which is the ELSE portion of the statement. Click the Edit icon to the right of the ELSE field. This makes the field editable. This value can be calculated using tags and functions (Min, Max, Avg). After setting the value, click the Save icon to the right of the field to validate and save the value. All equations must be saved before saving the adjustment guideline.
  6. Dynamically update value in Compensation Task - Select this option to have the values in the formula dynamically updated as they change within the compensation task. Note: Only non-discretionary fields can be dynamically updated in a compensation task.

Equation and Condition Statement Rules

The following rules apply to all equations and condition statements:

  • Formulas can include numeric operations (addition (+), subtraction (-), division (/), and multiplication (*)). Operators must be separated by a numeric value or tag.
  • Fields cannot be negated (made negative) by a negative sign, but numbers can. For example "{FIELD} * -3" is valid, but "-{FIELD} * 3" is not valid. To negate a field, you can multiply the field by -1.
  • Parenthesis can be used in equations, but the system must validate that the equation contains matching open and closing ends.
  • Imported fields must be enclosed in curly brackets and they must be defined in the system.
  • General compensation, base component, bonus component, and equity component fields used in custom formula equations that are not part of the planning task are treated as zero during the calculation with the exception of CURRENT component fields (e.g., Current Bonus, Current CustomBase).
  • If multiple values exist for a field, the formula retrieves the most recent value within the task's compensation period dates.
  • If a field's value is updated during the task period, it is reflected in subsequent new drafts of the task. However, all existing drafts are unaffected.
  • If a field in a condition is undefined for a user, the clause containing the undefined field as automatically false. However, this does not automatically make the entire IF statement false if there are additional clauses.
  • Invalid expressions (e.g., divide by zero) that arise in custom formula equations during a calculation cause the result of the equation to be zero.
  • For prorated compensation fields in custom formulas, only one value is retrieved for the field. If a user appears in multiple managers' compensation tasks as a result of proration, the field retrieves a value from their task only. If a user is prorated under the same manager, then the value retrieved for the field is the total prorated amount.
  • For conditions involving compensation fields, if a currency conversion is necessary, the conversion occurs before the condition is applied. The conversion rate used is the rate at the task's Start Date.

 

Last Published: 9/10/2021 If you have feedback or suggestions for an Online Help article, please select this link. © 2021, Cornerstone OnDemand