Tableau Formatting Literals

Formatting Literals

When you are using functions you will sometimes want to use literal expressions to represent numbers, strings, dates, and more. A literal expression signifies a constant value that is represented “as is.” For example, you may have a function where your input is a date. Rather than just type “May 1, 2005”, which would be interpreted a a string, you would type #May 1, 2005#, which is equivalent to using a date function to convert the argument from a string to a date (refer to Date Functions). You can use numeric, string, date, boolean, and Null literals. The way to format each of these literals is described below.

Numeric Literals

A numeric literal is written exactly like you usually write numbers. If you want to input the number one as a numeric literal you would type 1. Subsequently, if you want to input the number 3.1415 as a numeric literal you would type 3.1415.

String Literals

A string literal can be written either using single quotations or double quotations. If your string has a single or double quotation within it, simply type the symbol twice. For example, if you wanted to input the string “cat” as a string literal you could type ‘cat’ or “cat”. Additionally, if you want to type the string “She’s my friend.” as a string literal you could type ‘She’’s my friend.’ or “She’s my friend.”

Date Literals

Date literals are signified by the pound symbol (#). If you wanted to input the date “August 22, 2005” as a literal date you would type #August 22, 2005#.

Boolean Literals

Boolean literals are written as either true or false. If you wanted to input “true” as a boolean literal you would type true.

Null Literals

Null literals are written simply as Null. If you wanted to input “Null” as a Null literal you would type Null.

Details (Officials)

Tableau Operators

Operators

To create calculated fields and formulas, you need to understand the operators supported by Tableau. This section discusses the basic operators that are available and the order (precedence) of operations.

+ (addition)

This means addition when applied to numbers and concatenation when applied to strings. When applied to dates, it can be used to add a number of days to a date. For example,
7 + 3
Profit + Sales
'abc' + 'def' = 'abcdef'
#April 15, 2004# + 15 = #April 30, 2004#

– (subtraction)

This means subtraction when applied to numbers and negation if applied to an expression. When applied to dates, it can be used to subtract a number of days from a date. Hence it can also be used to calculate the difference in days between two dates. For example,
7 - 3
Profit - Sales
-(7+3) = -10
#April 16, 2004# - 15 = #April 1, 2004#
#April 15, 2004# - #April 8, 2004# = 7

* (multiplication)

This means numeric multiplication. For example, 5 * 4 = 20.

/ (division)

This means numeric division. For example, 20 / 4 = 5.

% (modulo)

Returns the remainder of a division operation. For example, 9 % 2 returns 1 because 2 goes into 9 four times with a remainder of 1. Modulo can only operate on integers.

= =, =, >, <, >=, <=, !=, <>(comparisons)

These are the basic comparison operators that can be used in expressions. Their meanings are as follows: = = or = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), != and <> (not equal to).
Each operator compares two numbers, dates, or strings and returns a boolean (TRUE or FALSE).

^ (power)

This symbol is equivalent to the POWER function. It raises a number to the specified power.
For example:
6^3 = 216

AND

This is a logical operator. An expression or a boolean must appear on either side of it. For example,
IIF(Profit =100 AND Sales =1000, "High", "Low")
If both expressions are TRUE (i.e., not FALSE and not UNKNOWN), then the result is TRUE. If either expression is UNKNOWN, then the result is UNKNOWN. In all other cases, the result is FALSE.
If you create a calculation in which the result of an AND comparison is displayed on a worksheet, Tableau displays TRUE and FALSE. If you would like to change this, use the Format area in the format dialog.

OR

This is a logical operator. An expression or a boolean must appear on either side of it. For example,
IIF(Profit =100 OR Sales =1000, "High", "Low")
If either expression is TRUE, then the result is TRUE. If both expressions are FALSE, then the result is FALSE. If both expressions are UNKNOWN, then the result is UNKNOWN.
If you create a calculation in which the result of an OR comparison is displayed on a worksheet, Tableau displays TRUE and FALSE. If you would like to change this, use the Format area in the format dialog. The OR operator employs "short circuit evaluation." This means that if the first expression is evaluated to be TRUE, then the second expression is not evaluated at all. This can be helpful if the second expression results in an error when the first expression is TRUE, because the second expression in this case is never evaluated.

