2950490 - Unexpected result with Calculated date columns to pull yesterday or tomorrow records - Advanced Reporting

A calculated column in Advanced Reporting is supposed to show the number of the difference between dates e.g. Last Modified Date and Today (dynamic date). However, the result is not showing the expected number. For example, Last Modified Date is 07/19/2020 and Today is 07/21/2020:

Calculation: [Last Modified Date] - [TODAY]

The expected result is -2, because the calculation is [07/19/2020] - [07/21/2020], but the report is showing -1.

SAP SuccessFactors HXM Suite

Some date fields are timestamp fields in the database (e.g. Last Modified Date) and a calculation with a dynamic date (e.g. Today, which the time is always 00:00) is resulting in a decimal number.

In this example, the actual Last Modified Date value is [07/19/2020 10:20:34] and TODAY is [07/21/2020 00:00:00]. Therefore, the result is -1,56 and Advanced Reporting is showing only as -1.

A workaround for this case is to keep the same calculated column, but in the Manage Query Filters you have to apply the following filter criteria to display only yesterday records:

        Calculated Column Name is less than "Today"
AND |
        Calculated Column Name equals to "0"

The zero in this case is the result of [Yesterday = 07/20/2020 10:20:34] and TODAY [07/21/2020 00:00:00], which is -0,56.

You can try a similar logic for tomorrow records if you are using a timestamp field and facing the same issue:

        Calculated Column Name is greater than "Today"
AND |
        Calculated Column Name equals to "0"

2587890 - Advanced Reporting displays only the date for timestamp fields (Canvas / ORD Report)

calculated column report Advanced Reporting date number difference dates tomorrow today calculation , KBA , LOD-SF-ANA , Analytics & Reporting (Ad Hoc, YouCalc, ORD) , How To