1438520 - Incorrect subtraction formula results when both numbers are equal

SAP Knowledge Base Article - Public

1438520 - Incorrect subtraction formula results when both numbers are equal

Symptom

  • Result of a formula not equal to zero
  • A simple subtraction formula does not return zero when both values are equal. 
  • Example: The expected result of 5 - 5 is zero, but if we compare the result of the formula to zero it is not equal, why?

Environment

  • Crystal Reports XI R2
  • Crystal Reports 2008
  • Crystal Reports 2011

Reproducing the Issue

  1. Create report against Customer and Order table
  2. Add "Order Amount" field to Details section 
  3. Add Group by Country 
  4. Add Running Total for "Order Amount" field with reset on change of group to Details section                                      
  5. Create a currency parameter {?Value}         
  6. Add a TEST formula to Details section                      
                                                                                                                                       
    {#RTotal0}-{?Value}=0                         
                                                                                                                                      
  7.  Add TEST2 formula to Details section                               
                                                                                                                                       
    {#RTotal0}={?Value}                                       
                                                                                                                                       
  8. Refresh the report and enter an existing value for parameter. For example 16794.25                                                     
  9.  See the relevant record returns FALSE for TEST and TRUE for TEST2                                                                
                                                                                                                                       
    It means A=B, but for the same record A-B is different than zero
                                                                                                                              

Cause

  • A 10 -12 fractional component has been added to the summary. As the result subtraction is not equal to zero.
  • The reason this is happening is because real numbers in computer are represented in binary float point format. So some decimal number such as 0.1 can’t be represented exactly, so it is approximately 1.10011001100110011001101 × 2-4, and when performing a calculation like: subtraction, addition, multiplication and division, precision can be lost and result in values that appear to be equal are not when performing a calculation on it.
  • For more information consult: What Every Computer Scientist Should Know About Floating-Point Arithmetic

Resolution

  • The solution is to use the ROUND function to keep the desired precision and eliminate a fractional component

Example: 

Round({@Formula}, 9) // round the summary with 10 -9 accuracy       

Keywords

total, summary, zero, incorrect, fractional, decimal , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports 2011, feature pack 03 ; SAP Crystal Reports XI R2