Quicksight - Adding Calculated Fields | My Digital Help Centre

In AWS QuickSight, you can build reports using exported data from MDA, which includes a wide range of fields for analysis. However, there are many scenarios where custom calculations are needed. For those familiar with Excel formulas, calculated fields in QuickSight offer a powerful alternative for defining custom logic directly within your analysis.

Adding Calculated Fields

To add a calculated field in AWS QuickSight:

Go to the Analyses List and select the analysis you'd like to modify.
Alternatively, use the search bar on the homepage to quickly locate your desired analysis.

Note: You cannot edit dashboards directly in QuickSight. Calculated fields must be added within an analysis.

Author Users: If you can view a dashboard but do not have access to its corresponding analysis, please reach out to another author user who may be able to grant access. If they also do not have access, we recommend contacting our support team to request the necessary permissions.
Reader Users: Readers can only interact with published dashboards and do not have permissions to view or modify analyses.

To add a calculated field:

In the Data pane, click + Calculated Field located above the list of fields, or navigate to the top menu and select Data > Add Calculated Field.
The Add Calculated Field window will appear, offering the following options:
- Name: Provide a unique name. It must not duplicate any existing field or calculated field.
- Textbox: Enter your logic here using QuickSight’s expression language. You can reference existing fields, parameters, and functions.
- Fields: Calculated fields can reference any other fields within the same dataset, including other calculated fields. However, it's essential to avoid circular references, as they can lead to errors.
  
  For example, if you create a calculated field "CF1", then create another calculated field "CF2" that uses "CF1" in its calculation and later modify "CF1" to use "CF2", this creates a circular dependency. Because the fields depend on each other, QuickSight cannot resolve the calculations, and both fields will return errors.
- Parameters: You can incorporate parameters into calculated fields to create dynamic calculations that respond to user input. Note that parameters must have a default value when used in calculated fields, and most functions support only single-value parameters.
- Functions: Calculated fields use a combination of dataset fields, parameters, and predefined functions to generate new values. QuickSight provides a variety of prebuilt functions. These can be combined to build powerful and complex logic for your visuals.

Once your logic is complete, click “Save” in the top-right corner to apply your changes.

Editing Calculated Fields

Once a calculated field is created, it will appear in the Data pane with a = symbol in front of its name, indicating that it is a calculated field.

To edit:

Click the three dots next to the field name.
Select “Edit calculated field”.
This opens the same editor you used to initially create the field.

Using Calculated Fields in Your Analysis

Calculated fields behave just like regular fields and can be used in visuals, filters, or aggregations. However, depending on the formula and aggregations involved, some fields may have limitations on where they can be applied.

Tips for Working with Calculated Fields

Here are some best practices and troubleshooting tips:

Data Types Matter: Unlike Excel, QuickSight is strict about data types.

For example, concat() only works with strings. Use toString() to convert numeric or date fields when needed.
Check Brackets Carefully: Misplaced or unmatched brackets are common sources of errors. To avoid confusion:
- Place each function or argument on a new line.
- Indent nested functions for clarity.
- Use a code editor (like Notepad++) with colour-coded bracket matching to cross-check your formula.
Use Comments: You can add comments within calculated fields to document what the calculation does, which is helpful for future reference or collaboration. To add a comment, simply start the line with //.

When making significant changes to a calculated field, consider copying the original formula and commenting it out. This way, you retain the previous version within the editor while working on the updated logic, helping you track changes without losing the original calculation.
Break Down Complex Logic: If you're working with a complex calculated field that isn't producing the expected results, consider breaking it down into smaller test calculated fields. By isolating and evaluating individual components of the overall formula, you can more easily identify where the issue lies.

Commonly Used Functions in QuickSight

Below is a list of commonly used functions grouped by category, along with brief explanations of how they work.

For a full list of available functions and their syntax, please refer to the official AWS QuickSight documentation:

🔗 QuickSight Functions by Category

Note: When using functions, input values can come from dataset fields, parameters, fixed values, or even other functions, provided they return the appropriate data type.

Generic Functions

ifelse

Evaluates a condition and returns the corresponding result. This is similar to Excel’s IF() function but more flexible—it allows multiple conditions and results in a single expression:
```
ifelse(condition1, result1, condition2, result2, ..., false)
```
switch

