In our previous examples, we learned the different way to create a Data Source Connection and Joins for our Tableau Reports. When we create a data source and import the data, then it is not mandatory we will get all the information we required for reporting. Sometimes we need to add manual calculations to retrieve the correct information from our Data Source. This article is dedicated for Operators in Tableau.
Following are the Key Points which we will cover in this article.
- Operators in Tableau
- Create Calculated Columns with Operators
Operators in Tableau
An operator is a symbol used to perform special mathematical or logical operations. We will check different operators to create calculated columns in Tableau.
Following are the different operators available in Tableau
Mathematical Operators: Mathematical operators used to perform a calculation on numeric data types. The plus operator (+) is also used with strings to perform strings concatenation. The plus (+) and the minus (-) operators are also used with date type.
+ for additions, adding dates, and string concatenation
Example: 1+10=11, 'Hello, '+'vLemonn'='Hello, vLemonn'
- for subtraction (including dates) and negation
Example: 2-1=1
* for multiplication
Example: 2*5=10
/ for division
Example: 5/2=2.5
% For the modular division, which returns reminder
Example: 5%2=1
^ exponents
Example: 2^5=32
Comparison Operators:
Comparison Operators are used to comparing two numbers, dates and strings. It will return a Boolean value (True or False) based on compression. The result will be used in the expression for decisions. For example, if a number is less than (<) 10 then increment it with 1. Else do nothing.
Equals To (=): Equals To symbolic representations “=”. It is used to compare if the Left and the right values are equal or not.
Example
1=1 Result True
1=2 Result False
‘Hello, vLemonn’=Hi, Mayank’ Result False
Greater Than (>): It is used to compare if the left value is greater than the right one.
Example
1 > 2 Result False
2>1 Result True
Less Than (<): It is used to compare if the left value is greater than the right one.
Example
1<2 Result True
2<1 Result False
Greater Than Equals to (>=) It is used to compare if the left value is greater than or equals to the right one.
Example
1>=2 Result False
(1+1)>= 2 Result True (Equals)
2>=1 Result False (Greater)
Less Than Equals to (<=) It is used to compare if the left value is less than or equals to the right one.
Example
1<=2 Result True (Less)
(1+1) <= 2 Result True (Equals)
2<=1 Result False
Not Equals to (<>) It is used to compare if the left value and the right value are not the same.
Example
1<>1 Result false
1<>2 Result True
‘Hello, vLemonn’<>’Hi, Mayank’ Result True
Logical Operators:
It is used to combine two are more conditions. The result for a Logic operator is a Boolean value. For example, if we need to check if the number is in between 5 to 10 then we can use the following expression. N> 5 AND N <10. In the expression, we are combining two different conditions with the “AND” operator. If the value of N is in between 5 and 10 then the result is true.
AND: Returns true if all the conditions are true.
OR: Returns true if either of the available condition is true.
NOT:
Operators Precedence
It is important to consider the operator precedence while using it inside a calculation. Why? Check the following calculation and predict the result
10-2^3+4*5
A) The result is 2580. (((10-2)^3)+4)*5
B) The result is -8388598. (10-(2^(3+(4*5))))
C) Result is 22. 10+((-2)^3)+(4*5)
D) All the above
If we perform the calculation from left to right, then the answer is (A). Similarly, if we perform the calculation from right to left, then the answer is (B). But in the computer world, some operators have precedence over the others. Following is the operator’s precedence table. Off Corse, the top of the list will evaluate first.
| Precedence | Operator |
| 1 | –(negate) |
| 2 | ^ (power) |
| 3 | *, /, % |
| 4 | +,- |
| 5 | ==, >, <, >=, <=, != |
| 6 | NOT |
| 7 | AND |
| 8 | OR |
As per the above table, the correct answer is (C). If the two operators have the same precedence, then they are evaluated from left to right. If you want to avoid any confusion, then you can use the parenthesis. With parenthesis, the innermost will evaluate first and so on.
This is all about theory. Let’s check all the operators in Tableau. For this article, we will use the following Sample Data File.
Let’s create a data source connection using the above sample file. After this, create a calculated column. To Create a calculated column right click on any column and select Create Calculated Field….
In the Calculated Field dialog, we need to provide the Calculated Field Name and the Expression. For our first calculated field, the name is “TableauOperators” with Expression “1+1”. To save the calculated field click on the Ok button.
Our first calculated field is created, and we can check it in the output. In our first calculated field, we used a plus (+) operator. If you want to add values in two columns, then use the two columns in the expression. To edit the existing calculated field right click on the calculated field and select edit.
In the expression, we added Cod Charges and Delivery Charge. To apply this change, you can click on the Apply button.
Now, what is the difference between Apply and OK? When we use the OK button, our Calculated field dialog box will be closed. With Apply, Tableau will apply all the new changes and refresh the data, but the calculated field dialog box stays open. We can use the Apply button to check the latest changes without closing the dialog box.
In the above example, we used a + operator to add COD charges and the delivery charges. What if we need to combine two string? For this, we will remove the current expression. For our next example, we will add Brand and Title column.
As shown in the above image in our expression, we have three strings. First one is the Brand, next is a hard-coded string “ > “, and in the last, we have a Title. We can use the plus (+) operator for string concatenation. Now you may ask why we needed a hard-coded string. It is used to separate the Brand and Title string. Why? Because “Brand > Title” is more readable in comparison with “BrandTItle”.
We used our plus operator with number and string. Next, we need to use it with a date column. When we used a plus operator with a date column, we can add no of days in the date.
As we used a Plus operator with Number and date, we can also use a negative or minus operator with Number and Date. We can’t use the minus operator with string. Following is the different example for Minus operator. In the example, we reduce 10 days from the Offer Last Date.
As we mentioned it is important to consider the operator precedence while using it in a calculation. This is the reason why we removed parenthesis from the Discount Percentage column. Its value is changed drastically. Why?
Following is the calculation for the first row.
48084 – 34644 / 48084 * 100
As per the operator – (negate) is with the highest precedence. So, from our expression “– 34644”, it will be evaluated first. Next, division “/” and the multiplication operators have the same precedence, but higher than the minus operator. In such a case “– 34644 / 48084 * 100“ expression will evaluate first from left to right. First, calculated this “– 34644 / 48084 = -0.7204891439980035”. Then “-0.7204891439980035” multiply it by 100. -0.7204891439980035 * 100 = -72.04891439980035. Finally, subtract “-72.04891439980035” from 48084. The result is 48011.9510856002. After rounding it was 48011.95. I hope now you can understand the importance of Operator Precedence.
Note: Operator precedence is not the Tableau Terminology. It is applicable for most of the computer tools.
Modular Division
When we use the Modular division, it always returns the remainder. For example, 5%2 is 1.11 % 3 is 2.
Exponent
It is used to set the power for a number. For example, if you need to find 2*2*2*2*2 then you can use 2^5. Similarly, 2^-2 is 0.25. It equals 2/2/2/2.
For the rest of the remaining operators, we will cover when we learned about BLOCKS. Following is the example for IF BLOCK.
