6ec8a14 Jun 28, 2019
2 contributors

### Users who have contributed to this file

326 lines (247 sloc) 17 KB

# Calculated Field Function and Operator Reference for Amazon QuickSight

You can use the following functions and operators to create calculated fields.

For information on aggregating calculated fields, see Using Aggregate Functions in Calculated Fields . You can only aggregate calculated fields in an analysis, not in a data set.

Topics

## Arithmetic and Comparison Operators

You can use the following arithmetic and comparison operators in calculated fields:

• Subtraction (−)
• Multiplication (*)
• Division (/)
• Power (^)
• Equal (=)
• Not equal (<>)
• Greater than (>)
• Greater than or equal to (>=)
• Less than (<)
• Less than or equal to (<=)
• AND
• OR
• NOT

Equal (=) and not equal (<>) comparisons are case-sensitive. For example, if the condition is `state = 'WA'` and the value in the field is wa, those values are not considered to be equivalent.

Amazon QuickSight uses the standard order of operations: parentheses, exponents, multiplication, division, addition, subtraction.

To make lengthy calculations easier to read, you can use parenthesis to clarify groupings and precedence in calculations.

In the following statement, you don't need parentheses. The multiplication statement is processed first, and then the result is added to five, returning a value of 26. However, parentheses make the statement easier to read and thus maintain.

``````5 + (7 * 3)
``````

Because parenthesis are first in the order of operations, you can change the order in which other operators are applied. For example, in the following statement the addition statement is processed first, and then the result is multiplied by three, returning a value of 36.

``````(5 + 7) * 3
``````

### Example: Arithmetic Operators

The following example uses multiple arithmetic operators to determine a sales total after discount.

``````(Quantity * Amount) - Discount
``````

### Example: (=) Equal

Using = performs a case-sensitive comparison of values. Rows where the comparison is TRUE are included in the result set.

In the following example, rows where the `Region` field is South are included in the results. If the `Region` is south, these rows are excluded.

``````Region = 'South'
``````

In the following example, the comparison evaluates to FALSE.

``````Region = 'south'
``````

The following example shows a comparison that converts `Region` to all uppercase (SOUTH), and compares it to SOUTH. This returns rows where the region is south, South, or SOUTH.

``````toUpper(Region) = 'SOUTH'
``````

### Example: (<>)

The not equal symbol <> means less than or greater than.

So, if we say x<>1, then we are saying if x is less than 1 OR if x is greater than 1. Both < and > are evaluated together. In other words, if x is any value except 1. Or, x is not equal to 1.

Note
Use <>, not !=.

The following example compares `Status Code` to a numeric value. This returns rows where the `Status Code` is not equal to 1.

``````statusCode <> 1
``````

The following example compares multiple `statusCode` values. In this case, active records have an activeFlag = 1. This example returns rows where one of the following applies:

• For active records, show rows where the status isn't 1 or 2
• For inactive records, show rows where the status is 99 or -1
``````( activeFlag = 1 AND (statusCode <> 1 AND statusCode <> 2) )
OR
( activeFlag = 0 AND (statusCode= 99 OR statusCode= -1) )
``````

### Example: (^)

The power symbol `^` means to the power of.

The following example is a simple expression of 2 to the power of 4 or (2 * 2 * 2 * 2). This returns a value of 16.

``````2^4
``````

### Example: AND, OR, and NOT

The following example uses AND, OR, and NOT to compare multiple expressions using conditional operators to tag top customers NOT in Washington or Oregon with a special promotion, who made more than 10 orders. If no values are returned, the value 'n/a' is used.

``````ifelse(( (NOT (State = 'WA' OR State = 'OR')) AND Orders > 10), 'Special Promotion XYZ', 'n/a')
``````

### Example: Creating Comparison Lists Like "in" or "not in"

This example uses operators to create a comparison to find values that exist, or don't exist, in a specified list of values.

The following example compares `promoCode` a specified list of values. This example returns rows where the `promoCode` is in the list (1, 2, 3).

``````promoCode    = 1
OR promoCode = 2
OR promoCode = 3
``````

The following example compares `promoCode` a specified list of values. This example returns rows where the `promoCode` is NOT in the list (1, 2, 3).

``````NOT(promoCode = 1
OR promoCode  = 2
OR promoCode  = 3
)
``````

Another way to express this is to provide a list where the `promoCode` is not equal to any items in the list.

``````promoCode     <> 1
AND promoCode <> 2
AND promoCode <> 3
``````

### Example: Creating a "between" Comparison

This example uses comparison operators to create a comparison showing values that exist between one value and another.

The following example examines `OrderDate` and returns rows where the `OrderDate` is between the first day and last day of 2016. In this case, we want the first and last day included, so we use "or equal to" on the comparison operators.

``````OrderDate >= "1/1/2016" AND OrderDate <= "12/31/2016"
``````

## Functions by Category

In this section, you can find a list of the functions available in Amazon QuickSight sorted by category.

Topics

### Aggregate Functions

The aggregate functions for calculated fields in Amazon QuickSight include the following. These are only available in SPICE, while you are in the analysis screen. Each of these functions returns values grouped by the chosen dimension or dimensions.

• avg averages the set of numbers in the specified measure.
• count calculates the number of values in a dimension or measure.
• distinct_count calculates the number of distinct values in a dimension or measure.
• max returns the maximum value of the specified measure.
• min returns the minimum value of the specified measure.
• sum adds the set of numbers in the specified measure.

### Conditional Functions

The conditional functions for calculated fields in Amazon QuickSight include the following:

• `coalesce` returns the value of the first argument that is not null.
• `ifelse` evaluates a set of if, then expression pairings, and returns the value of the then argument for the first if argument that evaluates to true.
• `isNotNull` evaluates an expression to see if it is not null.
• `isNull` evaluates an expression to see if it is null. If the expression is null, `isNull` returns true, and otherwise it returns false.
• `nullIf` compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression.

### Date Functions

The date functions for calculated fields in Amazon QuickSight include the following:

### Numeric Functions

The numeric functions for calculated fields in Amazon QuickSight include the following:

### String Functions

The string (text) functions for calculated fields in Amazon QuickSight include the following:

### Table Calculations

Table calculations form a group of functions that provide context in an analysis. They provide support for enriched aggregated analysis. By using these calculations, you can address common business scenarios such as calculating percentage of total, running sum, difference, common baseline, and rank.

When you are analyzing data in a specific visual, you can apply table calculations to the current set of data to discover how dimensions influence measures or each other. Visualized data is your result set based on your current data set, with all the filters, field selections, and customizations applied. To see exactly what this result set is, you can export your visual to comma-separated value (CSV) format. A table calculation function performs operations on the data to reveal relationships between fields.

The table calculations available in Amazon QuickSight for both SPICE and direct query data include the following:

## Function Index

In this section, you can find a list of functions available in Amazon QuickSight. Some functions are available in SPICE, while you are in the analysis screen.

To view a list of functions sorted by category, with brief definitions, see Functions by Category.

Topics

## Table Calculation Functions Index

When you are analyzing data in a specific visual, you can apply table calculations to the current set of data to discover how dimensions influence measures or each other. Visualized data is your result set based on your current data set, with all the filters, field selections, and customizations applied. To see exactly what this result set is, you can export your visual to comma-separated value (CSV) format. A table calculation function performs operations on the data to reveal relationships between fields.

In this section, you can find a list of the functions available in table calculations that you can perform on visualized data in Amazon QuickSight.

To view a list of functions sorted by category, with brief definitions, see Functions by Category.

Topics

You can’t perform that action at this time.