How do I troubleshoot calculated fields in Quick Sight?

Resolution

Note: For calculated field expressions in Quick Sight, curly braces ({}) are for field references. For example, {cost} refers to the dataset column that's named cost. Parameters use the ${paramName} syntax.

Resolve aggregation errors in calculated fields

A calculated field expression can't combine aggregated functions, such as sum, avg, or count, with non-aggregated field references. If you combine aggregated functions and non-aggregated field references, then you receive the following error message:

"Mismatched aggregation. Custom aggregations can't contain both aggregated and nonaggregated fields, in any combination."

To resolve mismatch issues in your aggregations, take one of the following actions.

Match the aggregation level across all fields

Use the same aggregation level on all fields. For example, replace sum({cost}) / {total} with sum({cost}) / sum({total}).

To use conditional aggregation, put the ifelse inside the aggregate function.

Example calculation:

sum(ifelse({category} = 'A', {cost}, 0)) / avg(ifelse({category} = 'A', {total}, 0))

Use parameters to avoid aggregation conflicts

Parameters that use the ${paramName} format are scalar values that don't require aggregation. In expressions that contain aggregate functions, use parameters for fixed values, such as date ranges.

Example calculation that results in errors:

sum(ifelse(status = 'RESOLVED', 1, 0)) / ifelse(extract("MM", {resolvedon}) = 2, 28, 31)

Example calculation that uses parameters:

sum(ifelse(status = 'RESOLVED', 1, 0)) / dateDiff(truncDate("MM", ${startDate}), addDateTime(1, "MM", truncDate("MM", ${endDate})), "DD")

You can reference analysis parameters only in analysis-level calculated fields with the ${paramName} syntax. Dataset-level calculated fields can't reference analysis parameters. However, you can create dataset parameters to use in dataset-level calculated fields, and then use custom SQL to directly query datasets.

Correctly configure your LAC functions

If you use level-aware calculations (LAC), such as sumOver, countOver, or avgOver, then the function results are already aggregated. If you combine LAC with non-aggregated fields in arithmetic operations, then you receive aggregation errors. To resolve this issue, wrap the fields in a matching LAC function or separate the fields into distinct calculated fields.

Example calculation with LAC and a non-aggregated field that results in errors:

sumOver({revenue}, [{region}]) / {total_budget}

Example calculation that wraps LAC with a matching partition:

sumOver({revenue}, [{region}]) / sumOver({total_budget}, [{region}])

To apply filtering logic to a LAC function, use ifelse conditions within the expression, or set the calculation level parameter to PRE_FILTER or PRE_AGG. For information about how Quick Sight evaluates LAC functions, filters, and aggregations, see Order of evaluation in Amazon Quick Sight.

If you use a calculated field that contains aggregate functions as a filter condition, then you receive the following error message:

"Aggregated calculated column values cannot be fetched"

To resolve this issue, create a separate calculated field that doesn't use aggregate functions to produce the same categorization logic. Then, apply a filter to the field. Or, create a top or bottom filter that you use on aggregated results.

Resolve data formatting, NULL, or division-by-zero issues

Remove decimal places from a numeric field

Complete the following steps:

1. Open the Quick console.
2. Choose your analysis, and then choose the field that you want to update.
3. Choose Format, and then choose More formatting options.
4. For Decimal places, enter 0.

Or, use the following formula to round up the calculated field and remove decimal places:

round({your_numeric_field}, 0)

Note: Replace your_numeric_field with your field name.

To truncate instead of round up the decimals, use the following formula:

decimalToInt({your_numeric_field})

Note: Replace your_numeric_field with your field name.

Manage NULL values as blanks or replacement values

Complete the following steps:

1. Open the Quick console.
2. Choose your analysis, and then choose the field that you want to update.
3. Choose Format, and then choose Null values.
4. Choose Custom, and then enter your display value.

Or, use coalesce to replace NULL with a default value.

Example:

coalesce({your_field}, 0)

Note: Replace your_field with your field name.

For a conditional replacement, use the following formula:

ifelse formula: ifelse(isNull({profit}), 0, {profit})

Note: Replace profit with the field that might be NULL and 0 with the value to use instead of NULL.

All branches of an ifelse expression must return the same data type. For example, if one branch returns a date value, then the other branches must also return date values, not strings. If none of the default values work, then use NULL to represent the missing or non-applicable value. NULL is compatible with any data type.

Configure formulas to manage division by zero

If your formulas divide by zero, then the result appears blank because the formula evaluates to undefined. This configuration also occurs if you divide zero by zero.

To return a 0 value for division by zero, use an ifelse condition to check the denominator before division.

Row-level example:

ifelse({denominator} = 0, 0, {numerator} / {denominator})

Aggregated context example:

ifelse(sum({denominator}) = 0, 0, sum({numerator}) / sum({denominator}))

Troubleshoot save failures and missing fields

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Resolve the intermittent "An error occurred while saving a calculated field" error

If you intermittently receive the "An error occurred while saving a calculated field" error, then refresh the page. Check whether the calculated field saved despite the error. If the field saved, then there are no issues.

If the field didn't save, then clear your browser cache and try again. If the issue continues, then try again in a different browser to determine whether the issue is browser specific.

If the issue occurs across multiple browsers, then create a HAR file to capture the error. Then, contact AWS Support.

Make sure that calculated fields don't disappear when you replace datasets

If you replace a dataset in an analysis, then the analysis-level calculated fields might become broken or inaccessible. This issue typically occurs when the new dataset has different column names or data types. If you replace multiple datasets with a single dataset, then Quick Sight might completely remove calculated fields.

Prerequisite: Before you replace your dataset, run the following describe-analysis-definition AWS CLI command to back up your analysis definition:

aws quicksight describe-analysis-definition \
--aws-account-id your_account_id \
--analysis-id your-analysis-ID

Note: Replace your-account-ID with your AWS account ID and your-analysis-ID with your analysis ID.

To keep calculated fields when you replace datasets, make sure that you correctly replace the dataset. Confirm that the new dataset schema, such as column names and data types, exactly matches the original dataset.

Use the dataset editor to create reusable calculated fields at the dataset level. Quick Sight stores dataset-level calculated fields within the dataset, so dataset replacement doesn't affect the fields.

If you already lost your calculated fields, then import visuals from the source dashboard that contain the lost calculated fields. The visuals that used the fields might still contain the field details.

Troubleshoot "INVALID_CALCULATED_COLUMN_EXPRESSION" errors that occur during cross-account imports

If you import an asset bundle across accounts, then you might receive the "INVALID_CALCULATED_COLUMN_EXPRESSION" error message. This error occurs when a calculated field references a column that doesn't exist in the destination account's dataset, or there are dependency ordering issues.

To resolve this issue, check the dataset definition. In the source account, run the following describe-data-set command:

aws quicksight describe-data-set \
  --aws-account-id your_account_id \
  --data-set-id your_dataset_id

Note: Replace your-account-ID with your account ID and your-dataset-ID with your dataset ID.

In the command's output, confirm that all referenced columns exist. Make sure to check columns that the fields generate through joins. If there are missing columns from joined tables, then configure the same joins in the destination dataset. If there are missing source columns, then confirm that the destination data source contains the columns. Add them to the source table or SQL query.

Also, check for dependency ordering issues. If calculated field B references calculated field A, then the asset bundle must define A before B. If the fields are out of order, then reorder them.

Related information

Calculated field function and operator reference for Amazon Quick

Asset bundle operations

truncDate function