topic Rounding error in QlikView

Rounding error

Tue, 01 Dec 2009 21:23:04 GMT

Hi all,

in the attached Application you can see an analysis of the rounding error in QilikView. I've listed the numbers from 1 to 99 with 3 digits rounded to 2 digits. I've calculated the right result with a formula and added a comparison flag called R_Fehler.

The surprising point is, that there are nuber ranges with no errors (see 41 - 52).

What do you think, could this error be solved? Or do we have to live with it?

Regards Alex:)

Rounding error

Tue, 01 Dec 2009 21:47:44 GMT

dunno why but doing this:

round(round(1.265,0.001), 0.01)

results in 1.27!

Rounding error

Tue, 01 Dec 2009 21:50:47 GMT

or =round(1.265,0.01,5)

AW:Re: Rounding error

Tue, 01 Dec 2009 22:43:13 GMT

Thank you,

that is better ... but:

sum(Rundung1) =4999989.21 that is round(x,'0.01')

sum(Rundung2) = 5000000 thats the formula

sum(Rundung3)= 4999993.91 that is round(round(x,'0.001'),'0.01')

sum(Rundung4)= 4999973.15 that is round(x,'0.01',5) in this case the comparison flag is wrong but see 99.035

Regards Alex:)

AW:Rounding error

Wed, 02 Dec 2009 19:05:27 GMT

Hi all,

at the beginning I want to discuss if there is a chance in one of the next patches that QlikView rounds with the right result (ISO standard).

But now it is a search for the workaround. I Have a new solution. This one works but it is tricky.

1. Load the original Value with

Zahl*1000 as Zahl

2. To show the Value with 2 digits use the formula

round(Zahl/10)/100

Thats only a solution when you want 3 digits shown as 2. For every other count of digits you have to Load/calculate it new.

So - why can't QlikView do it right for every number of digits?

Have Fun With QV

Alex:)

AW:Rounding error

rwunderlich — Thu, 03 Dec 2009 04:19:46 GMT

You may find this excellent wiki entry from John Witherspoon helpful:

http://community.qlik.com/wikis/qlikview-wiki/explanation-of-mathematical-errors-in-qlikview.aspx

-Rob

AW:Rounding error

johnw — Thu, 03 Dec 2009 05:03:33 GMT


Alexander Schubert wrote: at the beginning I want to discuss if there is a chance in one of the next patches that QlikView rounds with the right result (ISO standard).

As I mention in the Wiki, that seems unlikely to happen in the forseeable future, but I don't work for QlikTech and haven't asked them if this is something they intend to fix, and if so, what their time table might be.


Alexander Schubert wrote: But now it is a search for the workaround. I Have a new solution. This one works but it is tricky.
1. Load the original Value with
Zahl*1000 as Zahl
2. To show the Value with 2 digits use the formula
round(Zahl/10)/100

Your workaround looks almost right. QlikView cannot always accurately store fractional data, so if your data always has three or fewer decimal places, multiplying by 1000 during the load is the way to make certain that your numbers are stored with 100% accuracy. But your rounding expression still leaves you open to errors, as round(Zahl/10) will FIRST calculate a fraction, and then try to round it. That fraction may not be correct, so you can't guarantee that the rounded result will be correct. Instead, round Zahl to the tens position, and then divide by 1000: round(Zahl,10)/1000. Your resulting number may not be exactly correct, and should not be used in further calculations if those calculations must be exact, but it should DISPLAY correctly.


Alexander Schubert wrote: Thats only a solution when you want 3 digits shown as 2. For every other count of digits you have to Load/calculate it new.

I believe the general form of the script and rounding expression would be this:

"Source Number" * pow(10,"Max Decimal Places in Source Number") as "Converted Number"

round("Converted Number","Step" * pow(10,"Max Decimal Places in Source Number"))
/ pow(10,"Max Decimal Places in Source Number")

Not that you would actually write it as pow(10,...). You'd just plug in something like 1000.