topic Re: Rounding Issue Of Qlik in QlikView

Rounding Issue Of Qlik

Wed, 02 Sep 2015 08:19:43 GMT

Hi All,

I have a scenario where i have converted decimals from DB to integers on application.

At one scenario,

DB – 128.5 is getting converted to

Dashboard- 128

Other Scenario,

DB-2670.5 is getting converted to 2671.

Any idea why qlik treats these two numbers differently?

Khushboo

Re: Rounding Issue Of Qlik

lironbaram — Wed, 02 Sep 2015 08:26:54 GMT

i don't think it's a qlik issue

probably you see in the db a round version of real number

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 08:35:56 GMT

Liron, Thanks for your answer.

But i am still on clear on the issue.

But for Qlik , both are .5 , so why different treatment for these two numbers?

Re: Rounding Issue Of Qlik

qlikviewwizard — Wed, 02 Sep 2015 08:40:15 GMT

Hi,

Please change the number format as shown below:

Re: Rounding Issue Of Qlik

sasiparupudi1 — Wed, 02 Sep 2015 08:58:34 GMT

may be one is using floor and the other is using round?

can you attach a sample?

Re: Rounding Issue Of Qlik

qlikviewwizard — Wed, 02 Sep 2015 09:10:56 GMT

Hi Khushboo,

Please attach the sample to demonstrate the issue? Thank you.

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 09:31:36 GMT

Sample i cannot attach.

but both these values are from one single column from DB and also they have same number settings.

Both the number settings are integer.

Still one is rounding off other one is not?

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 09:56:44 GMT

In case you want it to round it off to 2 decimal places and load from DB.Try Loading

Round(FieldName,.01) as FieldName

Or if you want to round it off to next Integer like, 128.7 to 129 and 128.3 to 128- Try this

Round(FieldName,1) as FieldName

But please use Round( ) functions everywhere you are loading this field for uniformity.

Pallav

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 10:02:13 GMT

Hi Pallavi,

Thanks for your reply.

But i cant use round functions everywhere in my dashboard.

My questions whats causing this issue. if the DB has values 128.5,2670.5 .How come one gets rounded off and other Does'nt?

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 10:09:46 GMT

Probably it is loading from Db in with Decimals then rounding off on charts differently based on settings on Numbers tab. Make sure its is set to Integer or Decimal on charts based on your preference.

If you use Round() function in the loading script in the way I mentioned (while loading) and then check your Numbers tab for charts to show the field as an Integer, you should be good.

One can't be sure why it is happening, but it seems like Qlik is loading (while loading through script) a decimal data from db like ( 128.50) and it is rounding off differently on different charts based on your settings on 'Numbers' tab.

Pallav

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 10:28:58 GMT

Settings on my number tab are same.and this issue is for same column name in the DB.

say for a year 2014 , value is

128.50 which shows as 128 and for year 2015 it is 2670.5 which comes as 2671.

Still different behavior, same settings , same column?

Thanks and Regards

Khushboo

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 10:51:40 GMT

If loaded data from db (reason why I wanted to use Round() in scripts) and Settings on number tab are same, then it is hard to imagine any reason for the discrepancy.

I would have done this.

1. Use round(field, 1) while loading in script

2. Create a new chart(table box or list box or straight table with a dimension). Put this field there.

3. Set its number format to integer

So we need to look at 3 levels.

what is the data on DB ?

what is data immediately after loading to QlikView?

what is data being shown on the charts?

I guess for further investigation one would require a test data and qvw.

Pallav

Re: Rounding Issue Of Qlik

stigchel — Wed, 02 Sep 2015 11:01:47 GMT

Re: Rounding Issue Of Qlik

jonathandienst — Wed, 02 Sep 2015 11:05:42 GMT

khushboo Peswani wrote:

say for a year 2014 , value is 128.50 which shows as 128

and for year 2015 it is 2670.5 which comes as 2671.

Not at all - I expect that if you show more decimals, you will see that the 128.50 is actually smaller than 128.5 (for example 128.4997), so QV is quite correct to round the number down.

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 11:10:37 GMT

Hi Jonathan,

Thanks for your reply.

I have checked upto 5 decimals.

In my source data it is 128.500000 and 2670.500000.

Thanks

Khushboo

Re: Rounding Issue Of Qlik

sasiparupudi1 — Wed, 02 Sep 2015 11:43:21 GMT

please post some sample data and your script

Re: Rounding Issue Of Qlik

jonathandienst — Wed, 02 Sep 2015 11:55:37 GMT

khushboo Peswani wrote:

I have checked upto 5 decimals.

In my source data it is 128.500000 and 2670.500000.

Then you may need more decimals - I think QV numbers have a resolution of about 14 digits. Try an expression like

=128.50 - DBvalue -- you will see that you don't get an answer of zero, you will get a (very) small positive number.

Did you read this: Rounding Errors

It explains the rounding "error" problem in more detail.

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 12:14:19 GMT

Hi Jonathan,

Thanks a ton for your reply Jonathan.

i checked upto 14 digits and got an difference .

i got 1258.49999999999997. Thanks for telling me the root cause of this issue.

Can you also tell me how to get rid of this issue?

Thanks and Regards

Khushboo

Re: Rounding Issue Of Qlik

jonathandienst — Wed, 02 Sep 2015 12:16:02 GMT

Why is an issue to get rid of?

QV is rounding that value correctly as it is less than 1258.5.

Re: Rounding Issue Of Qlik

Wed, 02 Sep 2015 12:23:53 GMT

But problem is even tough on Qlik it is 128.49999999999997 but in the source excel which we have loaded ,

it is 128.50000000000000

So client will not get convinced?

Thanks

Khushboo