Print this page

Historical trending report filter Not Equals to does work as expected on Opportunity Amount when the number of decimals is above two

Knowledge Article Number 000230089
Description If the historical trending report is active and the amount field of some records contains more than two decimals, the reports that compare the historical value and the actual value might not show the expected records.

Possible scenario:
Historical trending report were enabled for the opportunity object (for more information please review the article Set up Historical Trend Reporting)
The amount field in a record is: $10,000.1234 and remain the same for 1 month.
A standard user creates a report using the Opportunities with Historical Trending report type and add the filter Amount (Historical): 1 Week Ago equals "Amount"
The user is not expecting this record that apparently remained the same to appear in the report, but the report shows it.
Resolution This is happening due to the rounding used in the Historical trending stored record, which is by default into two decimals and is not possible to increase.
Please note that the object where this value is stored is not accessible via workbench (and is different from the OpportunityHistory table).

The workaround would be a rounded numeric field that copy the value of the amount with only 2 decimals upon workflow field update, enable the historical trending on that field and use it for comparison.

promote demote