Rounding Issue Of Qlik

[#1]

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

[#2]

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

[#3]

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

[stigchel]

See also

Rounding Errors

[jonathandienst]

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.

[#6]

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

[sasiparupudi1]

please post some sample data and your script

[jonathandienst · Accepted solution]

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.

[#9]

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

[jonathandienst]

Why is an issue to get rid of?

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

[#11]

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