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.

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 during analysis and visualization. Each of these functions returns values grouped by the chosen dimension or dimensions.

  • avg averages the set of numbers in the specified measure, grouped by the chosen dimension or dimensions.

  • count calculates the number of values in a dimension or measure, grouped by the chosen dimension or dimensions.

  • distinct_count calculates the number of distinct values in a dimension or measure, grouped by the chosen dimension or dimensions.

  • max returns the maximum value of the specified measure, grouped by the chosen dimension or dimensions.

  • min returns the minimum value of the specified measure, grouped by the chosen dimension or dimensions.

  • percentile computes the nth percentile of the specified measure, grouped by the chosen dimension or dimensions.

  • sum adds the set of numbers in the specified measure, grouped by the chosen dimension or dimensions.

  • stdev calculates the standard deviation of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a sample.

  • stdevp calculates the standard deviation of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a biased population.

  • var calculates the variance of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a sample.

  • varp calculates the variance of the set of numbers in the specified measure, grouped by the chosen dimension or dimensions, based on a biased population.

  • For each aggregation, there is also a conditional aggregation. These perform the same type of aggregation, based on a condition. Conditional aggregations include avgIf, countIf, distinct_countIf, maxIf, minIf, sumIf, stdevIf, stdevpIf, varIf, and varpIf.

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:

  • addDateTime adds or subtracts a unit of time to the date or time provided.

  • 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. (SPICE enabled for data sets only)

  • 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. (SPICE enabled)

  • left returns the specified number of leftmost characters from a string. (SPICE enabled)

  • locate locates a substring within another string, and returns the number of characters before the substring. (SPICE enabled)

  • ltrim removes preceding white space from a string. (SPICE enabled)

  • parseDate parses a string to determine if it contains a date value, and returns the date if found. (SPICE enabled for data sets only)

  • parseDecimal parses a string to determine if it contains a decimal value. (SPICE enabled for data sets only)

  • parseInt parses a string to determine if it contains an integer value. (SPICE enabled for data sets only)

  • parseJson parses values from a native JSON or from a JSON object in a text field.

  • replace replaces part of a string with a new string. (SPICE enabled)

  • right returns the specified number of rightmost characters from a string. (SPICE enabled)

  • rtrim removes following white space from a string. (SPICE enabled)

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

  • strlen returns the number of characters in a string. (SPICE enabled)

  • substring returns the specified number of characters in a string, starting at the specified location. (SPICE enabled)

  • toLower formats a string in all lowercase. (SPICE enabled)

  • toString formats the input expression as a string. (SPICE enabled for data sets only)

  • toUpper formats a string in all uppercase. (SPICE enabled)

  • trim removes both preceding and following white space from a string. (SPICE enabled)

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:

Lookup-based functions:

  • difference calculates the difference between a measure based on one set of partitions and sorts, and a measure based on another.

  • lag calculates the lag (previous) value for a measure.

  • lead calculates the lead (following) value for a measure.

  • percentDifference calculates the percentage difference between the current value and a comparison value.

Over functions:

  • avgOver calculates the average of a measure over one or more dimensions.

  • countOver calculates the count of a field over one or more dimensions.

  • maxOver calculates the maximum of a measure over one or more dimensions.

  • minOver the minimum of a measure over one or more dimensions.

  • percentileOver calculates the nth percentile of a measure partitioned by a list of dimensions.

  • percentOfTotal calculates the percentage a measure contributes to the total.

  • sumOver calculates the sum of a measure over one or more dimensions.

  • stdevOver calculates the standard deviation of the specified measure, partitioned by the chosen attribute or attributes, based on a sample.

  • stdevpOver calculates the standard deviation of the specified measure, partitioned by the chosen attribute or attributes, based on a biased population.

  • varOver calculates the variance of the specified measure, partitioned by the chosen attribute or attributes, based on a sample.

  • varpOver calculates the variance of the specified measure, partitioned by the chosen attribute or attributes, based on a biased population.

Ranking functions:

  • rank calculates the rank of a measure or a dimension.

  • denseRank calculates the rank of a measure or a dimension, ignoring duplicates.

  • percentileRank calculates the rank of a measure or a dimension, based on percentile.

Running functions:

  • runningAvg calculates a running average for a measure.

  • runningCount calculates a running count for a measure.

  • runningMax calculates a running maximum for a measure.

  • runningMin calculates a running minimum for a measure.

  • runningSum calculates a running sum for a measure.

Custom Window functions:

  • windowAvg calculates the average of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.

  • windowCount ccalculates the count of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.

  • windowMax calculates the maximum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.

  • windowMin calculates the minimum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.

  • windowSum calculates the sum of the aggregated measure in a custom window that is partitioned and sorted by specified attributes.