Is it possible to create a formula / calculated columns in Advanced reporting which would show the date + 1 Year?
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 HCM Suite
- Report - Canvas on Advanced Reporting query
Reproducing the Issue
For example, Hire date + 1 Year like the example below for excel.
With the current functionalities we cannot perform arithmetics between a date and a number.
- Date - Date is possible
- Date - number is not possible
Alternatively, if the requirement is to just add X number of years to a source column, the following workaround can be applied:
We will consider an example where we need to add 60 years to the user's date of birth
- Create a new calculated column in the existing Advanced Reporting report, with datatype Number (example: Year_Add)
- Add the desired input date column to this calculated one
- Edit the inserted column (Step 3) by double clicking it and access the 'Token Editor'
- Click 'Edit' and choose 'Year' as the 'Function' in the Function Editor
- Add the desired number of years to the previous column (example: 60)
- Save this column and create another calculated column of datatype Text (example: retire)
- Include the Date of Birth field twice, with functions Day and month applied to each of the usages
- Include the text '/' (not to be confused with a division operator) between the dates. This is done with the Token Editor'
- Finally include the previously created calculated column
- An example end result
Limitation of the workaround: Since we are generating a text field, it isn't possible to apply filters / sort the data, based in this newly generated column within SuccessFactors
date formula, date calculated column, Arithmetic on dates, add days, remove days, subtract, plus days, retirement, service anniversary, calculated , KBA , LOD-SF-ANA-ADV , Advanced Reporting (ODS) , How To