Symptom
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.
Environment
- 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.
Resolution
With the current functionalities we cannot perform arithmetics between a date and a number.
i.e.:
- 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
Keywords
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