SAP Knowledge Base Article - Public

3129698 - Sorting is not working properly for date format changed via Calculated Column

Symptom

Sorting is not working properly for date fields the format of which were changed via Calculated Column.

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Environment

People Analytics Report Story

Reproducing the Issue

  1. Change the date format to DD/MM/YYYY via Calculated columns in Report Story Query designer (please see KBA 2986093 on how to set it up)
  2.  Finish your query
  3. Try to Sort the date columns in your Table report on Report Designer page
  4. See that the date fields are not sorted properly

Resolution

The TODATE function need to be added to the Calculated column in order to be treated as Date type field and the sorting will work properly.

TODATE function: TODATE(“<string>”,”<format>”)

Example: TODATE(CONCAT([DAY_STRING], CONCAT(".",CONCAT([MONTH_STRING] ,CONCAT("." ,[YEAR_STRING] ) ) )),"DD.MM.YYYY" ) 

See Also

2986093 - How to create calculated column to produce output of DD/MM/YYYY for a date field in Report - Story

Keywords

People Analytics, Report Story, sorting, date format, date format change, calculated column, report - story, story, date field, dd/mm/yyyy , KBA , LOD-SF-ANA-SAC-QDS , Query Designer - Filter, Calculated Column, Params , LOD-SF-ANA-SAC , People Analytics (Analytics Cloud for SF) , How To

Product

SAP SuccessFactors HXM Core 2111