QuickSight automatically rounding to 1 decimal after a division

Question & Answer
, , ,
1

Good morning,

Recently I’ve been trying to add a calculated field, which the output needs to have, at least, 2 decimals. For confidentiality reasons, I’ll map my columns names, used for this calculation, into ‘X’ and ‘Y’.

Here is the case:

I’ve 2 columns, column X and Y. Both of them, are Decimal data type with 4 decimals, defined in the QuickSight dataset. Then, to obtain the calculated field, I’m performing this aggregated operation:

(sum({column_X})/sum({column_Y}))*1000

Unfortunately, I get only one decimal.

What I have tried:

Select new calculated field - More formatting options - decimal Places - Increase the Custom number

That didn’t solve the problem, I believe that, after the division of an aggregation, QuickSight is just capping it into 1 decimal.

Does anyone has a workaround for this?

Thanks!

1 Like
2

Hi @Jose_Siles - What is the data type of column_X and Column_Y. Can we change to decimal and test it?

Regards - Sanjeeb

3

Hi @Sanjeeb2022 , thanks for your response. The datatype of both is Decimal, it was specified in the body of my question.

4

Hi @Jose_Siles - Can you cast to the division to 2 decimal as well.

CAST(sum({column_X})/sum({column_Y}) as decimal(10,2))*1000

Regards - Sanjeeb

5

Hi @Jose_Siles - Just thinking since the multiplication is happening 1000, can we do the case to decimal(10,4)

Tagging @Jesse and @gillepa and @Max for their feedback.

Regards - Sanjeeb

6

Hi @Sanjeeb2022 thanks again!

This formula is giving me a Syntax Error. Can it be maybe because CAST() is not appearing in my functions list? I also have tried CONVERT(), but neither is appearing.

7

Thanks for that, I’ll implement that as decimal(10,4) once I can use the CAST/CONVERT AS funciton properly!

8

Thank @Jose_Siles . Yes you are correct. Is your data source is RDBMS, can you use custom sql and do the cast in custom sql.

I am also tagging some experts for their advise, @Max @David_Wong @Biswajit_1993

Regards - Sanjeeb

9

Than you too @Sanjeeb2022 . But the 2 input columns are already decimal(10,4). So, correct me if I’m wrong, you are proposing me to generate the calculated field in my ETL query, right?

10

Hi @Jose_Siles - Not at ETL layer, but when import the data set, you can use custom sql and use the cast and sum and create your custom field at data set level.

Regards - Sanjeeb

11

Okay, thanks @Sanjeeb2022 . I’m testing a new solution to add column of the calculted field in my RDBMS table which resides in my Redshift. Will come back to you with my feedback.

12

Hi @Jose_Siles, we are workin on a feature to support as many as 15 decimal places with high level of calculation precision for SPICE dataest. We are now open for private preview, if you would like to test the feature, please message me offline and we can sign you up for preview. Thanks!

1 Like
13

Hi @emilyzhu - Is it possible for me to have a look on the new feature?
Please advise.

Regards - Sanjeeb

14