- This article provides detailed information for users attempting to build their own calculations with the compensation and varpay programs.
Note! Customer Support is typically unable to troubleshoot your custom calculations when you break these or are unable to create a valid formula in your program. Formulas are by nature complex and unique to your program and should be developed with assistance from a paid Partner or Professional Services engagement.
- What are the supported functions within the product?
- 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.
- 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 that "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,value1,if(cond2, value2, value3)) to build more complex logic, etc.
- Please note that support will be unable to provide assistance on the following functions. This is out-of-scope for regular support via the ticket system. 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 that 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>) |
if(<item>, <item>, <item>) |
toDate(<item>, <item>) |
dateDiff(<item>, <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 := %
or := ||
not := !
less := <
greater := >
lessequal := <=
greaterequal := >=
equal := =
notequal := !=
rounding-mode := 'up' |'down' | 'halfUp'
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, outputIndex 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: date operation should always use date functions, do not directly operate on other operators.
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
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
- Here is an example of a complex formula using both lookup tables and nested if statements:
if(customSalaryFrequency='Monthly', (customMonthlyPartTimeRate/lookup('Market_Index', customJobCode, 1)), if(customSalaryFrequency='Annual', (curSalary/lookup('Market_Index', customJobCode, 1)),(curSalary*lookup('Hours Per Month', customMarketIndexCode, 1)*customGuranteedMonths)/lookup('Market_Index', customJobCode, 1)))
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
KBA , sf compensation manage data , LOD-SF-CMP , Compensation Management , How To