(Redshift only) Why are the AVG (average) results in my reports automatically rounded?

[Quynh_Le]

This article is applicable to Redshift users only. If you use other data sources and encounter this issue, please contact Holistics support at [email protected].

The problem:

When using AVG function in your Data Exploration, you notice that the results are displayed as whole numbers without their decimals. You try configuring Data Format into showing decimals, but to no avail.

Why does this happen?

By default, the return type for Redshift AVG function is BIGINT for any integer type argument. Therefore, Holistics would be unable to display decimals for those results (even if you have selected Decimal Number Format in Format setting).

How to resolve this issue?

Create a custom dimension that casts your your integer field into decimals

In your Holistics Data Model, create a custom dimension that casts your integer field into decimals, like so:

cast( {{ #THIS.my_field }} as double precision )

You can then use this dimension to calculate in your Redshift AVG function.