SAP Knowledge Base Article - Public

2381629 - Calculated columns doing math with text fields

Symptom

Can a calculated column perform mathematical/arithmetic calculations on numbers stored in text fields?

Environment

SAP SuccessFactors Analytics, Detailed Reporting or Advanced Reporting.

Resolution

Yes, and here's how.

1. After the original field is in a query, create a calculated column.

2. Start the calculation by using if-then criteria to exclude any possible non-number values, e.g. specific text values or nulls.

3. Program in the calculation as if the text value were a number value, e.g. Rating * 20.

4. In the line with the main calculation, double-click the text value.  Apply the function 'Raw Value'.

This will allow the system to treat any number it finds as a number.  It may behave unpredictably if it finds anything which isn't a number, so ensure everything else is excluded elsewhere in the calculation with if-then clauses.

If the calculation returns no results or errors, try creating an interim calculated column with the sole purpose of converting the text value to a number prior to the main calculation, then use the interim column instead of the base field.

Keywords

KBA , LOD-SF-ANA , Analytics & Reporting (Ad Hoc, ORD) , How To

Product

SAP SuccessFactors HCM Core 1608 ; SAP SuccessFactors Workforce Analytics all versions