Bug 18736 - Problems in order calculations (rounding errors)
Summary: Problems in order calculations (rounding errors)
Status: In Discussion
Alias: None
Product: Koha
Classification: Unclassified
Component: Acquisitions (show other bugs)
Version: master
Hardware: All All
: P5 - low critical (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Whiteboard:
Keywords:
Depends on: 13321
Blocks:
  Show dependency treegraph
 
Reported: 2017-06-06 14:36 UTC by Nick Clemens
Modified: 2017-08-06 09:11 UTC (History)
11 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:


Attachments
Bug 18736 - Problems in order calculations (rounding errors) (4.36 KB, patch)
2017-06-06 14:38 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 18736: Correctly handle rounding (17.75 KB, patch)
2017-06-06 19:11 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Nick Clemens 2017-06-06 14:36:49 UTC
The way we are calculating and/or storing our prices in acquisitions is causing some odd issues.

For example:

Place an order (no tax, let's not even deal with that right now please)
 listprice/rrp = 16.99
 discount = 42%
 quantity = 8

estimated calculated at 9.85

but order total is 78.83, but 8 times 9.85 = 78.80
Comment 1 Nick Clemens 2017-06-06 14:38:32 UTC
Created attachment 64061 [details] [review]
Bug 18736 - Problems in order calculations (rounding errors)

Unit tests to highlight the issue

To test:
Prove t/db_dependent/Budgets.t
Comment 2 Nick Clemens 2017-06-06 14:46:37 UTC
The issue we need to decide here is whether we store the extra digits and correct calculations based on these, or store the price with 2 digits precision
Comment 3 Marc Véron 2017-06-06 14:53:26 UTC
We have a similar issue with fines:
Bug 15741 - Incorrect rounding in total fines calculations 
(see comment #12)
Bug 17140 - Incorrect rounding in total fines calculations, part 2
Comment 4 Jonathan Druart 2017-06-06 19:11:33 UTC
Created attachment 64065 [details] [review]
Bug 18736: Correctly handle rounding

The values displayed should be the sum of the rounded values, not the
reverse.

In ModReceiveOrder, i am wondering if:
  tax_value_on_ordering = quantity * format(ecost_tax_excluded) * format(tax_rate_on_ordering)

is correct or if it must be:
  tax_value_on_ordering = quantity * format(ecost_tax_excluded * tax_rate_on_ordering)

Note: the second form is used in 16.06.00.042 (bug 13321)
Comment 5 Jonathan Druart 2017-06-06 19:14:35 UTC
Here is my try, for discussion.
We must understand and fix everything in one go, we need people that have been involved in bug 13321 and related to confirm the correct behaviours.
See also bug 18639.
Comment 6 Nick Clemens 2017-06-07 12:53:56 UTC
I think we shouldn't round the tax rates - they should be applied with as much precision as possible

you have one ROUD where you want ROUND

spent.pl will need an update too
Comment 7 Koha Team Lyon 3 2017-06-20 12:03:04 UTC
Hi,
what we need in our library is to have the more precise price for order and the exact invoice price. So we sould surely store precise result rather than rounded price.
But, people will not understand if we display rounded prices and if the total is calculated on prices with 4 decimals...
Perhaps, we souhld display more precise price (4 decimals ?)...

I'm not really sure to help in this discussion because I feel that I miss some technical points.

Sonia
Comment 8 Nick Clemens 2017-06-26 14:43:33 UTC
Hi Sonia,

So do your invoices from vendors include portions of a cent? 

It seems most libraries/vendors round to nearest penny in the final calculations so not rounding causes errors. If you have the opposite situation perhaps we need a system preference to control exact vs. rounded prices?

-Nick

(In reply to Koha Team Lyon 3 from comment #7)
> Hi,
> what we need in our library is to have the more precise price for order and
> the exact invoice price. So we sould surely store precise result rather than
> rounded price.
> But, people will not understand if we display rounded prices and if the
> total is calculated on prices with 4 decimals...
> Perhaps, we souhld display more precise price (4 decimals ?)...
> 
> I'm not really sure to help in this discussion because I feel that I miss
> some technical points.
> 
> Sonia
Comment 9 Marc Véron 2017-06-26 16:15:52 UTC
(In reply to Nick Clemens from comment #8)
> Hi Sonia,
> 
> So do your invoices from vendors include portions of a cent? 
> 
> It seems most libraries/vendors round to nearest penny in the final
> calculations so not rounding causes errors. If you have the opposite
> situation perhaps we need a system preference to control exact vs. rounded
> prices?
> 
> -Nick
> 
> (In reply to Koha Team Lyon 3 from comment #7)
> > Hi,
> > what we need in our library is to have the more precise price for order and
> > the exact invoice price. So we sould surely store precise result rather than
> > rounded price.
> > But, people will not understand if we display rounded prices and if the
> > total is calculated on prices with 4 decimals...
> > Perhaps, we souhld display more precise price (4 decimals ?)...
> > 
> > I'm not really sure to help in this discussion because I feel that I miss
> > some technical points.
> > 
> > Sonia

To make things a little bit more complicated: 
Switzerland and Finland round to 5 cents. 
    1,000–1,024 → 1,00
    1,025–1,074 → 1,05
    1,075–1,099 → 1,10

See: https://de.wikipedia.org/wiki/Rundung 
("Rappenrundung": 5 Rappen (cents) is the smallest coin in Switzerland)
Comment 10 Tomás Cohen Arazi 2017-06-26 17:51:07 UTC
I think prices should be stored in 4 decimal digits precision, and so calculations made. Then for display purposes we could provide a way to specify how rounding should be done (number of digits).
Comment 11 Katrin Fischer 2017-06-27 20:57:42 UTC
I am not sure if this is only a display issue. Thinking of the calculations we do like adding up the values of all orders to determine the available money. If you store exactly, display rounded, that will never 'add up correctly' and will make people wonder. I wonder how other ILS handle this?
Comment 12 Koha Team Lyon 3 2017-07-12 09:31:09 UTC
(In reply to Nick Clemens from comment #8)
> Hi Sonia,
> 
> So do your invoices from vendors include portions of a cent? 
> 
Hi Nick,
Our invoice amount looks like 1453.78 for example.

But our colleagues often say us that there's few penny difference between Koha and the invoice amount.

It seems really tricky to have the exact calculation so I suppose that rounding only at the last step of calculation (addition of prices of an invoice) could reduce rounding error ?


> It seems most libraries/vendors round to nearest penny in the final
> calculations so not rounding causes errors. If you have the opposite
> situation perhaps we need a system preference to control exact vs. rounded
> prices?

Yes, as Finland and Switserland are other rounding pratices, a syspref could be a solution...
Sonia
Comment 13 Marc Véron 2017-08-06 09:11:57 UTC
Independently of the discussion above: Typo in C4/Budgets.pm line 224

SELECT SUM(ROUD(ecost_tax_included, 2)) FROM aqorders