2084135 - Managing Data, Custom Calculations / Formulas and Functions - CMP & VRP

This KB article provides detailed information for users attempting to build their own calculations within Compensation & Variable Pay.

Note:

• Support is typically unable to troubleshoot any custom calculations (formula) when these are either broken or you are unable to create a valid calculation.
• Calculations are by nature complex and unique to your plan and should be developed with assistance from a paid partner or professional services engagement.
• If you do encounter calculation issues with custom formulas etc we would recommend the formula to be broken down. This will identify the potential problem.
• If the field is presenting one of the below scenarios, it means that the custom formula created is not entirely correct, like missing a function or using additional functions that are not required. If that is the case, implementation partner or professional services must be engaged to verify.
1. The worksheet is launched and the field is displayed as N/A but populated once any change is made to the form.
2. The worksheet is launched and the field is correctly populated, but switches to N/A once any change is made to the form.
• If you are not following the best practices recommended, your calculations (formulas) may behave with inconsistencies.
• It's not recommended to use formulas with blank values in between ' ' or " ". The best practice is to have a value between the quotation marks.

Important:

• When creating formulas please note it is NOT recommended to have more than 10-15 conditions when using the IF, THEN or ELSE syntax.
• If this is something you are looking to implemement we suggest using a lookup table.
• Attached to this KB article is an example of a custom formula using the IF syntax, which is not recommended.

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

• SAP SuccessFactors Compensation
• SAP SuccessFactors Variable Pay

• Formula can have arithmetic operators.
   
• Formula can have other custom or out of the box columns.
   
• Only ‘read-only’ custom columns can have formulas associated with them.  Tag is readOnly="true".
   
• To use a custom field from the employee import file within a calculation, you must add it to the form and make it visible=no if you do not want to see it. You cannot reference fields on the import file directly.
   
• In XML the tag driving formulas is <comp-custom-field-formula> within the <comp-field-definition> element.

• Cannot reference another field that is calculated. We can reference custom fields in the calculations, but not custom fields that are calculated- you need to add in the same code that is in the ref field. So if:
   
  FieldA = 1 + 2 = 3
  You cannot do this: Field B = FieldA * 20
  You must do this: Field B = (1+2)*20

• Standard Algebraic formula structure/operator syntax applies.

• You can reference fields from other tabs in the salary tab.

• The calculation engine only works in the salary tab.

Examples:

• Following is a basic example of how a formula could be used to calculate a result for a person in a specific location.
   
  if(location='BDA', curSalary/curRatio, finSalary)
   
• This formula is saying "if location = BDA then target is based on midpoint of final pay grade, else, target is based on final salary."
   
• A more complicated if() usage scenarios can be: you may use if () to conditionally reference two different lookup tables, or use another nested if() call like: if(cond1, value2, value3)) to build more complex logic, etc.

         

Supported Functions:

• Please note Product Support is unable to provide assistance on the following functions.
• This information is provided for reference only, and only for trained experienced administrators using Admin Tools Self-Service that do not require additional assistance from support to build valid functional formulas. 
• All other administrators who would like to modify their compensation or variable pay programs using custom designed formulas need to engage via a paid partner or professional services consultation.

 Formula := <item>

 Item := <number_literal> | <String-literal> | <field> | <function> | (<item>) | <item> <op> <item>

 Function := lookup (<string_literal> , <inputfields>) |

 lookup (<string_literal> , <inputfields>, <number-literal>) |

 toNumber(<item>) |

 toString(<item>) |

 if(<item>, <item>, <item>) |

 toDate(<item>, <item>) |

 dateDiff(<item>, <item>) |

 round(<rounding-mode>, <item>);

 Field := <std-field-id> | <custom-field-id>

 input_fields := <field>|<string_literal>(,<field>|<string_literal>)1-4

 Std-field-id := curSal|promo|merit|...

 Custom-field-id := (a-z)+([A-z|0-9])*

 String-literal := "any string" | 'any string' // any alpha numerical string quoted or single quoted.

 Number-literal := [0-9]*(.[0-9]*)

 Op := add | subtract| multiply | divide | mod | and | or | not | less | greater | lessequal | greaterequal | equal| notequal |

 add := +

 subtract := -

 multiply := *

 divide := /

 mod := %

 and :=&&

 or := ||

