Dates and Roundings in Compensation

Overview

Formulas using dates can be difficult to implement in SuccessFactors Compensation templates due to the complexity of the formula parameter.

Additionally some Employee Central fields such as Age (but also Compa-Ratio or Range Penetration and a significant number of other fields) cannot be retrieved directly from EC into a column of a compensation worksheet because they are Transient fields, which means they are calculated on the fly in EC and their data isn’t stored.

Finally, roundings in standard recommendation columns or in custom formulas are also complicated to set up either because they require XML edits or because the formulas are not obvious to configure (nested conditions).

 

 

CALCULATING A NUMBER OF DAYS BETWEEN DATES

In this 3 minutes recording we go through the steps required to build a formula that recalculates the age of an employee in a compensation worksheet using the dateDiff function. Please find more Q&A on dateDiff formulas on the SAP blog question thread here.

Formula : round(‘down’,(dateDiff(toDate(customDateOfBirth,”MM/dd/yyyy”),toDate(“09/29/2020″,”MM/dd/yyyy”))/365))

 

Note : if we need to recalculate the seniority of an employee (Time in Position is a transient field in EC and its data cannot be retrieved in comp planning worksheets) please know that we cannot bring in Today’s date dynamically. We would have to either hardcode a date in the formula as per my example above on calculating the Age (our date would need to be aligned with the Comp Cycle start date) or get the date from a lookup table (preferred solution which is easier to maintain for customers).

 

 

Recalculating proration in a custom field with dateDiff formula :

How to use dateDiff to recalculate employee proration in a custom field based on the Hire Date in EC mapped to the standard column “startDate” in the compensation template (this is useful if we would like to display the proration % for each employee but not have it impact standard money columns as per the standard proration feature) :

 

Formula :  if((dateDiff(toDate(“01/01/2021″,”MM/dd/yyyy”),startDate)/365) <= 0,1,(dateDiff(toDate(“01/01/2021″,”MM/dd/yyyy”),startDate)/365))

 

 

 

 

USING A DATE FROM A LOOKUP TABLE FOR PUBLISH BACK TO EC OR MDF

Formula : toDate(lookup(‘2021_Publish_back_settings’,EE_Country,payroll_area_code,2),’MM/dd/yyyy’)

 

 

 

BUILDING IF-THEN RULES WITH DATES

If we want to build an if-then rule where the output will always be a date (for example to handle exceptions for the effective date of the publish back to EC), then we can build our rule the following way (all input fields and the one output field are column type = Date)

Example 1 :

Example 2 : same than example 1 but with 2 override dates (for example one for merit and one for lumpsum)

 

If we want to build complex if-then rules using dates and decide whether our output is a date or any other text then we must turn all input columns as well as the output column into String type. Please see examples below.

 

Example 3 :

 

Example 4 :

 

Example 5 :

 

Example 6 :

 

 

Example 7 : If there is no date, then…

 

 

ADDING ROUNDINGS IN COMPENSATION WORKSHEETS

In both cases below (standard or custom fields), SuccessFactors always applies roundings on the local currency value of the recommendation. Which means that in the case of larger currencies (Yen, Rupies…) a lot of inaccuracy can be introduced when toggling back and forth between functional and local currency currency (the percentage may keep increasing because the system will first convert the local currency into functional currency, then round).

We recommend whenever possible to round final payout values instead of recommendation columns. And to remember to always test rounding configuration changes in Local Currency view which is the only view valid for testing due to the above explanations.

 

• Rounding in standard recommendation fields

To add rounding on all standard recommendation fields such as merit, lumpsum, extra or promotion, we need to download the template xml and add the following line to the MONEY format :

To round 1001.2 to 1100, use <comp-number-format-ext-multiple roundingMode=”up”>100</comp-number-format-ext-multiple>

To round 1001.2 to 1010, use <comp-number-format-ext-multiple roundingMode=”up”>10</comp-number-format-ext-multiple>

To round 1001.2 to 1002, use <comp-number-format-ext-multiple roundingMode=”up”>1</comp-number-format-ext-multiple>

The xml tag “roundingMode” can have the values “up” or “down” or “halfUp” (halfUp is the default prebuilt roundingMode for each format when the tag isn’t defined).

 

Please note the following :

1. this tag ONLY applies to the following fields : merit / promo / extra / extra2 / lumpsum / lumpsum2 / stockunit / stock / option / stockother1 / stockother2 / stockother3 / and custom editable fields of type Money on the worksheet.
2. this tag doesn’t round the Current Salary and Final Salary fields or any other standard read-only field of type Money. To round these fields we will need to replace them with a custom field (see below) and adjust the configuration of the publish back to Employee Central.
3. Specific Money formats can also be configured based on the paycomponent frequency of an employee (ANNUAL, MONTHLY, ANNUAL) or the currency code.

 

The recording below shows examples of roundings in standard recommendation fields :

 

 

 

• Rounding to whole numbers in custom fields (credit Phil MacGovern – SAP)

 

To round the outcome of a calculated field we need to create a new custom column of type Money and pick one of the formula below :

Rouding to nearest 5: round(‘halfUp’,round(‘halfUp’,lumpSum)/5)*5
Rounding to nearest 10: round(‘halfUp’,round(‘halfUp’,lumpSum)/10)*10
Rounding to nearest 100: round(‘halfUp’,round(‘halfUp’,lumpSum)/100)*100
Rounding Up always: round(‘up’,round(‘up’,lumpSum))
Rounding Down always: round(‘down’,round(‘down’,lumpSum))

 

 

• Rounding on the last decimal in custom fields (credit Phil MacGovern – SAP)

 

Rounding down on the last decimal defined in the column Format (for example #,##0.00 or #,##0.##) : round(“Down”,finSalary*100/unitPerYear)/100

Rounding halfUp on the last decimal defined in the column Format (for example #,##0.00 or #,##0.##) : round(“halfUp”,finSalary*100/unitPerYear)/100

Rounding up on the last decimal while keeping the number of decimals defined in the column Format (for example #,##0.00 or #,##0.##) : round(“Up”,finSalary*100/unitsPerYear)/100

 

 

 

 

 

Thank you !