topic Re: Rounding off to an integer using NUM in QlikView

Rounding off to an integer using NUM

chhavi376 — Wed, 27 Jul 2016 13:16:05 GMT

I was trying to round off all my numbers to integer but i came across this problem:

if i use '#,##0.00' in num, i get these numbers:

When we round this off, it should ideally be 16, 27 and 35

But when i use '#,##0' in num, i get these numbers:

Re: Rounding off to an integer using NUM

sunny_talwar — Wed, 27 Jul 2016 15:10:44 GMT

Look here for Rounding error issues in QlikView:

Rounding Errors

Re: Rounding off to an integer using NUM

Nicole-Smith — Wed, 27 Jul 2016 15:12:40 GMT

Is 15.50 rounded? If the underlying data is actually something like 15.4991, rounded to two decimal places, it would be 15.50, but the rounded integer would be 15 instead of 16.

You could also try using round() instead of num() to get the integer value.

Re: Rounding off to an integer using NUM

chhavi376 — Thu, 28 Jul 2016 05:53:19 GMT

Hi Nicole,

I just checked the data in my reporting tables,

It is exact '15.5'

the round function also gives me 15, if i use '1'as the offset.

Re: Rounding off to an integer using NUM

Thu, 28 Jul 2016 06:59:18 GMT

Hey please Round() and dont use num() inside for that

i hope it will work

Re: Rounding off to an integer using NUM

saimahasan — Thu, 28 Jul 2016 07:07:55 GMT

Please use ceil function:

Ceil(FieldName) as FieldName

Re: Rounding off to an integer using NUM

Anil_Babu_Samineni — Thu, 28 Jul 2016 07:10:57 GMT

O/P for this

LOAD Floor(Round(Value)-1) as Value INLINE [

Value

12.6

13.8

14.5

];

Value

Re: Rounding off to an integer using NUM

gautik92 — Thu, 28 Jul 2016 07:13:14 GMT

use round()

Re: Rounding off to an integer using NUM

chhavi376 — Thu, 28 Jul 2016 07:26:42 GMT

Vijay,

As per my understanding, floor() function rounds the numbers down.

i want it to round as per the logic:

number >= x.5 gives me x+1

and number <x.5 gives me x.

and as i mentioned in the original post,

my numbers from the reporting tables were 15.5, 26.5 and 34.5

but when i round them, i get 15,27 and 35

So why is 15.5 not rounding upto 16?

I have tried using num(), round(), ceil() and now floor() as well.

But i get the same numbers-15,27,35, which are not correct

Re: Rounding off to an integer using NUM

chhavi376 — Thu, 28 Jul 2016 07:28:01 GMT

Round(), Ceil(), Floor(), Num()- doesn't work!

Re: Rounding off to an integer using NUM

chhavi376 — Thu, 28 Jul 2016 07:29:47 GMT

I checked that Sunny,

But it doesnt work.

Re: Rounding off to an integer using NUM

saimahasan — Thu, 28 Jul 2016 07:30:50 GMT

You can also use Round function:

Round(FieldName)

Re: Rounding off to an integer using NUM

oknotsen — Thu, 28 Jul 2016 08:18:54 GMT

What you seem to be doing is using a format to show the numbers. That has nothing to do with actual rounding the numbers.

Example:

15 will still show as 15.00000 if you use a format as '0.00000'

To round something, you will need to use the round() function (and yes, it actually works if you use it correct). For example:

15.496 used with round() trying to round it to whole numbers would look like round(15.496, 1).

Please note that round works slightly different in Qlik as it does in XLS or many programming languages. Round() in Qlik works with "steps of". So rounding in two decimals would look like round(15.496, 0.01)

Re: Rounding off to an integer using NUM

chhavi376 — Thu, 28 Jul 2016 08:32:00 GMT

Saima,

Ceil rounds up,

i want it to round as per the logic:

number >= x.5 gives me x+1

and number <x.5 gives me x.

Round() is also giving wrong results

Re: Rounding off to an integer using NUM

chhavi376 — Thu, 28 Jul 2016 08:34:46 GMT

Onna,

You are right.

But Round is giving me these results:

my numbers from the reporting tables were 15.5, 26.5 and 34.5

but when i round them, i get 15,27 and 35

Re: Rounding off to an integer using NUM

sunny_talwar — Thu, 28 Jul 2016 08:36:03 GMT

I guess the point of the article was that the behavior you are seeing is because of the rounding issue. You can try this

=Round(Round(Number, 0.1)), but this will not work perfectly as well.

Using the above code, you should get these

13.50 -> 14

13.46 -> 14

13.44 - > 13

Re: Rounding off to an integer using NUM

saimahasan — Thu, 28 Jul 2016 08:51:41 GMT

can you please upload your QVW

Re: Rounding off to an integer using NUM

gautik92 — Thu, 28 Jul 2016 08:55:19 GMT

for this round() should work

can yu share the script

Re: Rounding off to an integer using NUM

Thu, 28 Jul 2016 08:55:45 GMT

Can you share the code where you using, so that we can help you

Re: Rounding off to an integer using NUM

chhavi376 — Thu, 28 Jul 2016 10:06:01 GMT

These are the backend values:

This is the expression i am using:

round(Sum( {<flag={1},Products-= {'Product1'}, Metric_Type={'abc'} >} Metric_Value_N),1)

This is the output in the table: