Amazon QuickSight – Functions By Category
Categories of functions available in Amazon QuickSight are
- Aggregate functions: These functions aggregate data and include functions such as “SUM”, “AVG”, “MIN”, “MAX”, “COUNT”, “COUNTD”, and “MEDIAN”.
- Conditional functions: These functions perform conditional operations and include functions such as “IF”, “AND”, “OR”, and “NOT”.
- Date functions: These functions work with date values and include functions such as “NOW”, “DATEDIFF”, “DATEADD”, “YEAR”, “MONTH”, “DAY”, “HOUR”, “MINUTE”, and “SECOND”.
- Numeric functions: These functions perform mathematical calculations and include functions such as “ABS”, “CEIL”, “FLOOR”, “MOD”, “POWER”, and “ROUND”.
- Mathematical functions: These functions perform mathematical operations and include functions such as “LOG”, “LN”, “SQRT”, and “EXP”.
- String functions: These functions manipulate text values and include functions such as “CONCAT”, “LEN”, “LOWER”, and “UPPER”.
- Table calculations: These functions perform calculations based on data within a table, and include functions such as “RANK”, “PERCENTILE”, and “WINDOW”.
You can use these functions to perform complex calculations, manipulate data, and create custom expressions for use in your visualizations. The exact functions available in Amazon QuickSight may vary based on the version of QuickSight you are using.
Aggregate Functions
Aggregate functions are mathematical operations that summarize data by performing a calculation on multiple values and returning a single value. In Amazon QuickSight, some common aggregate functions are:
SUM: calculates the sum of a set of numbers
Example: You can use the SUM aggregate function to calculate the total sales of your company. To do this, you would select the SUM function and apply it to the “Total Sales” column in your data source. The result would be a single value representing the sum of all sales.
AVG: calculates the average of a set of numbers
Example: You can use the AVG aggregate function to calculate the average price of products sold by your company. To do this, you would select the AVG function and apply it to the “Price” column in your data source. The result would be a single value representing the average price of products.
MIN: returns the minimum value from a set of numbers
Example: You can use the MIN aggregate function to determine the lowest temperature recorded in a given time period. To do this, you would select the MIN function and apply it to the “Temperature” column in your data source. The result would be a single value representing the minimum temperature recorded.
MAX: returns the maximum value from a set of numbers
Example: You can use the MAX aggregate function to determine the highest sales recorded in a given time period. To do this, you would select the MAX function and apply it to the “Total Sales” column in your data source. The result would be a single value representing the highest sales recorded.
COUNT: counts the number of items in a set
Example: You can use the COUNT aggregate function to count the number of products sold by your company. To do this, you would select the COUNT function and apply it to the “Product Name” column in your data source. The result would be a single value representing the total number of products sold.
MEDIAN: calculates the median of a set of numbers
Example: You can use the MEDIAN aggregate function to determine the median salary of employees in your company. To do this, you would select the MEDIAN function and apply it to the “Salary” column in your data source. The result would be a single value representing the median salary of employees.
MODE: calculates the mode of a set of numbers
Example: You can use the MODE aggregate function to determine the most frequently sold product by your company. To do this, you would select the MODE function and apply it to the “Product Name” column in your data source. The result would be a single value representing the most frequently sold product.
In Amazon QuickSight, you can apply these aggregate functions to fields in your data sources to create summary reports, visualizations, and dashboards.
Conditional Functions
Conditional functions in Amazon QuickSight allow you to create conditional statements and perform actions based on specific conditions. Some common conditional functions in QuickSight
IF/ELSE: The IF/ELSE function evaluates a condition and returns one value if the condition is true and another value if the condition is false.
Example: IF Total Sales > $100,000 THEN “High Sales” ELSE “Low Sales”
SWITCH: The SWITCH function is similar to the IF/ELSE function, but it allows you to evaluate multiple conditions and return a corresponding value for each condition.
Example: SWITCH Product Category, “Electronics” -> “High Demand”, “Fashion” -> “Moderate Demand”, ELSE “Low Demand”
CASE: The CASE function is similar to the SWITCH function, but it allows you to specify conditions using conditional expressions.
Example: CASE WHEN Total Sales > $100,000 THEN “High Sales” WHEN Total Sales > $50,000 THEN “Moderate Sales” ELSE “Low Sales”
NULLIF: The NULLIF function returns NULL if the two arguments are equal, and the first argument otherwise.
Example: NULLIF(Total Sales, $0) returns NULL if Total Sales is equal to $0, and Total Sales otherwise.
NOT IN: The NOT IN function returns a Boolean value indicating whether a specified expression does not match any value in a list of values.
Example: NOT IN(Region, “North”, “South”) returns true if Region is not “North” or “South”, and false otherwise.
ISNULL: The ISNULL function returns a Boolean value indicating whether a specified expression is NULL.
Example: ISNULL(Total Sales) returns true if Total Sales is NULL, and false otherwise.
ISNOTNULL: The ISNOTNULL function returns a Boolean value indicating whether a specified expression is not NULL.
Example: ISNOTNULL(Total Sales) returns true if Total Sales is not NULL, and false otherwise.
COALESCE: The COALESCE function returns the first non-NULL expression in a list of expressions.
Example: COALESCE(Total Sales, 0) returns Total Sales if it is not NULL, and 0 otherwise.
IN: The IN function returns a Boolean value indicating whether a specified expression matches any value in a list of values.
Example: IN(Region, “North”, “South”) returns true if Region is “North” or “South”, and false otherwise.
In Amazon QuickSight, you can use these conditional functions to create calculated fields that return specific values based on certain conditions. These calculated fields can be used in your visualizations and reports to provide additional insights into your data.
Numeric Functions
Amazon QuickSight provides a range of numeric functions that you can use to perform mathematical operations on your data. Some common numeric functions in QuickSight are:
ABS: The ABS function returns the absolute value of a specified number.
Example: ABS(-123) returns 123.
ROUND: The ROUND function returns a specified number rounded to a specified number of decimal places.
Example: ROUND(123.456, 2) returns 123.46.
CEIL: The CEIL function returns the smallest integer greater than or equal to a specified number.
Example: CEIL(123.456) returns 124.
FLOOR: The FLOOR function returns the largest integer less than or equal to a specified number.
Example: FLOOR(123.456) returns 123.
TRUNC: The TRUNC function returns the specified number truncated to a specified number of decimal places.
Example: TRUNC(123.456, 2) returns 123.45.
POW: The POW function returns the result of a specified number raised to a specified power.
Example: POW(2, 3) returns 8.
SQRT: The SQRT function returns the square root of a specified number.
Example: SQRT(9) returns 3.
LOG: The LOG function returns the logarithm of a specified number to a specified base.
Example: LOG(100, 10) returns 2.
DECIMALT0INT: The DECIMALT0INT function converts a decimal number to an integer by rounding down to the nearest integer.
Example: DECIMALTOINT(123.456) returns 123.
INTTODECIMAL: The INTTODECIMAL function converts an integer to a decimal number.
Example: INTTODECIMAL(123) returns 123.00.
These numeric functions can be used in calculated fields or expressions in Amazon QuickSight to perform mathematical operations on your data and derive insights from your data.
Mathematical Functions
There are a few mathematical functions that can be used in QuickSight to perform advanced mathematical operations on your data, including logarithmic calculations, square roots, exponential calculations, and more. This can help you gain insights into the relationships and trends in your data, and make informed decisions based on the results.
MOD(number, divisor): returns the remainder when dividing “number” by “divisor”.
Example: MOD(15, 4) returns 3, since 15 divided by 4 equals 3 with a remainder of 3.
LOG(expression): returns the logarithm of “expression” to the base 10.
Example: LOG(100, 10) returns 2, since 10 to the power of 2 equals 100.
LN(expression): returns the natural logarithm of “expression”.
Example: LN(2.71828) returns 1, since the natural logarithm of e (2.71828) is 1.
ABS(expression): returns the absolute value of “expression”.
Example: ABS(-123) returns 123.
SQRT(expression): returns the square root of “expression”.
Example: SQRT(9) returns 3, since 3 times 3 equals 9.
EXP(expression): returns e (the base of the natural logarithms) raised to the power of “expression”.
Example: EXP(1) returns 2.71828.
String Functions
Amazon QuickSight provides a range of string functions that you can use to manipulate and extract information from text data. Some common string functions in QuickSight are:
CONCAT: The CONCAT function combines two or more strings into a single string.
Example: CONCAT(“Hello”, ” “, “World”) returns “Hello World”.
LENGTH: The LENGTH function returns the number of characters in a string.
Example: LENGTH(“Hello World”) returns 11.
SUBSTRING: The SUBSTRING function returns a portion of a string, starting at a specified position and with a specified length.
Example: SUBSTRING(“Hello World”, 1, 5) returns “Hello”.
UPPER: The UPPER function converts a string to uppercase.
Example: UPPER(“Hello World”) returns “HELLO WORLD”.
LOWER: The LOWER function converts a string to lowercase.
Example: LOWER(“Hello World”) returns “hello world”.
TRIM: The TRIM function removes leading and trailing spaces from a string.
Example: TRIM(” Hello World “) returns “Hello World”.
REPLACE: The REPLACE function replaces all occurrences of a specified string with another string.
Example: REPLACE(“Hello World”, “o”, “a”) returns “Hella Warld”.
These string functions can be used in calculated fields or expressions in Amazon QuickSight to manipulate and extract information from text data in your data set.
Table Calculations
Running Total: A running total calculation computes the cumulative sum of a field over time.
Example: To calculate the running total of sales, you would create a calculated field using the following formula
SUM([Sales]) OVER ([Date] ORDER BY [Date])
Moving Average: A moving average calculation computes the average of a field over a specified number of periods.
Example: To calculate the 3-month moving average of sales, you would create a calculated field using the following formula
MAVG(SUM([Sales]), 3, [Date])
Percent of Total: A percent of total calculation calculates the percentage of a field in relation to the total of that field.
Example: To calculate the percent of total sales for each region, you would create a calculated field using the following formula
SUM([Sales]) / TOTAL(SUM([Sales]))
Year-to-Date: A percent of total calculation calculates the percentage of a field in relation to the total of that field.
Example: To calculate the year-to-date total of sales, you would create a calculated field using the following formula
SUM([Sales]) OVER ([Date] ORDER BY [Date], [Region], [Product])
Rank: A rank calculation assigns a rank to each value in a field, based on its value relative to the other values in the field.
Example: To calculate the rank of sales for each region, you would create a calculated field using the following formula
RANK(SUM([Sales]), [Region])
Differences: A differences calculation calculates the difference between the current value and the previous value for a field.
Example: To calculate the difference between the current value and the previous value for sales, you would create a calculated field using the following formula:
[Sales] – PREVIOUS_VALUE([Sales])
These are just a few examples of table calculations that you can perform in Amazon QuickSight. The specific formula for a table calculation will depend on the data in your data set and the insights you want to derive from that data.