another (different) rounding problem

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Re: another (different) rounding problem

Postby omar » Wed Sep 20, 2017 2:48 pm

Same problem, you still have to calculate the VAT and round to whole cents...
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands

Re: another (different) rounding problem

Postby Bernd.N » Wed Sep 20, 2017 2:49 pm

You won't believe it, Harjo, but I currently wrote nearly the same:

"Looks very complicated, this matter.
Maybe one solution would be to just store everything in cents instead of Euros, so that one can use only integers." :D

omar wrote:Same problem, you still have to calculate the VAT and round to whole cents...

True, but you could use integers in JavaScript, too, which have their counterparts as integers in the database.
For calculations you would need a temporary variable that can handle decimals.

However in my feeling that is not very practical in programming, so I overall agree with Harjo.
Last edited by Bernd.N on Wed Sep 20, 2017 2:52 pm, edited 1 time in total.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 544
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: another (different) rounding problem

Postby lwjwillemsen » Wed Sep 20, 2017 2:50 pm

It's no rocket science, just maths and google on "javascript rounding".

Here our function (we extended the function with the optional parameter _round_part) :

function svy_utl_round(_val, _nr_decs, _round_part) {
if (_val === null) {
return null
}
var _res = _val
if (_val < 0) {
_res = - _val
}
if (_round_part) {
_res = Math.round(_res / _round_part) * _round_part
}
var _pow = _nr_decs ? Math.pow(10, _nr_decs) : 1
_res = _res * _pow
_res = Math.round(_res) / _pow
if (_val < 0) {
_res = - _res
}
return _res
}
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 680
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: another (different) rounding problem

Postby Harjo » Wed Sep 20, 2017 3:02 pm

lwjwillemsen wrote:It's no rocket science, just maths and google on "javascript rounding".

Here our function (we extended the function with the optional parameter _round_part) :

function svy_utl_round(_val, _nr_decs, _round_part) {
if (_val === null) {
return null
}
var _res = _val
if (_val < 0) {
_res = - _val
}
if (_round_part) {
_res = Math.round(_res / _round_part) * _round_part
}
var _pow = _nr_decs ? Math.pow(10, _nr_decs) : 1
_res = _res * _pow
_res = Math.round(_res) / _pow
if (_val < 0) {
_res = - _res
}
return _res
}


Oke Lambert, it IS rocketscience, did you try to round: 4.725 with your method?? :? it also returns 4.72 :?

and 4.725 is not the only value what is failing
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: another (different) rounding problem

Postby ROCLASI » Wed Sep 20, 2017 3:05 pm

The fact that JavaScript always treats it as a float is the issue here. Just pass it off to the database as I already posted.

Edit: or perhaps Java can do it better? Plugin idea, anyone ?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: another (different) rounding problem

Postby Harjo » Wed Sep 20, 2017 3:09 pm

ROCLASI wrote:The fact that JavaScript always treats it as a float is the issue here. Just pass it off to the database as I already posted.

Edit: or perhaps Java can do it better? Plugin idea, anyone ?

That's a solution, but how will perfom that, if you use this in a lot calculations?

So far, this method is working for me:

Code: Select all
function core_round_money(vValue) {
   if(vValue<0) {
      return Math.round((vValue - 0.00000001) * 100) / 100;
   } else {
      return Math.round((vValue + 0.00000001) * 100) / 100;
   }
}


I can't find ANY example that fails...
Last edited by Harjo on Wed Sep 20, 2017 3:13 pm, edited 2 times in total.
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: another (different) rounding problem

Postby ROCLASI » Wed Sep 20, 2017 3:11 pm

Harjo wrote:That's a solution, but not practical, you can't (or must not) with every calculation or rounding query the database)


Sorry, I fail to follow the logic here.. Not practical? Can't? Must not ?
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: another (different) rounding problem

Postby Harjo » Wed Sep 20, 2017 3:14 pm

ROCLASI wrote:Sorry, I fail to follow the logic here.. Not practical? Can't? Must not ?


I have edited my sentence :wink:
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

Re: another (different) rounding problem

Postby lwjwillemsen » Wed Sep 20, 2017 4:46 pm

Oke Lambert, it IS rocketscience, did you try to round: 4.725 with your method?? :? it also returns 4.72 :?

and 4.725 is not the only value what is failing


Thanks Harjo, did not realize that!
I have added the 0.00000001 addition after the multiplying with 100 in my function.
I believe that is numerical better.

Thanks again for the eye opener!
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 680
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: another (different) rounding problem

Postby mboegem » Wed Sep 20, 2017 5:21 pm

This works for me:

