topic Re: Round off value incorrect in QlikView

Round off value incorrect

Thu, 06 Aug 2015 09:36:47 GMT

Hi QlikView users,

I would like to seek help, I am having a problem on the rounding of of the values, I have a value of 1.5 which is derived from 1500000 divided by 1000 to get value in million then when on chart. I used the number format fixed to and integer the value shows the value of 1. I also then tried this solution, Round(Sum(Fieldname) *10/10,0.1) it got the correct answer, but it affected the other values.

Please help on how to round off correctly to integer.

Thanks in advance

Royce

Re: Round off value incorrect

Kushal_Chawda — Thu, 06 Aug 2015 09:44:28 GMT

Can you post the sample?

Re: Round off value incorrect

gautik92 — Thu, 06 Aug 2015 09:53:55 GMT

post the sample data

Re: Round off value incorrect

jonathandienst — Thu, 06 Aug 2015 09:58:27 GMT

That looks correct to me, although its not necessary if you are producing a bar/line etc chart.

If that expression is not working for you, you will need to provide more information about what is going wrong. And as always, uploading your qvw application or a representative sample will help.

Re: Round off value incorrect

Thu, 06 Aug 2015 10:37:33 GMT

Sorry, I cannot post the sample data. would be images ok?

Re: Round off value incorrect

vardhancse — Thu, 06 Aug 2015 10:40:27 GMT

Hi,

If for the expression we use round function() or in number format do the changes as per required format, it will display for all the dimensions.

What exactly you are looking for.?

Re: Round off value incorrect

sasiparupudi1 — Thu, 06 Aug 2015 10:41:51 GMT

at leaset post the numbers that you are seeing the problem with rounding.

Re: Round off value incorrect

Thu, 06 Aug 2015 10:51:24 GMT

this is the exact value

this is with the use of Round() function

Re: Round off value incorrect

vardhancse — Thu, 06 Aug 2015 10:53:39 GMT

what you are trying to achieve.

Both number format and round() are same

Re: Round off value incorrect

Thu, 06 Aug 2015 11:17:48 GMT

id like to make it round off to 2, some of the solutions are ok, but it affect other values on the bar chart which makes the other values incorrect.

Re: Round off value incorrect

marcus_sommer — Thu, 06 Aug 2015 17:07:00 GMT

Are you sure that the results are not correct? Export the chart to excel, to see which numbers are behind the bars. Maybe it needs only a few small adjustments. Further rounding produced always errors determinded by the system, see: Rounding Errors. Therefore it could be that you need to combine different rounding-, formatting- and/or string-function to get your expected solution.

- Marcus

Re: Round off value incorrect

Fri, 07 Aug 2015 05:57:53 GMT

Below is the exported to excel. I cannot add the excel file so I captured it as image. the problem is the value for Feb which is 1.5, it should be round off into 2, I used some formulas, but what happen is for example the Jan value which is 61.44 will round off to 62.

Re: Round off value incorrect

marcus_sommer — Fri, 07 Aug 2015 08:04:11 GMT

I don't understand what your problem is - if I round this or formated it within the tab numbers everything is like expected. I suggest you checked it again and if it not worked you creates a small example from this case - these few numbers in a inline-table and then your chart over this.

- Marcus

Re: Round off value incorrect

sasiparupudi1 — Fri, 07 Aug 2015 08:54:07 GMT

I think you need to check your multiplication/division/sum operations..It may not be a rounding issue because QV returns the correct values for the above mentioned problem values.

Could you may be post the original values and the math operations you performed on them to arrive at for example 1.5 and 61.44

Re: Round off value incorrect

Fri, 07 Aug 2015 09:17:32 GMT

Yes, If I recreate it on the different version, it is rounding correctly, like for example on the text box Round(1.5) = 2.

Re: Round off value incorrect

Fri, 07 Aug 2015 09:19:37 GMT

I just did a sum of the field. there is no other computation. the only operation it went to is it was divided to 1000000 to get the value in million.

Re: Round off value incorrect

veidlburkhard — Fri, 07 Aug 2015 09:26:24 GMT

Hi Royce,

why don't you use 'ceil' to round upwards. This is the definition in QV Help:

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

Rounding of x upwards to the nearest multiple of base with an offset of offset. The result is a number.

Examples:

ceil( 2.4 ) returns 3

ceil( 2.6 ) returns 3

ceil( 3.88 , 0.1 ) returns 3.9

ceil( 3.88 , 5 ) returns 5

ceil( 1.1 , 1 , 0.5 ) returns 1.5

Good luck

Burkhard

Re: Round off value incorrect

Fri, 07 Aug 2015 10:43:32 GMT

I cannot use that, that will round the number always upward, I need to have the correct round off value.

Re: Round off value incorrect

Digvijay_Singh — Fri, 07 Aug 2015 10:59:37 GMT

Do you mean you want separate rounding mechanism of row values in a specific column. I don't think it is possible. If you round your expression up to desired decimal values, it will impact all values in the column which depicts output of that expression..

Re: Round off value incorrect

Fri, 07 Aug 2015 11:06:56 GMT

yes, that is correct. i don't want to have a separate rounding off, I just don't know why it is rounding that way.

I have read something, that the rounding is looking on the binary number and not on the exact value? is that correct?