Amazon QuickSight – Operators

In Amazon QuickSight, operators are symbols that are used to perform operations on data. 

In Amazon QuickSight, the PEMDAS rule applies to the order of calculation when using functions and operators.

PEMDAS

PEMDAS stands for Parentheses, Exponents, Multiplication, and Division (from left to right), and Addition and Subtraction (from left to right). It’s a mnemonic that represents the order of operations in arithmetic. 

For example, when using an expression like “2 + 3 * 4”, QuickSight would first evaluate the multiplication operation (3 * 4), resulting in 12, and then add 2 to get the final result of 14.

By following the PEMDAS rule, you can ensure that your calculations are performed correctly and that you get the expected results. This is particularly important when working with complex expressions in QuickSight.

Some of the common operators supported in QuickSight are

1. Arithmetic Operators: (+, -, *, /) used for performing mathematical operations.
2. Comparison Operators: (=, <>, <, >, <=, >=) used for comparing values.
3. Logical Operators: (AND, OR, NOT) used for combining and evaluating conditions.
4. Conditional Operators: (IF, THEN, ELSE) used for making decisions based on certain conditions.
5. Text Operators: (CONCAT, LOWER, UPPER, TRIM) used for manipulating text data.
6. Date Operators: (DATEDIFF, DATEADD, NOW) used for working with date and time data.

By using these operators in QuickSight, you can perform various data operations and manipulations to analyze and visualize your data.

Examples

Arithmetic Operators

Here are some examples of how you can use arithmetic operators in Amazon QuickSight

Addition: To add two values together, you can use the “+” operator. 

For example: “2 + 3” would return 5.

Subtraction: To subtract one value from another, you can use the “-“ operator. 

For example: “5 – 2” would return 3.

Multiplication: To multiply two values, you can use the “*” operator. 

For example: “3 * 4” would return 12.

Division: To divide one value by another, you can use the “/” operator. 

For example: “12 / 4” would return 3.

Modulus: To calculate the remainder after division, you can use the MOD() function.          For example: “MOD(7,3)” would return 1.

You can use these operators to perform arithmetic operations on data in QuickSight and gain insights into trends and relationships in your data. For example, you could use the “+” operator to add up sales figures, the “-” operator to calculate the difference between two values, and the “*” operator to find the product of two values.

Comparison Operators

Here are some examples of how you can use comparison operators in Amazon QuickSight

Equal to: To determine if two values are equal, you can use the “=” operator. 

For example: “2 = 2” would return “True”.

Not equal to: To determine if two values are not equal, you can use the “<>” or “!=” operator. 

For example: “2 <> 3” would return “True”.

Less than: To determine if one value is less than another, you can use the “<” operator.  For example: “2 < 3” would return “True”.

Less than or equal to: To determine if one value is less than or equal to another, you can use the “<=” operator. 

For example: “2 <= 2” would return “True”.

Greater than: To determine if one value is greater than another, you can use the “>” operator.

 For example: “3 > 2” would return “True”.

Greater than or equal to: To determine if one value is greater than or equal to another, you can use the “>=” operator. 

For example: “2 >= 2” would return “True”.

In: To determine if a value is contained within a list of values, you can use the “IN” operator. 

For example: “3 IN [1,2,3,4]” would return “True”.

Not In: To determine if a value is not contained within a list of values, you can use the “NOT IN” operator. 

For example: “5 NOT IN [1,2,3,4]” would return “True”.

Between: To determine if a value is within a specified range, you can use the “BETWEEN” operator. 

For example: “3 BETWEEN 2 AND 4” would return “True”.

You can use comparison operators in QuickSight to compare values and make decisions based on the results. For example, you could use the “=” operator to find records that match a specific value, or the “<” operator to find records that fall below a certain threshold. 

You could use the “IN” operator to find records that match one of the multiple possible values, or the “BETWEEN” operator to find records that fall within a specified range.

Logical Operators

Here are some examples of how you can use logical operators in Amazon QuickSight

AND: To determine if two conditions are both true, you can use the “AND” operator. 

For example: “2 > 1 AND 3 > 2” would return “True”.

OR: To determine if either of two conditions is true, you can use the “OR” operator.

 For example: “2 > 1 OR 3 < 2” would return “True”.

NOT: To determine the opposite of a condition, you can use the “NOT” operator. 

For example: “NOT (2 > 3)” would return “True”.

You can use logical operators in QuickSight to combine multiple conditions and make decisions based on the results. For example, you could use the “AND” operator to find records that match multiple criteria, or the “NOT” operator to exclude records that do not meet specific conditions.

Conditional Operators

Here are some examples of how you can use conditional operators in Amazon QuickSight:

If-Then-Else: To make a decision based on a condition, you can use the “IF-THEN-ELSE” operator. 

For example: “IF 2 > 1 THEN ‘True’ ELSE ‘False'” would return “True”.

Case: To make multiple decisions based on multiple conditions, you can use the “CASE” operator. 

For example: “CASE WHEN 2 > 1 THEN ‘True’ WHEN 2 < 1 THEN ‘False’ ELSE ‘Unknown’ END” would return “True”.

You can use conditional operators in QuickSight to perform complex calculations and make decisions based on the results. For example, you could use the “IF-THEN-ELSE” operator to assign a value based on a specific condition, or the “CASE” operator to assign a value based on multiple conditions.

Text Operators

Here are some examples of how you can use text operators in Amazon QuickSight:

Concatenation: To combine two or more strings of text, you can use the “&” or “||” operator for concatenation. 

For example: “Hello” & ” ” & “World” would return “Hello World”.

Length: To determine the number of characters in a string, you can use the “LENGTH” function. 

For example: “LENGTH(‘Hello World’)” would return 11.

Substring: To extract a portion of a string, you can use the “SUBSTRING” function. 

For example: “SUBSTRING(‘Hello World’, 7, 5)” would return “World”.

Trim: To remove leading and trailing spaces from a string, you can use the “TRIM” function. 

For example: “TRIM(‘ Hello World ‘)” would return “Hello World”.

You can use text operators in QuickSight to manipulate and analyze text data. For example, you could use the “Concatenation” operator to combine multiple columns into a single string, or the “Length” function to determine the length of a string.

Date Operators

Here are some examples of how you can use date operators in Amazon QuickSight

Date difference: To calculate the difference between two dates, you can use the “DATEDIFF” function. 

For example: “DATEDIFF(date ‘2023-01-01’, date ‘2022-01-01’)” would return 365.

Date addition: To add a specified number of days, months, or years to a date, you can use the “DATEADD” function. 

For example: “DATEADD(date ‘2022-01-01’, interval 1 year)” would return “2023-01-01”.

Date extraction: To extract the year, month, day, hour, minute, or second from a date, you can use the “YEAR”, “MONTH”, “DAY”, “HOUR”, “MINUTE”, and “SECOND” functions. 

For example: “YEAR(date ‘2022-01-01’)” would return 2022.

Formatting dates: To format a date in a specific way, you can use the “FORMATDATE” function. 

For example: “FORMATDATE(date ‘2022-01-01’, ‘MM/dd/yyyy’)” would return “01/01/2022”.

NOW: This expression returns the current date and time. You can then format the result using the “FORMATDATE” function, or use it in a calculation to determine the difference between the current date and a date in your data.

You can use date operators in QuickSight to manipulate and analyze date data. For example, you could use the “Date difference” function to calculate the number of days between two dates, or the “Formatting dates” function to display dates in a specific format.