Returns a result based on the exact value of a field. It's more efficient than ifelse() when comparing a single field to multiple values.

If the field is value1, then it returns result1. If the field is value2 instead, then it returns result2, etc. If the field is none of the specified values, then it returns the default value.
```
switch(field, value1, result1, value2, result2, ..., default)
```
isNull / isNotNull

Returns true if the field is null/is not null, and otherwise false.
```
isNull(field) / isNotNull(field)
```
coalesce

Returns the first non-null value from the list of fields.
```
coalesce(field1, field2, ...)
```
in / notIn

Returns true if the field’s value is (or is not) in a specified list. The list can be:
- A literal list, formatted as [value1, value2, ...]
- A multi-value parameter, which allows user-defined input of multiple values
```
in(field, list) / notIn (field, list)
```
toString

Converts the value of a field to a string format, useful when working with string-specific functions.
```
toString(field)
```

String Functions

parseInt / parseDecimal / parseDate

Converts a string to an integer, decimal, or date respectively. If the string is not valid for the target data type, the function returns NULL.
```
parseInt(string) / parseDecimal(string)
```
For parseDate(), you can specify the date format pattern.
```
parseDate(expression, ['format'])
```
concat

Joins two or more strings into a single string.
```
concat(string1, string2, ...)
```
left / right

Returns the leftmost or rightmost characters from a string based on the specified number of characters.
```
left(string, number) / right(string, number)
```
Example: left('Quicksight', 4) returns 'Quic'.
contains

Checks if a string contains a specified substring.
```
contains(string, substring, string-comparison mode)
```
The mode can be:
- CASE_SENSITIVE
- CASE_INSENSITIVE

Numeric Functions

Arithmetic Operators:

Use +, -, *, and / to perform basic mathematical operations.
floor / ceil

Rounds a number down (floor) or up (ceil) to the nearest integer.
```
floor(decimal) / ceil(decimal)
```
Example: ceil(3.14) returns 4 and floor(3.14) will return 3
round

Rounds a number to a specified number of decimal places.
```
round(decimal, places)
```
Example: round(3.1416, 2) returns 3.14.
abs

Returns the absolute value of a number (i.e., removes the negative sign).
```
abs(number)
```

Date Functions

now()

Returns the current date and time.
addDateTime

Adds a specific amount of time to a given date.
```
addDateTime(amount, period, datetime)
```
Example: addDateTime(5, 'DD', '2020-01-01') returns '2020-01-06'.
dateDiff

Returns the number of days between two dates. The result is positive if date1 is earlier than date2.
```
dateDiff(date1, date2,[period])
```
extract

Extracts part of a date (e.g., year, month, day).
```
extract(period, date)
```
Example: extract('DD', '2020-01-01') returns 1.

Aggregate Functions

These functions operate over grouped or total sets of data:

sum / avg / count / distinct_count

Standard aggregation functions for numeric and non-null values.

sum(measure, [group-by level]) / avg(decimal, [group-by level])

count(dimension or measure, [group-by level]) / distinct_count(dimension or measure, [group-by level])

min / max

Returns the minimum or maximum value within a group or dataset. Works with both numbers and dates.
```
min(measure, [group-by level])  / max(measure, [group-by level])
```
<function>If

Performs aggregation only on rows that meet a specified condition.
Example: sumIf(total, status = 'Paid') - will add all the ‘total’ entries together, but only for rows where the status = ‘Paid’.
```
sumIf(measure, conditions)
```
<function>Over

Aggregate values across all rows partitioned by one or more fields. The level defines how the function is calculated:
- POST_AGG_FILTER (default): Aggregated result used only in aggregated visuals.
- PRE_AGG: Returns a standard field value and is recommended for most scenarios.
- PRE_FILTER: Includes values filtered out of the visual in the aggregation.
```
<function>Over(value, [field1, field2, ...], level)
```

Tip: Use PRE_AGG in most cases, unless you specifically need to include filtered-out data, in which case PRE_FILTER may be more appropriate.

If you have any questions or need further assistance, don’t hesitate to reach out to our support team. I hope you enjoyed this article 😊