topic Re: Function Round Question. in QlikView

Function Round Question.

kentliao66 — Fri, 14 Nov 2014 02:59:23 GMT

Hi, Expert:

When we use function round, we found a strange status:

= ROUND(26364461.43,0.1)	= 26364461.4
= ROUND(26364461.44,0.1)	= 26364461.4

But when the number is 26364461.45 , the answer become 26364461.4, In my mind, it should be 26364461.5.

= ROUND(26364461.45,0.1)

= 26364461.4

When we use the follow number , 26364461.46, 26364461.47 the answer is correct "26364461.5"

= ROUND(26364461.46,0.1)	= 26364461.5
= ROUND(26364461.47,0.1)	= 26364461.5

so, is ROUND(26364461.45,0.1) = 26364461.4 correct ?

I'm using Qlikview x64 11.20.SR9 Desktop.

Many thnaks

Kent

Re: Function Round Question.

Fri, 14 Nov 2014 03:07:16 GMT

round( x [ , base [ , offset ]])

Rounding of x upwards or downwards to the nearest multiple of base with an offset of offset. The result is a number. If x is exactly in the middle of an interval, it is rounded upwards.

Examples:

round( 2.4 ) returns 2

round( 2.6 ) returns 3

round( 2.5 ) returns 3

round( 3.88 , 0.1 ) returns 3.9

round( 3.88 , 5 ) returns 5

round( 1.1 , 1 , 0.5 ) returns 1.5

it may help u

Re: Function Round Question.

kentliao66 — Fri, 14 Nov 2014 03:13:11 GMT

Hi , Manojqlik:

I know the syntax,

but the Function ROUND(26364461.45,0.1) , if follow the rule, the answer should be 26364461.5, not 26364461.4, Am I right ?

Thanks for your reply.

Kent

Re: Function Round Question.

Fri, 14 Nov 2014 03:15:02 GMT

s your right the answer is 26364461.5 only

Re: Function Round Question.

kentliao66 — Fri, 14 Nov 2014 03:24:14 GMT

Hi, Manojqlik:

so, the founction ROUND(26364461.45,0.1) the return value is wrong (26364461.4), thus, is it the bug or just I miss something ?

Re: Function Round Question.

Fri, 14 Nov 2014 03:35:17 GMT

= ROUND(26364461.46,0.1) change here .45 to .46

u will get like 26364461.5 ,

small fractions caculation ,

may be its a bug

Re: Function Round Question.

JonnyPoole — Fri, 14 Nov 2014 03:39:45 GMT

You are right Kent ... there have been inconsistencies reported before. Its inconsistent with the documentation and buggy.

num() rounds differently to round() for half-step values

Re: Function Round Question.

Anonymous — Fri, 14 Nov 2014 03:45:01 GMT

Hi Jonathan,

Unable to access the link you have provided to read about the inconsistencies for Round.

Best Regards,

Chinna

Re: Function Round Question.

kentliao66 — Fri, 14 Nov 2014 03:45:42 GMT

Hi Manojqlik:

Yes, change the .45 to .46 can fix the problem, but it raise another question, these value is in the 10 million records, if I add this "if-then" condition, the performance should be not good. thus, have any good idea ?

Best Regards...

Kent Liao

Re: Function Round Question.

Fri, 14 Nov 2014 03:55:00 GMT

No idea yar, its a bug.

jonthan provided a link based on this

i am able to see that link

http://community.qlik.com/message/423596#423596

Re: Function Round Question.

kentliao66 — Fri, 14 Nov 2014 03:56:15 GMT

Hi, Jonathan:

How do I get provided to access this link --

num() rounds differently to round() for half-step values

http://community.qlik.com/message/423596#423596

have any workaround solution ?

Many thanks.

Kent Liao

Re: Function Round Question.

JonnyPoole — Fri, 14 Nov 2014 03:57:48 GMT

This provides better context on the inconsistencies.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/17/rounding-errors

The suggestion is to avoid rounding to sub-integer fractions. Like rounding to .5 or .6 etc...

In your case, instead of rounding to a sub-integer fraction, multiply by 10 and round to an integer, than divide by 10 as follows:

= ROUND(26364461.45 * 10) / 10

Re: Function Round Question.

kentliao66 — Fri, 14 Nov 2014 04:03:32 GMT

HI, Manojqlik & Jonathan:

Thanks a lot, I'll try it.

by the way, is this Bug possible to fix, when?

Best Regards...

Kent Liao

Re: Function Round Question.

Fri, 14 Nov 2014 04:14:14 GMT

Hi kent,

that we need to ask qliktech, but i am not sure how much time it takes,

pls make this thread close