Note: define the condition seperately with or(||) and and (&&), example: if (condition =  "1" || "2") won't work and gives weird behavior for other editable fields as well. You would need to define if( condition ="1" || condition ="2"). Its better to apply same logic to every other operator as it reduces the logical error and makes reading more simplified.

 not := !

 less := <

 greater := >

 lessequal := <=

 greaterequal := >=

 equal := =

 notequal := In formulas you need to use =! while in custom validation you need to use !=

 rounding-mode := 'up' |'down' | 'halfUp'

Function Details:

  lookup('lookup_table_name', key1...key5, outputIndex) : must have a string_literal as the first argument to indicate

• the lookup table name, the lookup keys must be of string type, output Index is an integer from 1 to 5.
   
• lookup function always returns a string. In order to use it in a numeric operation, you need to wrap it with toNumber.

  toNumber(string_value): convert a number string to number, if the string is not a number string, formula evaluation will fail

  toString(number_value): converts a number to string

  if(cond, value1, value2): cond can be any expression that evaluates to true/false boolean value, value1 and value2 can be off String/Number/Date, but they must be of the same type.

  toDate(string_value, date_format): converts a date string based on the format to a date object. date_format can be a string literal like (MM/dd/yyyy) or variable.

  dateDiff(date1, date2): returns number of days that are different, the fraction of day is discarded.

NOTE

• The date operation should always use date functions. Do not not directly operate on other operators.
• toDate only accepts MM/dd/yyyy for date_format. Any other format may result in N/A or other inconsistent results

 round('up'|'down'|'halfUp', value): it will round the value to nearest integer value based on the rounding mode. 
 
 up: round away from zero
 
 down: round towards zero
 
 halfUp: default mode, round towards "nearest neighbor" unless both neighbors are equidistant, in which case round up

Examples:

Input Number	UP	DOWN	HALF_UP
5.5	6	5	6
2.5	3	2	3
2.49	3	2	2
1.6	2	1	2
1.1	2	1	1
1.0	1	1	1
-1.0	-1	-1	-1
-1.1	-2	-1	-1
-1.6	-2	-1	-2
-2.5	-3	-2	-3
-2.49	-3	-2	-2

Support for Lookup Tables

    Our calculation engine can also support references to lookup tables. Lookup tables are managed under Admin Tools, Compensation Administration, Lookup Tables

• The lookup table will be matched based on the lookup table name.
   
• The lookup key provides the match between the form values and the lookup table values.
   
• The Value is the number that is brought back to the calculation.
   
• Standard way to convert a lookup table amount to currency:
   
  Look up table doesn’t convert by default, however, you can:

  1. Define a money custom field (e.g., moneyConversionHelperField) and use importKey to import a constant amount (e.g., a large number like 10000) for each employee

  2. Then, define your formula to select the range maximum from a lookup table: moneyConversionHelperField * toNumber(lookup('2012_RANGE_MAX_LOOKUP',toString(PAYGRADE),1))/10000

If-Then-Else Calculation Rules

   Below are some tips with regards to using if-then formulas.  Make sure you keep these in mind when coding an if-then formula!

• 'if' function should have 3 parameters, if (cond, result1, result2). If parameter number is not 3, it may cause formula evaluation to fail. This will also cause formula references fields with such formulas to get the wrong result.
   
• If (cond, result1, result2): the second (result1) and the third (result2) parameters of function 'if' should have the same type. If they don’t have the same type, it will cause formula evaluation in JAVA side fails. This will also cause formula references fields with such formulas get the wrong result.
   
• More information on if-then-else rules.

custom formulas, compensation formulas, calculations, formulas, complex formulas, manage data, managing data, lookup tables, sf, success factors,formula inconsistency, tables, , KBA , sf compensation manage data , LOD-SF-CMP-ADM , Admin Tools, Settings, Permissions , LOD-SF-VRP-ADM , Admin Tools, Settings, Permissions , How To

SAP SuccessFactors Compensation all versions