Amazon QuickSight Calculated Field Function and Operator Reference
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.
Arithmetic and Comparison Operators
You can use the following arithmetic and comparison operators in calculated fields:

Addition (+)

Subtraction (−)

Multiplication (*)

Division (/)

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 casesensitive. 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 * {Sales Amount})  {Discount Amount}
Example: (=) Equal
Using = performs a casesensitive 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: AND, OR, & NOT
The following example uses AND, OR, & 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 {Number of 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 {Order Date}
and returns rows where
the {Order Date}
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.
{Order Date} >= "1/1/2016" AND {Order Date} <= "12/31/2016"
Functions by Category
In this section, you can find a list of the functions available in Amazon QuickSight sorted by category.
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:

dateDiff returns the difference in days between two date fields. (SPICE enabled)

epochDate converts an epoch date into a standard date. (SPICE enabled)

extract returns a specified portion of a date value. (SPICE enabled)

formatDate formats a date using a pattern you specify.

now returns the current date and time, using either settings for a database, or UTC for file and Salesforce.

truncDate returns a date value that represents a specified portion of a date. (SPICE enabled)
Numeric Functions
The numeric functions for calculated fields in Amazon QuickSight include the following.

ceil rounds a decimal value to the next highest integer. (SPICE enabled)

decimalToInt converts a decimal value to an integer. (SPICE enabled)

floor decrements a decimal value to the next lowest integer. (SPICE enabled)

intToDecimal converts an integer value to a decimal. (SPICE enabled)

round rounds a decimal value to the closest integer or, if scale is specified, to the closest decimal place. (SPICE enabled)
String Functions
The string (text) functions for calculated fields in Amazon QuickSight include the following.

concat concatenates two or more strings.

left returns the specified number of leftmost characters from a string.

locate locates a substring within another string, and returns the number of characters before the substring.

ltrim removes preceding whitespace from a string.

parseDate parses a string to determine if it contains a date value, and returns the date if found.

parseDecimal parses a string to determine if it contains a decimal value.

parseInt parses a string to determine if it contains an integer value.

replace replaces part of a string with a new string.

right returns the specified number of rightmost characters from a string.

rtrim removes following whitespace from a string.

split splits a string into an array of substrings, based on a delimiter that you choose, and returns the item specified by the position.

strlen returns the number of characters in a string.

substring returns the specified number of characters in a string, starting at the specified location.

toLower formats a string in all lowercase.

toString formats the input expression as a string.

toUpper formats a string in all uppercase.

trim removes both preceding and following whitespace from a string.
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.