Menu
Amazon QuickSight
User Guide

Working with Calculated Fields

Create calculated fields to use operators or functions to analyze or transform field data. For details about supported functions and operators, see Calculated Field Function and Operator Reference.

You can use multiple functions and operators in a calculated field. For example, you might use the formatDate function to extract the year from a date field, and then the ifelse function to segment records based on the year:

Copy
ifelse(formatDate(order_date, 'yyyy') > '2000', 'this century', 'last century')

You can create a calculated field based on one or more data set fields and/or existing calculated fields. For example, you could use the ifelse function to create a quarter field extrapolated from a month value:

Copy
ifelse(month <=3, 1, month > 3 AND month <= 6, 2, month > 6 AND month <= 9, 3, 4)

And then use that calculated quarter field and a sales amount field to identify high-spending customers for the first quarter:

Copy
ifelse(quarter = 1 AND sales_amount >= 10000, 'review account', 'n/a')

You can add calculated fields to a data set during data preparation or from the analysis page. When you add a calculated field to a data set during data preparation, it is available to all analyses that use that data set. When you add a calculated field to a data set in an analysis, it is available only in that analysis. For information about adding calculated fields during data preparation, see Adding a Calculated Field During Data Preparation. For information about adding calculated fields in an analysis, see Adding a Calculated Field to an Analysis.

Handling Decimal Values in Calculated Fields

The decimal data type supports up to four decimal places to the right of the decimal point. During data preparation, calculated fields that use decimal data with more than four decimal places use the full value to perform the calculation. If the result is again decimal data that uses more than four decimal places, the result is then truncated when the data set is imported into SPICE or displayed in an analysis.

As an example, take decimal field FieldA with a value of 0.00006, which is displayed in the user interface as 0.0. The full value 0.00006 is still used in all calculations. The following are some examples of how this value would be used in calculations:

  • FieldA > 0 = true. The calculated field value displayed in the analysis or imported into SPICE is true.

  • ceil(FieldA) = 1. The calculated field value displayed in the analysis or imported into SPICE is 1.

  • FieldA + 0.00009 = 0.00015. The calculated field value displayed in the analysis or imported into SPICE is 0.0001.

  • FieldA * 1.5 = 0.00009. The calculated field value displayed in the analysis or imported into SPICE is 0.0.