Code: Select all
function roundPrecision(_nNumber, _nPrecision) {
     _nPrecision = _nPrecision || 0;
     if(_nNumber == null) {
          return null;
     }
   
    var _nMultiplier = Math.pow(10 ,_nPrecision),
          _nRoundedNumber = +( Math.abs(_nNumber) * _nMultiplier ).toFixed( _nPrecision );
    _nRoundedNumber = Math.round( _nRoundedNumber );
   
    return _nRoundedNumber / _nMultiplier * (_nNumber < 0 ? -1 : 1);
}
Marc Boegem
Solutiative / JBS Group, Partner
• Servoy Certified Developer
• Servoy Valued Professional
• Freelance Developer

Image

Partner of Tower - The most powerful Git client for Mac and Windows
User avatar
mboegem
 
Posts: 1742
Joined: Sun Oct 14, 2007 1:34 pm
Location: Amsterdam

Re: another (different) rounding problem

Postby ROCLASI » Wed Sep 20, 2017 11:08 pm

Alright, I couldn't help myself.
Here is a oneliner based on above code examples:
Code: Select all
/**
* @param {Number} value
* @param {Number} [precision] optional value, default is 0
*/
function round(value, precision) {
    return Math.round( (value + ( (value < 0 ? -1 : 1) * 0.00000001)) * Math.pow(10, (precision||0))) / Math.pow(10, (precision||0));
}


Or the way Marc does it:
Code: Select all
/**
* @param {Number} value
* @param {Number} [precision] optional value, default is 0
*/
function round(value, precision) {
   return Math.round(Number(+ (Math.abs(value) * Math.pow(10, precision || 0)).toFixed(precision || 0))) / Math.pow(10, precision || 0) * (value < 0 ? -1 : 1);
}



And I found another one using the utils.numberFormat (but with a quirk)
Code: Select all
/**
* @param {Number} value
* @param {Number} [precision] optional value, default is 0
*/
function round(value, precision) {
    // ending on 5 doesn't seem to round up, 6 does
    return Number(utils.numberFormat(value + ( (value < 0 ? -1 : 1) * 0.00000000006), "#" + utils.stringLeft(".#############", (precision||0)+1)));
}



They all seem to work correctly with the examples in this thread.


Enjoy :)


Hope this helps.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: another (different) rounding problem

Postby lwjwillemsen » Thu Sep 21, 2017 8:45 am

Thanks everybody for your contribution!

In my bookcase I found the title "Introduction to numerical computations", James S. Vandergraft.
Nice bed time reading (again) :D
Lambert Willemsen
Vision Development BV
lwjwillemsen
 
Posts: 680
Joined: Sat Mar 14, 2009 5:39 pm
Location: The Netherlands

Re: another (different) rounding problem

Postby jcompagner » Thu Sep 21, 2017 11:21 am

Servoy could potentially have a round() function
that uses then BigDecimals (and that is what you have to put directly in the record WITHOUT touching it again...

And then the database should be configured as DECIMAL/NUMERIC not float/double

but the problem is then what Harjo already pointed out a bit if you do a lot of calculations in js then all those will be done based on floating point numbers
So this would only be something that rounds "the final" floating point result.

for example if you have a lot of order lines and you display the BTW/VAT per line and everything is calculated and then the total price and the total BTW/VAT is shown.
then what do you do to get the total BTW/VAT?

is that
1> sum all the none rounded values of the lines to a total (or calculate the total price and multiply that that should have the same result)
2> sum all the rounded values

problem with both scenario's that after you round then, that if you then take your calculator that it doesn't add up
with <1> is it is possible that the sum of all the rounded values are bigger or smaller then the total that you show
with <2> that is fixed but then if somebody will really calculate the total price (without vat) * by vat that that doesn't end up (this sound more like a real problem to me, that number should be valid i guess)

So i guess you do <1> and then add a +0.01 or a -.10 somewhere so it really adds up?
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8828
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: another (different) rounding problem

Postby omar » Thu Sep 21, 2017 11:43 am

Normally I would sum the amounts excluding VAT and calculate the VAT over that. That's how you fill it in in your tax reports which don't allow you to include cents anyway. Discrepancies are unavoidable. But we should distinguish between mathematical rounding issues and financial administration challenges. Mathematical rounding should always be correct in my opinion.
Intrasoft, Founder
Omar van Galen
omar@intrasoft.nl
+31-(0)6-21234586
Servoy Developer
omar
 
Posts: 377
Joined: Sat Feb 12, 2011 4:51 pm
Location: Intrasoft, The Netherlands

Re: another (different) rounding problem

Postby Harjo » Thu Sep 21, 2017 11:46 am

omar wrote:Discrepancies are unavoidable. But we should distinguish between mathematical rounding issues and financial administration challenges. Mathematical rounding should always be correct in my opinion.


Exactly!! :!:
Harjo Kompagnie
ServoyCamp
Servoy Certified Developer
Servoy Valued Professional
SAN Developer
Harjo
 
Posts: 4321
Joined: Fri Apr 25, 2003 11:42 pm
Location: DEN HAM OV, The Netherlands

PreviousNext

Return to Programming with Servoy

Who is online

Users browsing this forum: Bing [Bot] and 9 guests