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, YouCalc, ORD) , How To