Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hschultz
Partner - Creator
Partner - Creator

Money rounding cents to .00

Hi All

I cannot understand why my cents are being rounded to .00

When i select Number, 2 Decimals a figure will show (for instance) 123.50. But as soon as i make it Money, it shows as 124.00

This is the formula in the chart is this:

R # ##0.00;R-# ##0.00

Can anyone please advise what i am doing wrong?

19 Replies
giakoum
Partner - Master II
Partner - Master II

Please change your document settings according to the image bellow. Note that decimal symbol is dot.

2013-01-11_0952.png

hschultz
Partner - Creator
Partner - Creator
Author

Thank you for this answer.

I have been able to change it, and the defaut expression now works. However, Money still is not correct.

Capture.PNG

giakoum
Partner - Master II
Partner - Master II

It is because it has a comma still as a seperator. Change comma to dot and it will work.

BR, Ioannis

flipside
Partner - Specialist II
Partner - Specialist II

No, it's because there is a space between # & # as per my earlier post. 

flipside

Not applicable

hi

Keep this format

R #,##0.00;R-#,##0.00

It works fine.

Best of luck

hschultz
Partner - Creator
Partner - Creator
Author

@Ioannis: It is correct, that as for some reason after changing the document settings my chart reverted back to a ,

I have updated it on the chart as well and it now works as expected.

@flipside: It works now even with space.

Can anyone explain why something as simple as a , or . would cause rounding? I would understand if it fails completely, or give me thousands etc, but i don't understand why it would round

flipside
Partner - Specialist II
Partner - Specialist II

Actually, I've had another look at that may not be the issue.

I think the solution is to change the format to R # ###.##;R-# ###.##.

flipside

flipside
Partner - Specialist II
Partner - Specialist II

Does your source data include the currency symbol?

hschultz
Partner - Creator
Partner - Creator
Author

When i do a SQL select statement, i cannot see the symbol.

But when i look at the colum properties, then it states that it is Data Type > Money.

I am not sure if QV pulls this with the currency symbol or not.

flipside
Partner - Specialist II
Partner - Specialist II

That sounds correct, the SQL column is defined as money and is being picked up as such. I was wondering if the values were being held in a varchar or char column alongside the currency symbol.

Although it might not be the cause in this case, there can be problems with this datatype as it actually holds 4x decimal places (to avoid rounding issues when making calculations) and many SQL developers prefer the decimal type instead and handle the rounding problems themselves.

I think in your case the data is coming through correctly, but I can't see why it is rounding it.  What I HAVE discovered in your posted example, is if you leave all the fields at Mixed (which I think is set by default) in Document Properties > Number, AND set the Number to Expression Default in the chart properties > Number, the chart does some strange things. If I disable the first expression the values in the two remaining expressions work correctly, however if I disable the last expression as well, the middle column remaining now calculates wrong.

Even worse, when the 2nd and 3rd expressions are active and you then set the total mode to Sum of Rows, the cell values round again. Definitely something odd happening.