NOT

This is a logical operator. It can be used to negate another boolean or an expression. For example,
IIF(NOT(Sales = Profit),"Not Equal","Equal")

Precedence

All operators are evaluated in a specific order. For example, 2*1+2 is equal to 4 and not equal to 6. The reason is that the * operator is always evaluated before the + operator.
The following table shows the order in which operators are evaluated. The first line has the highest precedence. Operators on the same line have the same precedence. If two operators have the same precedence they are evaluated from left to right in the formula.

Precedence	Operator
1	– (negate)
2	^ (power)
3	*, /, %
4	+, –
5	==, >, <, >=, <=, !=
6	NOT
7	AND
8	OR

Parentheses can be used as needed. Operators that appear within parentheses are evaluated before those outside of parentheses, starting from the innermost parentheses and moving outward. For example, (1+ (2*2+1)*(3*6/3)) = 31.

Details (Officials)

Tableau Functions

Functions

The calculation functions are grouped into categories. These are the same categories used in the calculation editor. The aggregate functions such as sum, average, and so on are described in Aggregations.
For information on calculations, see Calculated Fields.

Number Functions

String Functions

Date Functions

Type Conversion

Logical Functions

Aggregate Functions

Pass-Through Functions (RAWSQL)

User Functions

Table Calculation Functions

Additional Functions

Details(Officials)

Tableau Data Types

Data Types

Tableau supports string, date/datetime, number, and boolean data types. These data types are automatically handled in the proper fashion. However, if you create calculated fields of your own, you need to be aware of how to use and combine the different data types in formulas. For example, you cannot add a string to a number. Also, many functions that are available to you when you define a calculation only work when they are applied to specific data types. For example, the DATEPART() function can accept only a date/datetime data type as an argument. So, you can write DATEPART('year',#April 15,2004#) and expect a valid result: 2004. You cannot write DATEPART('year',"Tom Sawyer") and expect a valid result. In fact, this example returns an error because "Tom Sawyer" is a string, not a date/datetime.

Although Tableau will attempt to fully validate all calculations, some data type errors cannot be found until the query is run against the database. These issues appear as error dialogs at the time of the query rather than in the calculation dialog box.

The data types supported by Tableau are described below. Refer to Type Conversion to learn about converting from one data type to another.

STRING

A sequence of zero or more characters. For example, "Wisconsin", "ID-44400", and "Tom Sawyer" are all strings. Strings are recognized by single or double quotes. The quote character itself can be included in a string by repeating it. For example, ‘O''Hanrahan’.

DATE/DATETIME

A date or a datetime. For example "January 23, 1972" or "January 23, 1972 12:32:00 AM". If you would like a date written in long-hand style to be interpreted as a a date/datetime, place the # sign on either side of it. For instance, “January 23, 1972” is treated as a string data type but #January 23, 1972# is treated as a date/datetime data type.

NUMBER

Numerical values in Tableau can be either integers or floating-point numbers.
With floating-point numbers, results of some aggregations may not always be exactly as expected. For example, you may find that the SUM function returns a value such as -1.42e-14 for a column of numbers that you know should sum to exactly 0. This happens because the Institute of Electrical and Electronics Engineers (IEEE) 754 floating-point standard requires that numbers be stored in binary format, which means that numbers are sometimes rounded at extremely fine levels of precision. You can eliminate this potential distraction by using the ROUND function (see Number Functions or by formatting the number to show fewer decimal places.
Operations that test floating point values for equality can behave unpredictably for the same reason. Such comparisons can occur when using level of detail expressions as dimensions, in categorical filtering, creating ad-hoc groups, creating IN/OUT sets, and with data blending.

Note: The largest signed 64-bit integer is 9,223,372,036,854,775,807. When connecting to a new data source, any column with data type set to Number (Whole), can accommodate values up to this limit; for larger values, Tableau will use floating point.

BOOLEAN

A field that contains the values TRUE or FALSE. An unknown value arises when the result of a comparison is unknown. For example, the expression 7 > Null yields unknown. Unknown booleans are automatically converted to Null.

Details(Officials)