- SAP Managed Tags:
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.
https://youtu.be/pwWkOHcCCGI
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).
Reminder: why there is no way to do day/month/year for a single column in a worksheet when the language selected by the planner is US English. (credit Phil MacGovern).
Date fields are shown in the format specified by the language. If planners want to see the dates as day/month/year they need to switch to a different language pack than US (settings > language in top right corner), like UK English (but that effects all dates for all employees including employees in the US).
The toDate function cannot help us here. Even though it can help convert a string column to a Date column, the second parameter (MM/dd/yyyy or dd/MM/yyyy) is a descriptor of what format the string is in, not the expected outcome. If source field is a string, then with the toDate function we are saying "I have a date string in this format: dd/MM/yyyy; convert it to a Date type. So if we have 3/26/2022 in the source field it takes the 3 as the day and 26 as the month which leads to an error and a blank in the target column.
It is also currently not possible to force the format of dates on standard Compensation Statements to only show in one format (for example DD/MM/YYYY). To achieve this we would need to make sure that Dates are in the right format already in the source Compensation worksheets column which in some cases (including when the date pulled onto the statement comes from an editable date column on the worksheet - for example if manager edits the publish back date use it otherwise use default 03/01/2024 date) isn't possible at this time. The only option to meet this requirement would be to download the standard statement and turn it into a "custom" statement by editing its xsl code with a sample like below ($startDate format is defined at the top of the xsl as DD/MM/YYYY).
Example 1 :
Example 2 : same than example 1 but with 2 override dates (for example one for merit and one for lumpsum)
https://youtu.be/pdXmFo1ezWA
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 :
- 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.
- 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.
- 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 :
https://youtu.be/-PYpgP8fymU
- Rounding on custom fields (credit Phil MacGovern)
To round to the nearest 1/n (i.e. round to the nearest 1/4th (0.25) or the nearest 1/20th (0.05) or 1/2 (0.5), the formula is: round('halfUp',x*n)/n
Rounding up to nearest 0.05: round('halfUp',lumpSum*20)/20
Rounding up to nearest 0.5: round('halfUp',lumpSum*2)/2
Other frequent rounding requirements:
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))
Example 1 (rounding to nearest 5 or 10 or 100) :
Example 2: (rounding to nearest 0.5 or 0.05)
| Result of calculation | Result should be rounded to: |
| 9.28 % | 9.50 % |
| 9.70% | 10.00 % |
| 10.30% | 10.50 % |
For additional content see conversation here.
- Rounding on the last decimal in custom fields
Please note : we can only control rounding within the Number Format already defined and there is no number format for STRING so the formulas below are only relevant for Amount, Money and Percentage type of columns.
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
Business case : make sure when converting from annual to monthly that the employee doesn't lose out. Let's take an example : If we divide a salary of 70,000 by 12 we get 5,833.33333 which rounds to 5,833.33. But if we multiply that 5,833.33 by 12 then in Employee Central Pay Component Group we get 69999.96 which is lower than what was on the compensation worksheet. By doing round("up",(finSalary/12)*100)/100 then we get 70,000.08 in the Pay Component Group in EC and hence make sure the employee doesn't lose out. See this blog for more information on this topic with configuration options for permanent consistency.
https://youtu.be/JPKzzvKMRkU
--
All the best,
Xavier
(If you found this blog useful please consider giving it a Like)
29 Comments
| User | Count |
|---|---|
| 8 | |
| 3 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 2 | |
| 1 |
