SAP Knowledge Base Article - Public

2354870 - Calculated Column Formula on Date differences which contains operators or condition operators return an error

Symptom

Currently if a calculated column is created with the formula DateX minus DateY and the result divided by a number the below error is thrown:

Text formula cannot contain any operators or condition operators

Environment

SAP SuccessFactors HCM Suite

Reproducing the Issue

  1. Login to the instance
  2. Analytics - Analytics - Detailed Reporting or Advanced Reporting
  3. Create a new query or edit an existing one
  4. Create calculated column with the difference in dates formula to get the number of days difference between two dates then divide it by a number. For this example, we used Hire and Termination Date from the Employee Details object and divided by 30.
    • Calculated column on Date Difference.jpg
  5. Try to save the formula and an Error occurs: Text formula cannot contain any operators or condition operators
    • Error.jpg
  6. If you use only the dates the formula works
  7. Dates differences.jpg

Cause

The system behaviour is as designed.

The error is thrown because in the code we intentionally convert any formula that isn't all the same type into text.

Resolution

The system behaviour is as designed.

With the current functionality you can achieve the same result, as per the intial formula '(Hire Date – Termination Date) / 30', with the following approach:

  1. Create a Calculated formula such as 'Hire Date – Termination Date' and save it
    • Dates differences.jpg
  2. Create an additional calculated column (Date1) based on the previous calculated column (Date). for example, 'Date / 30'
    • date1.jpg
  3. The formula can be saved without errors.
  4. You can then decide if to display both or just one column for your report.
    • Dates.jpg
  5. You can then use the calculated column result as you wish.

Keywords

Formula, Calculated column, Text formula, Date differences, operators, condition operators , KBA , LOD-SF-ANA , Analytics & Reporting (Ad Hoc, YouCalc, ORD) , How To

Product

SAP SuccessFactors HXM Suite all versions