Solved: Weird ROUND() - Qlik Community

Report Inappropriate Content · ‎2010-04-13

I want to round number:

=num(round(39965.666666666999, 0.1), '#,###.###############')

My expectation is to get 39965.7, but the reality is 39,965.700000000004000

Is it bug or feature?

OC: Win Server 2008 EE

QV: 9.00.7440.8 SR3 x64

Does anybody have any idea how to get the proper result for the round?

johnw · ‎2010-04-16

Calculate your calendar DateHour the same way, and they'll match.

In reality:

1 * 3 / 3 = 1
1 / 3 * 3 = 1

In a computer:

1 * 3 / 3 = 1.00000000
1 / 3 * 3 = 0.99999999

That's exactly what you're doing. You're calculating the "same" value in several different ways, and thinking that somehow they're going to match. They're not going to match. You're using a computer. Computers have limited precision regardless of what base they're using, be that binary or decimal or hexadecimal or whatever. Any fraction that can only be represented as a repeating decimal in your chosen base is not going to have an exact representation in a computer.

To solve your problem, you have two options that I can think of. First, you can stick with integer arithmetic for 100% reliability. You say you can't do that, but you can. Seconds are integers. That's what your source data was in. You can preserve that, and only calculate things like timestamps on the fly using the 100% accurate seconds. That's very likely overkill for your situation, though. If your problem is simply that you have multiple storage values for the same fundamental time, you just have to calculate all your times the same way. If you're going to have a Timestamp field, and a DateHour field, and another DateHour field in a calendar, calculate them the same way, and the "incorrect" values will all match, and will therefore not cause you this problem, or any other practical problems that I've thought of.

See attached.

View solution in original post

amars · ‎2010-04-13

Use this one

=num(round(39965.666666666999, 0.1), '#,###.#')

Report Inappropriate Content · ‎2010-04-13

Yes. It changes the presentation of the number for End user interface, but not the number itself. Check this:

=num(num(round(39965.666666666999, 0.1), '#,###.#'), '#,###.###############')

The result is still the same: 39,965.700000000004000

amars, try this:

= 'num(round(39965.666666666999, 0.1), "#,###.#") is' & if(num(round(39965.666666666999, 0.1), '#,###.#') = 39965.7, '', ' not ') & ' a good approach for rounding numbers'

johnw · ‎2010-04-13

It's a bug. Have a look at the following Wiki for an explanation of exactly what's going on, why QlikView can't be expected to fix it any time soon, and what you can do about to make your application behave correctly:

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

Let me know if you need any help getting the suggested workaround from the Wiki to work in your own application.

Report Inappropriate Content · ‎2010-04-15

This explanation is good. But how can you explain this:

=num(39965 + 16/24, '#.###############') & '\n' &
num(round(num((39965 + 16/24)*100),1) , '#.###############') & '\n' &
num(round(num((39965 + 16/24)*100),1) / 100, '#.###############')

39965.666666666664000
3996567.000000000000000
39965.669999999998000

I'm just trying to make some workaround.

Report Inappropriate Content · ‎2010-04-15

Or this:

=num(39965 + 16/24, '#.###############') & '\n' &
'DIV = ' & num((div(round(num((39965 + 16/24)*100),1), 100))
, '#.###############') & '\n' &
'MOD/100 = ' & num((mod(round(num((39965 + 16/24)*100),1), 100)
/100), '#.###############') & '\n' &
'DIV + (MOD/100) = ' & num((div(round(num((39965 + 16/24)*100),1)
, 100)) + (mod(round(num((39965 + 16/24)*100),1), 100)/100)
, '#.###############')

39965.666666666664000
DIV = 39965.000000000000000
MOD/100 = 0.670000000000000
DIV + (MOD/100) = 39965.669999999998000

It's making me crazy. QlikView is so smart to prevent simple operations to combine all together for complicated optimization latter.

johnw · ‎2010-04-15


Nick Bor wrote:
This explanation is good. But how can you explain this:<blockquote>=num(39965 + 16/24, '#.###############') & '\n' &
num(round(num((39965 + 16/24)*100),1) , '#.###############') & '\n' &
num(round(num((39965 + 16/24)*100),1) / 100, '#.###############')<pre>

39965.666666666664000
3996567.000000000000000
39965.669999999998000

I'm just trying to make some workaround.

It's the same explanation. Floating point binary can accurately represent integers, so it can accurately represent 3996567. Floating point binary cannot always accurately represent decimals and fractions, and it cannot accurately represent either 2/3 or 0.67.

2/3 is this repeating decimal in binary (unlerlined part repeats):

.10

0.67 is this repeating decimal in binary (underlined part repeats) (if I did my math right):

.10101011100001010001111

In other words, just like 2/3 has no EXACT representation in a terminating decimal (0.3), it also has no EXACT representation in a terminating binary, and neither does 67/100.

But the point, at least for getting accurate data in your application, is that it usually doesn't matter how bad binary is at representing decimals as long as you always use INTEGER artithmetic, and ONLY convert to a decimal at the last possible moment, when you're ready to display. The "rounding error" will then only occur on what is displayed, not what is stored or used in calculations, so such errors will not accumulate, and will generally be down in decimal positions that are not displayed.

Report Inappropriate Content · ‎2010-04-15

I cannot work with this number like a INTEGER. I need a calendar with hour granularity and this (39965 +16/24) is nothing else as date 1/6/2010 16:00:00. And when I load data from database all dates are with seconds, and I need to round or floor it to an hour. I've already found the solution for this like:

=(date#(date(39965+16/24+ 13/24/60+ 21/24/60/60
, 'M/D/YYYY hh:mm') & ':00', 'M/D/YYYY hh:mm:ss'))

But it's slow down my loading process.

According to binary logic, you can find my another example where all data converted to INTEGER. And MOD(x)/100 returns 0.670000000000. But when I do summary DIV(x) + MOD(x)/100 or in another word INTEGER + 0.67 I'm getting INTEGER.WEIRD NUMBER instead of INTEGER.67

johnw · ‎2010-04-15

So all you're trying to do is convert seconds from your source data into timestamps for display?

In that case, I don't think there's a problem. Yes, in a certain sense, "June 1, 2009 at exactly 4:00 PM" should be EXACTLY the number 39,965.66666666666666666666666666666666666666666666666666666666666666666666666666... repeating the 6 forever. This can't be stored in decimal, binary, hexadecimal, or any other format normally used on computers that I'm aware of. But who cares? So far as I can tell, the "incorrect" value of 39,965.66666666666400 is simply QlikView's internal storage value for this EXACT timestamp.

Are you actually having any problems? Or did you see the "wrong" number, and conclude that there WAS a problem?

Report Inappropriate Content · ‎2010-04-15

This is the problem. We have two very close values for a calendar map by DateHour:

First is a result of rounding function;

Second is a result of a calendar.

And for the calendar DateHour, there is no matching.