Amazon QuickSight
User Guide

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

Adding a Calculated Field to an Analysis

You create calculated fields to use functions and operators to analyze or transform field data. You can add calculated fields to a data set or to an analysis. You can create a calculated field and add a formula (expression) with aggregate functions only in an analysis.

When you add a calculated field to a data set during data preparation, it's available to all analyses that use that data set. Data sets support only single-row operations. When you add a calculated field to an analysis, it's available only in that analysis. Analyses support both single-row operations and aggregate operations.

Single-row operations are those that supply a (potentially) different result for every row. Aggregate operations supply results that are always the same for entire sets of rows. For example, if you use a simple string function with no conditions, it changes every row. If you use an aggregate function, it applies to all the rows in a group. If you ask for the total sales amount for the US, the same number applies to the entire set. If you ask for data on a particular state, the total sales amount changes to reflect your new grouping. It still provides one result for the entire set.

By creating the aggregated calculated field within the analysis, you can then drill down into the data. The value of that aggregated field is recalculated appropriately for each level. This type of aggregation isn't possible during data set preparation.

For example, let's say that you want to figure out the percentage of profit for each country, region, and state. You can add a calculated field to your analysis, (sum(salesAmount - cost)) / sum(salesAmount). This field is then calculated for each country, region, and state, at the time your analyst drills down into the geography.

To see which functions are available in SPICE, see Functions by Category.

Note

The date functions extract and truncDate don't support SS (second) in SPICE.

For information on calculated fields in data sets, see Working with Calculated Fields.

Adding a Calculated Field

Use the following procedure to add a calculated field.

  1. Choose Add on the application bar, and then choose Add calculated field.

  2. For Calculated field name, in Enter a field name, type a name for the calculated field. This name is the field label displayed in the analysis, so it should match the existing style of field names.

  3. Choose a function from Function list, and choose the fields that you want to use from the Field list. Each field is entered into the formula where your cursor is. You can add user-defined parameters from Parameter list in the same way.

  4. In Formula, type any formula parameters needed by the function. Choose any additional fields or parameters to complete your formula.

    If you use a field that has a space or a nonalphanumeric character other than an underscore in the name, enclose the field name in curly braces when referencing it. An example is {ship charges amount}. Curly braces are optional if the field name has no space and no nonalphanumeric character.

  5. Choose Create.

    If there are no errors in the formula or name, the new calculated field is created. It appears in the Fields list pane.

Parameters in a Calculated Field in an Analysis

You can reference parameters in calculated fields that are in an analysis. When you create a calculation, you can choose existing parameters from the list of parameters under Parameter list. Or you can type the parameter name, preceded with a $ and enclosed in curly braces {}, for example ${parameterName}.

Editing a Calculated Field

Use the following procedure to edit a calculated field.

  1. In the Field list pane, hover over the calculated field you want to change.

  2. Choose the selector icon to the right of the field name, and then choose Edit calculated field.

  3. If the field is a custom aggregation, you can edit it in the field well.

    Hover over the field in the field well and choose the selector icon to the right of the field name. Choose Aggregate: custom, and then choose Custom.

    Then choose Edit formula.

Deleting a Calculated Field

Use the following procedure to delete a calculated field.

  1. In the Field list pane, hover over the calculated field you want to delete.

  2. Choose the selector icon to the right of the field name, and then choose Remove calculated field.