SAP Knowledge Base Article - Public

2239440 - Create a Formula / Calculated Columns in Advanced Reporting which would show a Date + 1 Year?

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.

SAFDAFDA111111.png

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

  1. Create a new calculated column in the existing Advanced Reporting report, with datatype Number (example: Year_Add)
  2. Add the desired input date column to this calculated one

    2.png

  3. Edit the inserted column (Step 3) by double clicking it and access the 'Token Editor'

    3.png

  4. Click 'Edit' and choose 'Year' as the 'Function' in the Function Editor

    4.png

  5. Add the desired number of years to the previous column (example: 60)

    5.1.png

    5.2.png

  6. Save this column and create another calculated column of datatype Text (example: retire)
  7. Include the Date of Birth field twice, with functions Day and month applied to each of the usages

    7.png

  8. Include the text '/' (not to be confused with a division operator) between the dates. This is done with the Token Editor'

    token.png

  9. Finally include the previously created calculated column
  10. An example end result

    results.png

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

Product

SAP SuccessFactors HXM Suite all versions