topic Re: Additional Decimals in SUM in QlikView

Additional Decimals in SUM

gerhardl — Thu, 13 Jun 2013 07:49:07 GMT

Hi,

I found something weird that I can't figure out. Please see the attached picture.

I have a field which shows account balances. When I sum all the balances (about half a million records) I noticed that when formatted as a number, it shows more than 2 decimals. It comes to 694,082,201.58004

There should be no records with more than 2 decimals, so I tried to track down such records. I make multiple selections while keeping an eye on the sum, and got it down to about 40,000. When I make further selections the decimals are normal (2).

In the table box in the picture you can see the accounts remaining and their balances - when I send this table to excel and I autosum, it also gives me 75300995.820001 - but when I look at all the records individually none of them have more then 2 decimals!!

When in Qlikview's table box (see picture), I select all of the accounts except for the very top account (10201102080000178583) then the sum changes to a total with only 2 decimals, which tells me this account is the one with the multiple decimals, but when I select it the balance is 2361.35 with only 2 decimals.

What the hell - am I crazy? where does the 00004 come from?

Thanks,

Gerhard

Re: Additional Decimals in SUM

hic — Thu, 13 Jun 2013 07:58:51 GMT

This is normal behaviour for software that use 64-bit binary representation of numbers. Your numbers simply cannot be represented exactly by a binary number, so when you add a lot of numbers, each with a very small rounding "error", you will get this effect.

Work around 1:

use a rounding function: Round(Sum(Amount),0.01)

Work around 2:

Always use integers! If you know that all amounts are exact in cents (two decimals on the dollar) then you can convert everything to cents before you sum.

See more on

Microsoft knowledge base

Binary floating point and .NET

these articles are about .Net, but most of it is applicable also in QlikView arithmentics.

HIC

Re: Additional Decimals in SUM

gerhardl — Thu, 13 Jun 2013 08:15:51 GMT

Thanks Henric,

I still don't get where the "small rounding error" on the individual numbers come from, since I pull this data from a text file which is already rounded to cents (2 decimals only) - but I'll read up a bit.

Re: Additional Decimals in SUM

hic — Thu, 13 Jun 2013 08:44:16 GMT

Do the following test, and you will better see what happens:

1. Create and run a script:

Load Round(Rand(),0.01) as x autogenerate 20 ;

This will create 20 rows, each with a number with two decimals.

2. Create a text box with the following expression:

=Num(Sum(x),'0.000000000000000')

Here you can see if the sum contains rounding "errors". It does so roughly every 2nd run. But if you use more than 20 rows, it will occur more often.

3. Create a pivot table with x as dimension and the following expression

Num(x,'(BIN) 0.000000000000000000000000000000000000000000000000000000000000')

Here you will see the binary representation of the numbers. You will see that these numbers are not always exact - they often have recurring sequence of numbers that are cut off in position 53. Hence, they are in fact truncated at this position.

HIC