Calculations introducing extra decimal places

Q&A
,
1

I noticed that sometimes my calculations add extra decimal places to my numbers. For example, the numbers in the screenshot below are calculated but they should be exactly 45, 25, 45, 50, 45, 60 and 45 respectively. When I export this visual to CSV, I don’t see all the extra decimal places.

Normally this would be fine because I can easily set the number of decimal places to 0 at the visual level. The problem is when I add a top/bottom filter, QS doesn’t correctly identify ties because of all the extra decimal places in the calculated numbers.

Is there a reason why I’m getting these extra decimal places in my calculated numbers?

Decimal Places

2

Hi @David_Wong -

I’ve run into this issue and believe it’s a bug with Quick Sight.

You can use this workaround.

  1. round your score to 2 decimal places
  2. use tostring, split, substring, and locate to truncate the extra decimal places
  3. convert back to decimal with parseDecimal

Decimal

parseDecimal(concat(split(toString(round({score},2)),".",1),substring(toString(round({score},2)),locate(toString(round({score},2)),".",1),3)))

Int

parseInt(split(toString(round({score})),".",1))
1 Like
3

Hi, Thanks for bringing this issue to us for attention. We will dive deep and work on a fix for it.

2 Likes
4

@David_Wong, What is the type of “Score”? Is it Decimal or Float?
And what aggregation are you using for Score is it “SUM”, “MIN”, “AVG” etc?

Quick Sight on final result rendering uses Float type which can sometimes be in-precise (https://stackoverflow.com/questions/21895756/why-are-floating-point-numbers-inaccurate).

That said I am not sure if the TopBottom issue with tie-breakers is related to residual values that you see in the visual.

1 Like
5

Guys this issue is still pending .

6

this is very ugly way of doing things. i hope we have a the problem fixed in the first place itself

7

This is still an existing issue, look forward to a clean way to do such simple calculation

1 Like
8

This is not efficient way to use tostring. please notify when updated

9

Hi, this issue is caused by the nature of the “float” data type, please try to change the data type from “decimal-float” to “decimal-fixed”, see if it help address your need?

10

It is unclear how to convert the data type of a field to “decimal-fixed”. Can you include reference documentation in your replies?

11

Hi @bhaumikn - The bottom of this doc section will walk you through change the numeric data type in the dataprep (edit dataset) screen.

1 Like