Quicksight: Calculation Field - decimal places truncated

Question

I am posting a question which is related to a dashboard development on Quicksight. It includes a calculation field which uses sumover's in a sumover.  However, I encountered a problem that Quicksight can't produce accurate result when it take multiplication operations on two float number fields.

I am sharing a simple example to describe the problem. As you see, there would be two steps in the calculation. I.e. 1.) sum(cost)/sum(cost2), and 2.) pre-result * 10,000. I suspect the float number is truncated in the first step so that the displayed result is not accurate.

![Enter image description here](https://repost.aws/media/postImages/original/IMeZtCDOpQSkKAgwK-1-Uk7Q)

I tried some workaround solutions. E.g. re-write the calculation as sum(cost)*10000/sum(cost2). It seems to be using much a lot computation power; thus, this workaround results in overflow error in my real case.

Note: the 10,000 in my real case is not constant.

Answer

As per SPICE Limitaion, "QuickSight enables you to create calculations that are based on DECIMAL data having more than four decimal places to the right of the decimal point. However, QuickSight can display **no more than four decimal places to the right of the decimal point**. The value is truncated, not rounded, when displayed in data preparation or analyses and when imported into SPICE."

[+] Supported data types and values - Numeric data - https://docs.aws.amazon.com/quicksight/latest/user/supported-data-types-and-values.html#numeric

One workaround for the issue is to use the formula :
`(sum(cost^1)/sum(cost2^1))*10000`

Which basically puts a dummy power function on the column, whose result is Double type.

Please try the above and let me know if it helps. Thank you!

Quicksight: Calculation Field - decimal places truncated

相關內容