If you are accustomed to creating SQL statements in a database, the functions and syntax of Tableau’s calculated values should look familiar. If you are a spreadsheet expert, the syntax will be new but should not pose a significant challenge for you to learn.
Tableau’s formula-editing window provides help and error-checks the syntax of the formulas you create. Even if you have no experience, with a little practice you’ll find that you use some functions frequently. Table E-1 groups functions into thirteen categories.
Table E-1: Tableau Function Categories
Function Category | Category Capabilities |
Aggregate | Mathematical and statistical summaries of your data. |
Date | Calculate and parse date fields. |
Google Big Query | Functions that work with Big Query data sources only. |
Hadoop Hive | Functions that work with Hadoop Hive data sources only. |
Level of Detail (LOD) | Level of detail expressions support aggregation in calculations at dimension levels different from the view level. Computed at the data source unlike table calculations, totals, or reference lines. |
Logical | Conditional operations based on your data. |
Number | Arithmetic and trigonometric operations. |
RawSQL Pass Through | Pass SQL statements directly to the data source and then excute the statement within the data source. These functions do not work with every data source supported by Tableau. |
String | Functions for manipulating strings. |
String Pattern | Specialized string patter functions (REGEXP) that work with a subset of datasources including Text files, Google Big Query, PostgreSQL, Tableau Data Extracts, Microsoft Excel, Saleforce, and Oracle. |
Table Calculation | Functions that are executed within Tableau using the structure of the visualization for the calculation. |
Type Conversion | Change values from one data type to another. |
User | Information on the identify, domain, and membership of the current Tableau user. |
Adapted from the Tableau Manual
In the first edition of this book, 111 functions were listed. We now have 167 functions and qualifiers listed. Some of these functions are not available for every data source. Some can be used only with specific tools such as the R Project for Statistical Computing, Hadoop Hive, or Google Big Query. Restrictions on the use of any function in the list are indicated in the function reference.
Tableau Software provides abbreviated help for each function within the formula editing window, the product manual, and the website. This appendix supplements those resources by providing:
Examples are organized alphabetically by function name. Color encoding is used to identify fields, functions, and parameter entries. Care has been taken to match the color hues as they appear in Tableau’s formula editor.
Each entry contains one to three examples. There are a few (connection-specific functions) that don’t include formula examples. In those cases we refer you to the vendor manual. Examples are listed as basic, intermediate, or advanced. Please note that some function types—RAW SQL for example—are inherently more complicated than basic functions such as those for aggregation or dates. The difficultly is gauged within the function category only.
The RAW SQL functions are a special type of function in Tableau known as a pass-through function. These functions allow the user to send statements to the underlying database that are not evaluated by Tableau. This allows the user to call database functions that Tableau is unaware of. Tableau is aware of many built-in database functions and has mapped many of them to functions within Tableau, but depending on your data source, there are probably functions that Tableau doesn’t yet support. In addition to built-in database functions, the RAW SQL function allows you to call any function that the underlying data source supports—including user-defined functions. This makes the RAW SQL functions particularly powerful.
The name of each RAW SQL function is based on the type of function being passed to the database (Aggregate or Scalar) and the type of the return value. For example, RAWSQLAGG_INT()
passes an aggregate function and returns an integer value, and RAWSQL_REAL()
passes a scalar function and retruns a real value. The valid return types are:
BOOL
: A Boolean value.DATE
: A date value. Note that date types in databases usually omit time.DATETIME
: A date-time value. Note that date-time types usually include date and time.INT
: An integer value. Numbers without a decimal component.REAL
: A numeric value. Numbers with a decimal component.STR
: A string value. Text data.Choosing the incorrect scalar or aggregate function will cause an error within Tableau. Keep in mind that RAWSQL functions will not work for published data sources on Tableau Server.
Another thing to keep in mind when you are working with RAW SQL functions is that your underlying database will not understand the dimension and measure names within Tableau. To pass a dimension or measure into the RAW SQL expression, you must use the substitution syntax provided by Tableau. This syntax is similar to substitution syntax seen in other languages. This is demonstrated in the example that follows:
RAWSQL_INT("1000 + %1", [Order ID])
In this example, the RAWSQL_INT
function is being used to pass a simple expression to the database. The %1
will be replaced by the value of Order ID
in the expression. Notice that this example uses the scalar function and an integer return type.
Using the RAW SQL functions will let you expand the capabilities of Tableau in many ways. If you can write a function to perform the operation you require at the database level, then you can expose it to Tableau with these functions. Keep in mind that whenever you come across examples of RAW SQL usage that the examples are dependent upon the functions present in the database. For the examples in the remainder of this section, you will use a copy of the Superstore Orders dataset included with Tableau Desktop that has been loaded into a SQL Server 2012 instance. Some of the expressions used to demonstrate the pass-through queries may not work with your data sources.
Tableau added four new specialized functions that are for the statistical software tool R. These functions are all table calculations. If you are an R expert learning Tableau, it is important to understand how table calculations work before using them.
New functions have also been added that are for specific data sources such as Hadoop Hive or Google Big Query. In some cases, the functions work with only a subset of data sources, including text files, Microsoft Excel, Tableau Data Extracts, PosgreSQL, Salesforce, and Oracle data sources. Those limitations are indicated in the alphabetical function list example details. After this text is released, Tableau may expand the list of data sources supported by a function. Refer to Tableau’s online manual for the latest list of functions and data sources supported by each function.
Table E-2 shows every Tableau function available for the typical user. Depending on your data source, additional functions may be available from a particular database. Consult your database manual for additional commands not listed here. The remainder of Appendix E provides detailed explanations of each function. Code examples are provided for each function and are classified as basic, intermediate, and advanced.
Table E-2: Alphabetical Function List
# | Function Name | Type Function |
1 | ABS | Number |
2 | ACOS | Number |
3 | AND | Logical |
4 | ASCII | String |
5 | ASIN | Number |
6 | ATAN | Number |
7 | ATAN2 | Number |
8 | ATTR | Aggregate |
9 | AVG | Aggregate |
10 | CASE | Logical |
11 | CEILING | Number |
12 | CHAR | String |
13 | CONTAINS | String |
14 | COS | Number |
15 | COT | Number |
16 | COUNT | Aggregate |
17 | COUNTD | Aggregate |
18 | DATE | Type Conversion |
19 | DATEADD | Date |
20 | DATEDIFF | Date |
21 | DATENAME | Date |
22 | DATEPARSE | Date |
23 | DATEPART | Date |
24 | DATETIME | Type Conversion |
25 | DATETRUNC | Date |
26 | DAY | Date |
27 | DEGREES | Number |
28 | DIV | Number |
29 | DOMAIN | Google BigQuery |
30 | ELSE | Logical |
31 | ELSEIF | Logical |
32 | END | Logical |
33 | ENDSWITH | String |
34 | EXCLUDE | Logical |
35 | EXP | Number |
36 | FIND | String |
37 | FINDNTH | String |
38 | FIRST | Table Calculation |
39 | FIXED | LOD Aggregate |
40 | FLOAT | Type Conversion |
41 | FLOOR | Number |
42 | FULLNAME | User |
43 | GET_JSON_OBJECT | Hadoop Hive |
44 | GROUP_CONCAT | Google BigQuery |
45 | HEXBINX | Number |
46 | HEXBINY | Number |
47 | HOST | Google BigQuery |
48 | IF | Logical |
49 | IFNULL | Logical |
50 | IIF | Logical |
51 | INCLUDE | LOD Aggregate |
52 | INDEX | Table Calculation |
53 | INT | Type Conversion |
54 | ISDATE | Date, logical, String |
55 | ISFULLNAME | User |
56 | ISMEMBEROF | User |
57 | ISNULL | Logical |
58 | ISUSERNAME | User |
59 | LAST | Table Calculation |
60 | LEFT | String |
61 | LEN | String |
62 | LN | Number |
63 | LOG | Number |
64 | LOG2 | Google BigQuery |
65 | LOOKUP | Table Calculation |
66 | LOWER | String |
67 | LTRIM | String |
68 | LTRIM_THIS | Google BigQuery |
69 | MAKEDATE | Type Conversion |
70 | MAKEDATETIME | Type Conversion |
71 | MAKETIME | Type Conversion |
72 | MAX | Aggregate, date, number, string |
73 | MEDIAN | Aggregate |
74 | MID | String |
75 | MIN | Aggregate, date, number, string |
76 | MONTH | Date |
77 | NOT | Logical |
78 | NOW | Date |
79 | OR | Logical |
80 | PARSE_URL | Hadoop Hive |
81 | PARSE_URL_JQUERY | Hadoop Hive |
82 | PERCENTILE | Aggregate |
83 | PI | Number |
84 | POWER | Number |
85 | PREVIOUS_VALUE | Table Calculation |
86 | RADIANS | Number |
87 | RANK | Table Calculation |
88 | RANK_DENSE | Table Calculation |
89 | RANK_MODIFIED | Table Calculation |
90 | RANK_PERCENTILE | Table Calculation |
91 | RANK_UNIQUE | Table Calculation |
92 | RAWSQL_BOOL | Pass Through |
93 | RAWSQL_DATE | Pass Through |
94 | RAWSQL_DATETIME | Pass Through |
95 | RAWSQL_INT | Pass Through |
96 | RAWSQL_REAL | Pass Through |
97 | RAWSQL_STR | Pass Through |
98 | RAWSQLAGG_BOOL | Pass Through |
99 | RAWSQLAGG_DATE | Pass Through |
100 | RAWSQLAGG_DATETIME | Pass Through |
101 | RAWSQLAGG_INT | Pass Through |
102 | RAWSQLAGG_REAL | Pass Through |
103 | RAWSQLAGG_STR | Pass Through |
104 | REGEXP_EXTRACT | String |
105 | REGEXP_EXTRACT_NTH | String |
106 | REGEXP_MATCH | String |
107 | REGEXP_REPLACE | String |
108 | REPLACE | String |
109 | RIGHT | String |
110 | ROUND | Number |
111 | RTRIM | String |
112 | RTRIM_THIS | Google BigQuery |
113 | RUNNING_AVG | Table Calculation |
114 | RUNNING_COUNT | Table Calculation |
115 | RUNNING_MAX | Table Calculation |
116 | RUNNING_MIN | Table Calculation |
117 | RUNNING_SUM | Table Calculation |
118 | SCRIPT_BOOL | Table Calculation |
119 | SCRIPT_INT | Table Calculation |
120 | SCRIPT_REAL | Table Calculation |
121 | SCRIPT_STRING | Table Calculation |
122 | SIGN | Number |
123 | SIN | Number |
124 | SIZE | Table Calculation |
125 | SPACE | String |
126 | SPLIT | String |
127 | SQRT | Number |
128 | SQUARE | Number |
129 | STARTSWITH | String |
130 | STDEV | Aggregate |
131 | STDEVP | Aggregate |
132 | STR | Type Conversion |
133 | SUM | Aggregate |
134 | TAN | Number |
135 | THEN | Logical |
136 | TIMESTAMP_TO_USEC | Google BigQuery |
137 | TLD | Google BigQuery |
138 | TODAY | Date |
139 | TOTAL | Table Calculation |
140 | TRIM | String |
141 | UPPER | String |
142 | USEC_TO_TIMESTAMP | Google BigQuery |
143 | USERDOMAIN | User |
144 | USERNAME | User |
145 | VAR | Aggregate |
146 | VARP | Aggregate |
147 | WHEN | Logical |
148 | WINDOW_AVG | Table Calculation |
149 | WINDOW_COUNT | Table Calculation |
150 | WINDOW_MAX | Table Calculation |
151 | WINDOW_MEDIAN | Table Calculation |
152 | WINDOW_MIN | Table Calculation |
153 | WINDOW_PERCENTILE | Table Calculation |
154 | WINDOW_STDEV | Table Calculation |
155 | WINDOW_STDEVP | Table Calculation |
156 | WINDOW_SUM | Table Calculation |
157 | WINDOW_VAR | Table Calculation |
158 | WINDOW_VARP | Table Calculation |
159 | XPATH_BOOLEAN | Hadoop Hive |
160 | XPATH_DOUBLE | Hadoop Hive |
161 | XPATH_FLOAT | Hadoop Hive |
162 | XPATH_INT | Hadoop Hive |
163 | XPATH_LONG | Hadoop Hive |
164 | XPATH_SHORT | Hadoop Hive |
165 | XPATH_STRING | Hadoop Hive |
166 | YEAR | Date |
167 | ZN | Logical, number |
The ABS function returns the absolute value of the given number. The absolute value can also be seen as its distance from zero. This function is useful when you want to find out the difference between two values, regardless of whether that difference is positive or negative.
ABS(number)
ABS([Budget Variance])
ABS(SUM([Budget Sales])-SUM([Sales]))/SUM([Budget Sales])
The ACOS function returns the arc cosine of the given number. This is the inverse of the COS function.
ACOS(number)
ACOS(0.5)
DEGREES(ACOS(0.5))
This is an application of combined trigonometry functions in Tableau. This syntax calculates the distance between two geographical locations, whereby 3959 is the value of the average radius of the earth.
3959 * ACOS(SIN(RADIANS([Lat1])) * SIN(RADIANS([Lat2]))
+ COS(RADIANS([Lat1])) * COS(RADIANS([Lat2])) *
COS(RADIANS([Long2])— RADIANS([Long1])).
3959 * ACOS(SIN(RADIANS(36.105143)) * SIN(RADIANS(36.113231))
+ COS(RADIANS(36.105143)) * COS(RADIANS(36.113231))
* COS(RADIANS(-95.975677) - RADIANS(-97.103813)))
The AND qualifier allows multiple expressions to be combined and evaluated within one calculated field. If the two expressions on either side of the AND statement are true, or represent a Boolean value of 1 instead of 0, then the entire statement is considered to be true. If one or both of the expressions on either side of the AND statement are false, or represent a Boolean value of 0 instead of 1, then the entire statement is considered to be false.
IF SUM([Sales]) > 10,000 AND AVG([Discount]) > .1
THEN "Review" ELSE "OK"
END
IF (DATEPART('month', [Order Date]) = 6
AND DATEPART('year',[Order Date]) = 2014)
AND [Profit] < 0 THEN "Review" ELSE "OK"
END
ASCII is a character-encoding scheme that allows English characters, numbers, and symbols to be encoded into a corresponding number in the ASCII character set. The ASCII function returns the ASCII code for the first character in a given string. A Standard ASCII character set comprises 128 characters. These 128 ASCII characters can then be divided further into 4 equal groupings of 32 characters. The ASCII groups contain the following:
ASCII(String)
ASCII([Customer])
IIF (ASCII([Customer Name])<32, 'Non Printable Characters','Printable Characters')
ASCII(MID([Customer Name],FIND([Customer Name]," ")))
The ASIN
function returns the arcsine, in radians, of the given number. This is the inverse of the SIN
function.
ASIN(number)
ASIN(1)
DEGREES(ASIN(1))
The ATAN
function returns the arctangent of the given number. This is the inverse of the TAN
function. The result is given in radians in the range between –π/2 and π/2.
ATAN(number)
ATAN(1)
DEGREES(ATAN(1))
The ATAN2
function returns the arctangent of two given numbers (x and y). The result is in radians in the range between –π and π, excluding –π.
ATAN2(y number,x number)
ATAN2(1,1)
DEGREES(ATAN2(-1,-1))
The ATTR
function evaluates all of the members contained within the specified field and returns a single value (if all of the values are identical) or the symbol *
if more than one value exists in the set. The *
symbol is meant to denote a special kind of NULL—one containing many values, instead of the more typical use of NULL, no values.
When the ATTR
function is applied to a dimension that is expressed in a hierarchal view of the data, it will treat that field as a label and will cause aggregate values to be calculated based on the remaining dimensions. Figure E-1 shows this result.
A good explanation of ATTR
can be found in a Tableau Forum entry by Joe Mako1 in which Joe expresses the logic used by the ATTR
function using this formula:
IF MIN([field])=(MAX([field]) THEN MIN([field]) ELSE "*" END
To restate Joe’s logic, if the minimum value and the maximum value of the set of numbers returned from the database are the same, then use the minimum value, and if they are not the same, return the *
symbol.
ATTR([field])
ATTR([Sub-Category])
The AVG
function returns the mean of the expression. It is calculated as the sum of all of the numbers for the expression divided by the count of the number of records in that expression. For example, if you have a set containing 24, 30, 15, 5, 18 the average of those five numbers will be: (24 +30+15+5+16)/5 =15.
AVG([Discount])
AVG(DATEDIFF('day',[Order Date],[Ship Date]))
AVG(IF(DATEDIFF('day',[Order Date],[Ship Date])<=[Time to Ship Goal])
THEN 1 ELSE 0 END)
The CASE
function is provided with an expression/data field, which can be defined as the CASE statement source field. The data values located within this source field are compared against a sequence of values specified in the WHEN
clauses. If any of the values within the expression match a WHEN
specification, the corresponding THEN
value is returned. If no match is found, then the default return expression, specified in the ELSE
clause, is used. If there is no ELSE
clause, NULL is returned when no match is found.. The CASE
function can be duplicated using IF
or IIF
functions. Typically CASE
is easier to use and more concise.
CASE expression WHEN value1 THEN return1 WHEN value2 THEN return2 ......
ELSE default return
END
CASE [Month]
WHEN 1 THEN "January"
WHEN 2 THEN "February"
WHEN 3 THEN "March"
WHEN 4 THEN "April"
ELSE "Not required"
END
CASE (LEFT([Customer Name],1))
WHEN 'A' THEN 'Customer name starts with A'
WHEN 'B' THEN 'Customer name starts with B'
WHEN 'C' THEN 'Customer name starts with C'
ELSE 'Customer names not starting with A, B or C'
END
The CEILING
function rounds numbers up to the next highest integer. This function will be applied according to the level of aggregation specified in the equation, meaning SUM
must be present to produce a rounded summation.
CEILING(41.09)
CEILING(SUM([Sales]))
CHAR
is a function that changes an ASCII code into its relevant String character. ASCII
and CHAR
functions perform the reverse of each other and both are fundamentally linked.
CHAR(Number)
CHAR(65)
CHAR(IIF (ASCII([Customer])> 96
and ASCII([Customer])<= 122,
ASCII([Customer])-32,
ASCII([Customer])))
CHAR(ASCII([Customer]))+ "." +
CHAR(ASCII(LTRIM(MID([Customer],FIND([Customer]," ")))))+ "."
The CONTAINS
function gives the user the ability to search for any sequence of characters (SUBSTRING
) that may be present within a searchable string. The CONTAINS
function returns a Boolean value of True or False.
CONTAINS(String, Substring)
CONTAINS([City],"New")
The COS
function returns the cosine of a given number specified in radians.
COS(number)
COS(PI()/8)
COS(RADIANS(60))
This is an application of combined trigonometry functions in Tableau. This syntax calculates the distance between two geographical locations, whereby 3959 is the value of the average radius of the earth.
3959 * ACOS(SIN(RADIANS([Lat1])) * SIN(RADIANS([Lat2]))
+ COS(RADIANS([Lat1])) * COS(RADIANS([Lat2])) *
COS(RADIANS([Long2])— RADIANS([Long1])).
3959 * ACOS(SIN(RADIANS(36.105143)) * SIN(RADIANS(36.113231))
+ COS(RADIANS(36.105143)) * COS(RADIANS(36.113231))
* COS(RADIANS(-95.975677) - RADIANS(-97.103813)))
Returns the cotangent of a given number specified in radians. The number is expressed in radians.
COT(number)
COT(PI( )/4)
COT(RADIANS(45))
This function returns the count of the items in a group. NULL values are not counted.
COUNT([Ship Date])
COUNT(IIF([Discount]=0,1,NULL))
COUNT(IIF([Discount]=0,1,NULL))/COUNT([Number of Records])
Count distinct returns the number of distinct items in a group. NULL values are not counted. Each unique value is counted only once.
COUNTD([Customer Name])
COUNTD([City]+[State])
COUNTD(IF([Country]=[Country Parameter])
THEN [Customer Name] ELSE NULL END)
The DATE
function converts a given input into a date. This is similar to the DATETIME
function, but doesn’t include time. This is especially useful when you have string dates in your data source or are building your own dates using other data sources.
DATE("March 15, 2013")
DATE([DateString])
DATE(STR([Year]) + '/'+ STR([Month]) + '/' + STR([Day]))
The DATEADD
function adds a specified time period to a given date. This function is useful when you want to calculate new dates based on another date in your dataset, to create reference lines in time series analysis, or to create dimensions to use for filtering.
DATEADD(date_part, increment, date, start_of_week)
The date_part
specifies the type of time period that is being added. It is always specified in single quotes and lowercase (for example, 'day'
). Increment specifies the exact amount of time to add. Table E-3 below displays the date_part
values that can be used with date functions.
Table E-3: Valid Date Function Date Parts
date_part | Values |
'year' | Four-digit year |
'quarter' | 1–4 |
'month' | 1–12 or "January," "February," and so on |
'dayofyear' | Day of the year: Jan 1 is 1, Feb 1 is 32, and so on |
'day' | 1–31 |
'weekday' | 1–7 or "Sunday," "Monday," and so on |
'week' | 1–52 |
'hour" | 0–23 |
'minute' | 0–59 |
'second' | 0–60 |
Source: Tableau Desktop Manual
The date variable in the formula is the base date used for the addition. This value can be a constant value (#2015-06-23#), field, parameter, or another function that returns a date.
DATEADD('day',3,[Order Date])
DATEADD('day', -30, TODAY())
DATEADD('month', -12, WINDOW_MAX(MAX([Date])))
The DATEDIFF
function calculates the time between two given dates. This is useful for creating additional metrics or dimensions for your analysis. It returns an integer value of date2-date1
expressed in units of date_part
.
DATEDIFF(date_part, date1, date2, start_of_week)
DatePart
specifies the type of time period that is being returned. It is always specified in single quotes and lowercase (for example: 'day'
). See the DATEADD
entry for valid datepart values. Date1
and Date2
are the actual dates used for subtraction. The values can be constants, fields, parameters, other functions that return dates, or combinations of any of these.
DATEDIFF('day', #June 3, 2012#, #June 5, 2012#)
DATEDIFF('day',[Ship Date], TODAY())
CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DATEDIFF('day',[OrderDate],[ShipDate])
WHEN 'Week' THEN DATEDIFF('week',[OrderDate],[ShipDate])
END
The DATENAME
function returns part of date as text. This function is useful for creating custom labels that go beyond what Tableau formatting can provide.
DATENAME(DatePart, Date)
DATENAME('month', #2012-06-03#)
DATENAME('month',[StartDate]) + ' to ' + DATENAME('month',[EndDate])
DATENAME('month', TOTAL(MIN([Date]))) + ' ' +
DATENAME('year', TOTAL(MIN([Date]))) + ' to ' +
DATENAME('month', TOTAL(MAX([Date]))) + ' ' +
DATENAME('year', TOTAL(MAX([Date])))
The DATEPARSE
function converts a string field into a date/datetime field. This is especially useful when you have string dates in your data source or are building your own dates using other data sources. Converting string dates to a true date/datetime field allows you to use Tableau’s autogenerated date hierarchy in views. The DATEPARSE
function is available for non-legacy Microsoft Excel and text file connections, MySQL, Oracle, PostgreSQL, and Tableau data extracts data sources. Your computer system will control locale-specific formats.
DATEPARSE(format, [String])
Format
provides a map of how the string field in the data source is laid out. For example, if your data looks like "20150807"
then set up the function with "yyyyMMdd"
in the format section. Double quotes (""
) should wrap the combination of symbols in your format. Note that capital M
is used to designate “months” and lowercase m
designates “minutes.” The symbols to be used for the format are defined by International Components for Unicode (ICU) formatting language. For a complete list of the syntax for these date symbols refer to the ICU website at: http://userguide.icu-project.org/formatparse/datetime.
String
references the existing string field in the data source you are converting to a date/datetime field.DATEPARSE("MMyyyy", "082015")
DATEPARSE("MMddyyyy hh:mma", "08072015 10:07AM")
DATEPARSE("MMddyy", RIGHT("Customer 1 – 080715", 6))
The DATEPART
function returns part of a date as an integer. This can be useful for certain calculations when you need to parse portions of a date.
DATEPART(DatePart, date)
DatePart
defines the portion of the date you require, such as week, month, or year. Date
is the actual date you want to extract from the original date.
DATEPART('month', #June 3, 2012#)
DATEPART('dayofyear',[Date])
IF DATEPART('hour',[Datetime]) < 12 THEN 'Morning'
ELSEIF DATEPART('hour',[Datetime]) < 16 THEN 'Afternoon'
ELSEIF DATEPART('hour',[Datetime]) < 21 THEN 'Evening'
ELSE 'Night'
END
The DATETIME
function converts a given input into a date and time. This is similar to the DATE
function but includes the time. This is especially useful when you have string dates in your data source, separate fields for date and time, or are building dates from other sources.
DATE("March 15, 2013 5:30 PM")
DATETIME(STR([Date]) + ' ' + [Time]),
DATE(STR([Year]) + '/' + STR([Month]) + '/' + STR([Day]) + ' ' + STR([Time]))
This function returns a date—truncated to the nearest specified date part. Think of this as an aggregating method for converting time into the desired level of detail while maintaining the date format. The date returned will always be the first day in the time period.
DATETRUNC(DatePart, date)
The DatePart
defines the date aggregation displayed (week, month, or year, and so on). Date
is the actual date used to extract the desired DatePart
.
DATETRUNC('Month', #March 14, 2013#)
DATETRUNC('week', MIN([Date]))
CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DATETRUNC('day', [Date])
WHEN 'Week' THEN DATETRUNC('week', [Date])
WHEN 'Month' THEN DATETRUNC('month', [Date])
END
This function returns an integer representing the day of the month for the given date. This is a shortened form of DATEPART ('day', [Date])
.
DAY(Date)
Date
is the date you want to extract from.
DAY(#March 14, 2013#)
DAY(DATEADD('day',[Date], 5 ))
CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DAY([Date])
WHEN 'Month' THEN MONTH([Date])
WHEN 'Year' THEN YEAR([Date])
END
The DEGREES
function converts a given number in radians to degrees.
DEGREES(number)
DEGREES(PI()*2)
The DIV
function accepts a numerator and denominator as input and then outputs the integer portion of the result. Any remainder left over from the division is not included in this output.
DIV(16,5)
DIV(INT(SUM([Sales])), COUNTD([Customer ID]))
Returns the domain of a given URL. The URL must include protocol for this function to work properly. This function only works with Google Big Query data sources.
DOMAIN([URL])
DOMAIN('http://www.twitter.com/DGM885')
The ELSE
qualifier can be used in conjunction with an IF
/THEN
statement to provide a default value or expression in the case that a specified IF
statement evaluates to false.
IF [Sales] >= [Quota] THEN "Goal Met"
ELSE "Needs Improvement"
END
IF [Order Priority] = "Critical" or [Order Priority] = "High"
THEN [Shipping Cost] * 2
ELSE [Shipping Cost] * 1.5
END
The ELSEIF
qualifier makes it possible to use more than one IF
statement inside one calculated field. Multiple IF
statements cannot be used inside a single calculated field, but any number of ELSEIF
statements may be stacked underneath an IF
statement to provide alternative mathematical or logical comparisons. Given a situation where the expression in the first IF
statement is not true, each consecutive ELSEIF
statement will be evaluated until one does evaluate to true or until an ELSE
statement is encountered. Also see the CASE
function.
IF [Profit Ratio] >= .25 then "Excellent"
ELSEIF [Profit Ratio] >= .1 then "Decent"
ELSEIF [Profit Ratio] >= 0 then "Needs Improvement"
ELSE "Urgent"
END
The END
statement is not a function that stands alone, but rather an essential piece of any IF
/THEN
evaluation or CASE
statement. It behaves much like a period does in a regular sentence, signaling that a phrase or expression is complete. For the END
statement, these phrases and expressions are logical and mathematical comparisons.
IF YEAR([Order Date]) = 2013 THEN [Sales] END
The ENDSWITH
function does the same task as the STARTSWITH
function; however, its focus is on the end of a string.
ENDSWITH(String, Substring)
ENDSWITH([City],"Orleans")
One of the three key words used in Level of Detail calculations. The EXCLUDE
keyword will ignore the dimensions listed from the dimensionality of the worksheet. This keyword is most useful when trying to visualize a measure at a coarser level of detail than the worksheet. You can set the level of detail to exclude one more more dimensions.
{ EXCLUDE [Dimension 1], [Dimension 2],... : AGG([Measure]))}
{ EXCLUDE [State]: SUM([Sales])}
The EXP
function is the inverse of the LN
function. EXP
returns “e” raised to the power of the given number, where “e” has the value 2.71828182845905. In Tableau, the return is accurate to 14 decimal places.
EXP(number)
EXP(2)
The FIND
function returns the index position of a substring
contained within a selected string
, or 0 if the substring
isn't found. If the optional argument start
is added, the function ignores any instances of substring that appear before the index position start
. The first character in the string is position 1.
FIND(String,Substring,[start])
FIND(([City],"City")
FIND(([Customer] ,"'",4)
IIF(CONTAINS([Customer],"'"),
(RIGHT([Customer],LEN([Customer]),FIND([Customer],"'",4)+2)),NULL)
The FINDNTH
function searches a given string and counts the number of times a specified character or set of characters occurs within the string. One of the inputs to this function is the “occurrence,” otherwise known as the “nth” time the specified character or characters present themselves in the string. The FINDNTH
function outputs the index value of the “nth” occurrence within the string.
FINDNTH("Jon Doe", "o", 2)
FINDNTH("Is it hot or is it cold?", "it", 2)
The FIRST()
table calculation function returns the number of rows back to the first row of the view/partition. This function does not require any arguments.
FIRST()
WINDOW_AVG(SUM([Sales]),FIRST(),LAST())
IF FIRST()=0 THEN WINDOW_AVG(SUM([Sales]),0,IIF(FIRST()=0,LAST(),0)) END
FIXED
is one of the three key words used in Level of Detail calculations. The FIXED
keyword will fix a calculation at a particular level of detail, regardless of the level of disaggregation in the worksheet the calculation is used in. You can set the level of dimensionality in the calculation to use zero, one, or multiple dimensions.
{ FIXED [Dimension 1], [Dimension 2],... : AGG([Measure]))}
{ FIXED : MIN([Order Date])}
{ FIXED [Customer Name] : MIN([Order Date])}
The FLOAT
function returns a floating point number, or in other words, a decimal number.
FLOAT(5)
INT([Teachers]) + FLOAT([Students])
FLOAT(MID(2,[DollarString]))
The FLOOR
function returns the nearest integer less than or equal to the specified number. This function is useful when you want to round a decimal down to the nearest integer.
FLOOR(123.55)
FLOOR(FLOAT(REGEXP_EXTRACT('abc 123.55','(d+.d+)')))
The following assumptions are used for the examples:
FULLNAME()
returns the full name of the user logged on to Tableau Server. For example, if Malcolm is the user currently logged into Tableau Server, FULLNAME()
will return Malcolm Reynolds. In design mode, the author has the ability to impersonate any registered user on the server. Expression = a valid discrete argument.
FULLNAME()='River Tam'
FULLNAME()=[Sales Person]
CASE FULLNAME()
WHEN [Sales Person] Then 'True'
WHEN [Junior Manager] Then 'True'
WHEN [Snr Manager] Then 'True'
ELSE 'False'
END
This is a Google Big Query–specific function that returns a JSON object within the JSON string based on the JSON path. Refer to the Apache Language Manual UDF for details at https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
.
GET_JSON_OBJECT(JSON string, JSON path)
This is a Google Big Query-specific function that concatenates values from each record into a single comma-delimited string. This function acts like a SUM()
function for strings.
GROUP_CONCAT(expression)
GROUP_CONCAT(Region)="Central,East,West"
The HEXBINX
function accepts two variables that will be treated as a horizontal axis and vertical axis, or as Cartesian coordinates. The values within the designated axis variables are then placed in hexagonal bins and can be plotted. The output of the HEXBINX
function will become the new horizontal axis.
HEXBINX([Lon],[Lat])
HEXBINX([Lon]*[Scalar Value],[Lat]*[Scalar Value])/[Scalar Value]
The HEXBINY
function accepts two variables that will be treated as a horizontal axis and vertical axis, or as Cartesian coordinates. The values within the designated axis variables are then placed in hexagonal bins and can be plotted. The output of HEXBINY
function will become the new vertical axis.
HEXBINY([Lon],[Lat])
HEXBINY([Lon]*[Scalar Value],[Lat]*[Scalar Value])/[Scalar Value]
This is a Google Big Query–specific function that returns the host name as a string for a given URL.
HOST(string_URL)
HOST('http://www.google.com:80:/index.html')='www.google.com:80'
The IF
statement is is a logical function that allows you to test IF, THEN, ELSE conditions and return a result that meets the specified conditions.
IF test THEN value END / IF test THEN value ELSE else END
IF [Order Quantity] > 10 THEN "Bulk Buy" ELSE "Non Bulk" END
IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END
IF [Ship Mode] = "Regular Air"
THEN "Customs Required"
ELSEIF [Ship Mode] = "Express Air"
THEN "Express Customs"
ELSE "No Customs" END
The IFNULL
statement is a simple reference function against a field. It contains two expressions. The first expression is a testing expression, and the second is the override expression. If the first expression is NULL, then it returns the override expression as the result. If the first expression is not NULL then it retains that value.
IFNULL(expresson1,expression2)
IFNULL([Customer],"Unidentified")
The IIF
function uses similar logic to the IF
statement; however, its arguments and return values are not as flexible. The IIF
statement contains a TEST
argument, followed by a THEN
statement, and an ELSE
statement. The test argument is first calculated; if the result is TRUE
, then it returns the THEN
statement as an answer. If the result of the argument is FALSE
, then it returns the ELSE
statement as an answer. An additional UNKNOWN
value can be added to the end of an IIF
statement should the TEST
argument not return either a TRUE
or FALSE
value.
IIF(test,then,else)
or
IIF(test, then, else,[unknown])
IIF(1<2,"True","False")
IIF([Time to Ship]>12,"Within SLA", "Outside SLA" )
IIF([Order Date]< Today()-14 and [Ship Mode] = "N"
,"High Priority",IIF([Order Date]<Today()-4 and
[Ship Mode] = "N","Medium Priority","Low Priority"))
One of the three key words used in Level of Detail calculations. The INCLUDE
keyword will add the dimensions listed from the dimensionality of the calculation to calculate a result at a more granular level of detail than present in the view.
{INCLUDE [Dimension 1], [Dimension 2],... : AGG([Measure]))}
AVG({ INCLUDE [Sub-Category]: AVG([Sales]))})
The INDEX
function returns the row number of the current row within the window (pane) or partition. This function does not require any arguments.
INDEX()
Index() <= 5
IF INDEX()=1 THEN WINDOW_AVG(SUM([Sales]),0,LAST(),0)) END
The function INT
converts a value to an integer. If the value is a floating point number, it will round down to the nearest integer (this can be used as a FLOOR
function).
INT(3.7)
INT([Date])
INT(MID(4,[QtyString]))
The function ISDATE
checks to see if a text is a valid date. The resulting output is a Boolean value. If the date string is valid, it will return TRUE
; otherwise, an invalid date string returns a FALSE
value.
ISDATE(Text)
Text
is the value you want to test.
ISDATE("This is not a date")
ISDATE("01 January 2013")=TRUE
ISDATE("1st January 2012")=FALSE
ISDATE("1/9/2012")=TRUE
ISDATE(STR([Year]) + '/' + STR([Month]) + '/' + STR([Day]))
The following assumptions are used for the examples:
ISFULLNAME()
returns a Boolean (true/false) value when the string or dimension specified in the brackets matches the user’s full name for the user logged on to Tableau Server. In design mode, the author has the ability to impersonate any registered user on the server.
ISFULLNAME('River Tam')
IF ISFULLNAME('Malcolm Reynolds') THEN 'Management'
ELSEIF ISFULLNAME('River Tam') THEN 'Sales'
ELSEIF ISFULLNAME('Jayne Cobb') THEN 'Public Relations'
ELSE 'Unknown'
END
This user function returns a Boolean (true/false) based on the logged-in user’s group membership defined on Tableau Server.
ISMEMBEROF('Sales')
IF ISMEMBEROF('Management') THEN 'Access Permitted'
ELSEIF ISFULLNAME('Sales') THEN 'Access Permitted'
ELSE 'Access Denied'
END
The ISNULL statement is a simple Boolean function. It returns TRUE
if expression is NULL
; returns FALSE
if it is not NULL
.
ISNULL(expression)
ISNULL([Customer])
The following assumptions are used for the examples:
Returns TRUE
if the current user's username matches the specified username or FALSE
if it does not match.
ISUSERNAME('j.cobb')
This table calculation function does not require any arguments.
LAST()
WINDOW_COUNT(SUM([Sales]),FIRST(),LAST())
IF INDEX()=1 THEN WINDOW_AVG(SUM([Sales]),0,LAST(),0)) END
LEFT
is a String function that returns the left-most characters from its designated string. This function can be used to create new dimensions directly or combined to create advanced calculated fields.
LEFT(String,Number)
LEFT([Customer Zip Code],3)
Returns the length of a string as an integer.
Note that LEN
counts spaces between string characters to contribute to the LEN
total value.
LEN(String)
LEN("Bob Hope")
The Advanced FIND
example that was provided previously uses the LEN
statement to help complete the calculation.
RIGHT([Customer],LEN([Customer])-FIND([Customer],"'",4)+2)
The LN
function returns the natural logarithm of a number. This is the logarithm to the base e, where e, has the value 2.71828182845905. In Tableau, the return is accurate to 14 decimal places. The natural logarithm of the expression is the power to which e would have to be raised to equal the expression.
LN(number)
LN(7.38905609893065)
The LOG
function returns the logarithm of a number for the given base. The logarithm of the expression is the power to which the base would have to be raised to equal the expression. If the base value is omitted, then base 10 is used.
LOG(number,[base])
LOG(1000)
LOG(8,2)
A Google Big Query–specific function that returns the logarithm base 2 of a number.
LOG2(number)
LOG2(16)
A table calculation function that returns the value of the expression in the target row, specified as the relative offset from the current row. Use FIRST() + n
and Last() -n
as part of the offset definition for the target relative to the first or last rows in the partition. If offset is omitted, the row to compare to can be set on the field menu. This function returns NULL
if the target row cannot be determined.
LOOKUP(expression,[offset])
LOOKUP(SUM([Sales]),2)
LOOKUP(SUM([Sales]), FIRST()+1)
LOOKUP( MIN([Region]),0)
This function allows the user to lowercase all characters within a string. The LOWER
function will only change the uppercased characters that exist in a string and thus ignore all lowercase characters that already exist.
LOWER(String)
LOWER("BatMan")
The LTRIM
function removes leading spaces that may be present within the data. This function can be used as a data cleansing function so that the data is consistent and set correctly.
LTRIM (string)
LTRIM(" Bob Hope" )
The output for this would simply be "Bob Hope"
. You need to remove leading spaces as these can cause a number of issues if you try to apply any additional functions to the data. An example of this would be:
LEFT(" Bob Hope", 4) which result is " ".
A Google Big Query–specific function that removes all of the leftmost characters from the first string that match the second string. It is case sensitive.
LTRIM_THIS(string1, string2)
LTRIM_THIS('Remove Me',' Me')
The MAKEDATE
function provides a simple way to create a date given three basic date parts: a year, a month number, and a day number. This is a useful function for avoiding complex variable type conversions if attempting to construct a date variable out of separate date parts.
MAKEDATE(year, month, day)
MAKEDATE(2015, 6, 18)
Returns a datetime that combines a date and a time. The date can be a date, datetime, or a string type. The time must be a datetime. This function is available only for MySQL connections.
MAKEDATETIME([Order Date], #02:32:59#)
The MAKETIME
function allows users to define their own time values using a calculated field. There are three inputs required: hours, minutes, and seconds. The output is formatted as hh:mm:ss.
MAKETIME(15,37,59)
The MAX
function is normally reserved for numbers, but this function can also be used on strings and dates. When MAX
is applied to strings, the MAX
value returns the string that is highest within the data’s sort sequence for that particular string.
MAX(a,b)
MAX("Maureen","William")
MAX([Sales])
MAX([Sales],[Profit])
The MAX
function can also be used as a String function or Date function, whereby expression1
and expression2
are string or date data types respectively.
MAX(ABS([Sales]-[Sales est]))
This function returns the median of a single expression. MEDIAN
can be used with numeric fields only. NULL values are ignored. For Tableau Desktop workbooks created by versions before 8.2, if the data source is Excel, Access, or a text file , this function will not be available unless the data source is extracted.
MEDIAN([Discount])
MEDIAN(DATEDIFF('day',[Order Date],[Ship Date])
The MID
function returns a partial string as its output. The MID
function allows extraction of specific segments from within a string. This function requires an index position, from which the extraction begins. The function then extracts all parts of the string from the index position onwards, or an optional argument can be used to only extract a certain number of characters from the start index position.
MID(string,start,[Length])
MID("Michael Gilpin",9)
MID("Michael Gilpin",9,4)
The MIN
function is similar to the MAX
function, whereby this function returns the minimum value when applied to a single field in an aggregate calculation. The MIN
function can also be applied to return the minimum of two arguments. These arguments must be of the same type. When used with two arguments, the function returns NULL if either argument is NULL.
MIN(expression1, expression2)
MIN([Sales])
MIN([Sales],[Profit])
MIN([Shipping Cost],[Maximum Shipping Cost])
DATEDIFF('day',MIN([Order Date]),MAX([Ship Date]))
This function returns an integer representing the month of any given date. This is a shortened form of DATEPART
('month', date).
MONTH(Date)
The Date
is the date the function will use to extract the month.
MONTH(#March 14, 2013#)
MONTH(DATEADD('day',[Date],5))
CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DAY([Date])
WHEN 'Month' THEN MONTH([Date])
WHEN 'Year' THEN Year([Date])
END
The NOT
statement, when placed before a mathematical or logical expression, negates the evaluation of the given expression. This function is useful when a desired outcome is most easily achieved by specifying values that should not be included in the resulting output.
IF NOT QUARTER([Order Date]) = 1 THEN [Profit] END
IF NOT ([Segment] = "Home Office"
AND ([Order Priority] = "Low" OR [Order Priority] = "Medium"))
THEN [Shipping cost] END
The NOW
function returns the current date and time.
NOW( )
DATEADD('hour', -5, NOW())
The OR
statement, when placed between two mathematical or logical expressions, evaluates the combined output of both expressions to a single Boolean output. If either of the expressions is TRUE
, the final OR
output is TRUE
. If both expressions are FALSE
, the OR
output is FALSE
. This function is useful when multiple conditions are eligible to trigger a desired calculation or outcome.
IF [Ship Mode] = "First Class" OR [Market] = "EMEA" THEN [Profit] END
IF ([Hours Worked]/[Hours Scheduled] < 0.8)
OR ([Hours Worked]/[Hours Scheduled] > 1.2)
THEN "Needs Attention" ELSE "Reasonable" END
A Hadoop Hive–specific function that returns a component of the given URL string where the component is defined by url_part
. Valid url_part
values include: HOST, PATH
, QUERY
, REF
, PROTOCOL
, AUTHORITY
, FILE
, and USERINFO
.
PARSE_URL(string,url_part)
PARSE_URL('http://www.tableau.com','HOST')
A Hadoop Hive–specific function that returns the value of the specified query parameter in the given URL string. The query parameter is defined by the key.
PARSE_URL_QUERY(string,url_part)
PARSE_URL_QUERY('http://www.tableau.com?page=1&cat=4','page')
The PERCENTILE
function requires an input variable and a user-defined value between 0 and 1, which represents the decimal form of a desired percentage. The function applies the specified percentage to the numerical range that exists within the input variable and outputs the corresponding result. A 0 percent specification is equivalent to the minimum value, a 100 percent specification is equivalent to the maximum value, and a 0.5 percent specification is equivalent to the median value.
PERCENTILE([Sales],0.5)
The function PI
returns the mathematical constant pi, also expressed with the symbol π. The value is approximately equal to 3.14159265358979. In Tableau, the return is accurate to 14 decimal places.
PI()
2*PI()*5
The POWER
function raises the number to the specified power.
POWER(number,power)
POWER(4,3)
[Profit]*POWER(1+0.12,6)
Returns the value of calculation in the previous reow. Returns the given expression if the current row is the first row in the partition.
PREVIOUS_VALUE(expression)
SUM([Sales])+ PREVIOUS_VALUE(1)
The RADIANS
function converts the given number from degrees to radians.
RADIANS(number)
RADIANS(360)
The RANK
table calculation function returns an ordered list of values currently in the window (pane) or partition. This version of the function uses a standard competition (“1,2,2,4”) rank and descending order by default. Items that equally compare are given the same rank number, and subsequent numbers are skipped.
RANK(expression, ['asc' | 'desc'])
RANK(SUM([Sales]))
The RANK_DENSE
table calculation function returns an ordered list of values currently in the window (pane) or partition. This version of the function uses a dense (“1,2,2,3”) rank and descending order by default. Items that equally compare are given the same rank number and subsequent items are assigned the following rank number.
RANK_DENSE(expression, ['asc' | 'desc'])
RANK_DENSE (SUM([Sales]),'asc'))
The RANK_MODIFIED
table calculation function returns an ordered list of values currently in the window (pane) or partition. This version of the function uses a modified competition (“1,3,3,4”) rank and descending order by default. A rank value is skipped, creating a gap before equally comparable items.
RANK_MODIFIED(expression, ['asc' | 'desc'])
RANK_MODIFIED(SUM([Sales]))
The RANK_PERCENTILE
table calculation function returns an ordered list of percentages currently in the window (pane) or partition. This version of the function returns a percentile (“25,75,75,100”) rank and ascending order by default. Items are assigned percentages according to their position in the frequency distribution. Note that using the percent number format provides accurate results.
RANK_PERCENTILE(expression, ['asc' | 'desc'])
RANK_PERCENTILE(SUM([Sales]))
The RANK_UNIQUE
table calculation function returns an ordered list of values currently in the window (pane) or partition. This version of the function uses an ordinal (“1,2,3,4”) rank and descending order by default. Items that equally compare receive separate numbers assigned by the sort order, typically alphabetical.
RANK_UNIQUE(expression, ['asc' | 'desc'])
RANK_UNIQUE(SUM([Sales]))
The RAWSQL_BOOL()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return a scalar value of a type that Tableau can convert into a Boolean. This expression will not be checked in any way by Tableau and may produce an error at the data source level. The user must respect the syntax conventions of the data source when constructing the expression. The following is the generalized syntax for the function:
RAWSQL_BOOL("expr",[arg1],...[argN])
The expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQL_BOOL("%1=%2",[Order Date],[Ship Date])
RAWSQL_BOOL("%1='Oklahoma' AND %2 > 100.00",[State],[Sales])
RAWSQL_BOOL("PATINDEX('%Henry%',%1)>0 AND %2>100.00",[Customer Name],[Sales])
The RAWSQL_DATE()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return a scalar value of a type that Tableau can convert into a date. Tableau will ignore any time component if a date-time is returned. This expression will not be checked in any way by Tableau and may produce an error at the data source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQL_DATE("expr",[arg1], ...[argN])
The expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQL_DATE("%1 + 10", [Order Date])
RAWSQL_DATE("COALESCE(%2, %1)", [Order Date], [Ship Date])
RAWSQL_DATE("CASE WHEN %1 = 'Critical' THEN %2+2
WHEN %1 = 'High' THEN %2+3
WHEN %1 = 'Medium' THEN %2+4
ELSE %2+10 END", [Order Priority], [Order Date])
The RAWSQL_DATETIME()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return a scalar value of a type that Tableau can convert into a date time. This expression will not be checked in any way by Tableau and may produce an error at the data source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQL_DATETIME("expr", [arg1], ...[argN])
The expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQL_DATETIME("%1 + '06:30:00'", [Order Date])
RAWSQL_DATETIME("DATETIMEFROMPARTS(2013,2,24,9,40,35,0)")
RAWSQL_DATETIME("CASE WHEN %2 = 'East'THEN %1 + '01:00:00'
WHEN %2 = 'West' THEN %1—'02:00:00'
ELSE %1 END", [Order Date], [Region])
The RAWSQL_INT()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return a scalar value of a type that Tableau can convert into an integer. This expression will not be checked in any way by Tableau and may produce an error at the data-source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQL_INT("expr",[arg1],...[argN])
The expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQL_INT("1+2")
RAWSQL_INT("CEILING(%1)",[Unit Price])
RAWSQL_INT("DATEDIFF(day,COALESCE(%2,%1),GETDATE())",[Order Date],[Ship Date])
The RAWSQL_REAL()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return a scalar value of a type that Tableau can convert into a number. This expression will not be checked in any way by Tableau and may produce an error at the data-source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQL_REAL("expr",[arg1],...[argN])
The expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQL_REAL("5.39 + 3.56")
RAWSQL_REAL("RAND()")
RAWSQL_REAL("ROUND(CASE WHEN %1 = 'East' THEN %2 * 1.15
WHEN %1 = 'West' THEN %2 * 0.85
ELSE %2 END, 2)",[Region],[Sales])
The RAWSQL_STR()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return a scalar value of a type that Tableau can convert into a string. This expression will not be checked in any way by Tableau and may produce an error at the data-source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQL_STR("expr",[arg1],...[argN])
The expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQL_STR("'Trivial Case'")
RAWSQL_STR("%1 + '-' + CONVERT(varchar, %2)",[State],[Zip Code])
RAWSQL_STR("STUFF(%1,CHARINDEX(' ', %1), 0,' ''' + %2 + ''' ')",
[Customer Name],[State])
The RAWSQLAGG_BOOL()
function is a pass-through function that provides a means to send an arbitrary expression to the underlying data source. The expression must return an aggregate value that Tableau can convert into a Boolean. This expression will not be checked in any way by Tableau and may produce an error at the data source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQLAGG_BOOL("agg_expr", [arg1], ...[argN])
The agg_expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQLAGG_BOOL("SUM(%1) = SUM(%2)", [Sales],[Profit])
RAWSQLAGG_BOOL("SUM(CASE WHEN %1='Oklahoma' THEN %2 ELSE 0 END)
> 100.00", [State], [Sales])
RAWSQLAGG_BOOL("SUM(CASE WHEN PATINDEX('%Henry%', %1) > 0 THEN %2 ELSE 0 END)
> 100.00",[Customer Name],[Sales])
The RAWSQLAGG_DATE()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return an aggregate value that Tableau can convert into a date. This expression will not be checked in any way by Tableau and may produce an error at the data-source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function follows:
RAWSQLAGG_DATE("agg_expr",[arg1],...[argN])
The agg_expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, %N
syntax.
RAWSQLAGG_DATE("MIN(%1)",[Order Date])
RAWSQLAGG_DATE("MAX(COALESCE(%2, %1))",[Order Date],[Ship Date])
RAWSQLAGG_DATE("MAX(CASE WHEN %1 = 'Critical' THEN COALESCE(%3, %2) END)",
[Order Priority],[Order Date],[Ship Date])
The RAWSQLAGG_DATETIME()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return an aggregate value that Tableau can convert into a date time. This expression will not be checked in any way by Tableau and may produce an error at the data source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQLAGG_DATETIME("agg_expr",[arg1],...[argN])
The agg_expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQLAGG_DATETIME("MAX(%1)",[Ship Date])
RAWSQLAGG_DATETIME("MAX(%2—%1)",[Order Date],[Ship Date])
RAWSQLAGG_DATETIME("MAX(CASE WHEN %2 = 'East' THEN %1 + '01:00:00'
WHEN %2 = 'West' THEN %1—'02:00:00'
ELSE %1 END)",[Order Date],[Region])
The RAWSQLAGG_INT()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return an aggregate value that Tableau can convert into an integer. This expression will not be checked in any way by Tableau and may produce an error at the data source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQLAGG_INT("agg_expr",[arg1],...[argN])
The agg_expr
in quotes is the expression to be passed through to the data source. N number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, %N
syntax.
RAWSQLAGG_INT("FLOOR(SUM(%1))",[Sales])
RAWSQLAGG_INT("CEILING(STDEV(%1))",[Unit Price])
RAWSQLAGG_INT("AVG(DATEDIFF(day, COALESCE(%2, %1), GETDATE()))",
[Order Date],[Ship Date])
The RAWSQLAGG_REAL()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return an aggregate value that Tableau can convert into a number. This expression will not be checked in any way by Tableau and may produce an error at the data source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQLAGG_REAL("agg_expr",[arg1],...[argN])
The agg_expr
in quotes is the expression to be passed through to the data source. N number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQLAGG_REAL("SUM(%1)",[Profit])
RAWSQLAGG_REAL("VAR(%1-%2)",[Product Base Margin],[Discount])
RAWSQLAGG_REAL("ROUND(SUM(CASE WHEN %3='East' THEN %1*%2*0.85
WHEN %3='West' THEN %1*%2*1.15
ELSE %1*%2 END),4)",[Unit Price],[Order Quantity],[Region])
The RAWSQLAGG_STR()
function is a pass-through function that allows the user to send an arbitrary expression to the underlying data source. The expression must return an aggregate value that Tableau can convert into a string. This expression will not be checked in any way by Tableau and may produce an error at the data-source level. The user must respect the syntax conventions of the data source when constructing the expression. The generalized syntax for the function is as follows:
RAWSQLAGG_STR("agg_expr",[arg1],...[argN])
The agg_expr
in quotes is the expression to be passed through to the data source. N
number of arguments can be specified in a comma-separated list as shown. These arguments are referenced in the expression with a %1, %2, and % N
syntax.
RAWSQLAGG_STR("MIN(%1)",[Order Date])
RAWSQLAGG_STR("MAX(LEFT(%1, 3))",[City])
RAWSQLAGG_STR("CASE WHEN (SUM(%1)/SUM(%2)) > 0
THEN 'Compliant' ELSE 'Noncompliant' END",[Profit],[Sales])
REGEXP_EXTRACT
is a function that only works with text files, Hadoop Hive, Google BigQuery, PostgreSQL, Tableau Data Extracts, Microsoft Excel, Salesforce, and Oracle datas sources.
The function returns a string based on the given pattern. If the pattern returns more than one result, the function fails. For Tableau Data Extracts, the pattern must be a constant.
For information on regular expression syntax, see your data source’s documentation. For Tableau extract files, syntax should conform to the International Components for Unicode (ICU) standard, an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. Search for the Regular Experessions page in the online ICU User Guide.
REGEXP_EXTRACT(string,pattern)
REGEXP_EXTRACT('abc 123','[a-z]+s+(d+)')
REGEXP_EXTRACT('ABC20DEF','20(.*)')
REGEXP_EXTRACT_NTH
is a function that works only with text files, Hadoop Hive, Google BigQuery, PostgreSQL, Tableau Data Extracts, Microsoft Excel, Salesforce and Oracle datas sources.
The function returns the portion of the string that matches the regular expression pattern. The substring is matched to the nth capturing group, where n is the given index. If the index is 0, the entire string is returned. For Tableau Data Extracts, the pattern must be a constant.
For information on regular expression syntax, see your data source’s documentation. For Tableau extract files, syntax should conform to the International Components for Unicode (ICU) standard, an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. Search for the Regular Experessions page in the online ICU User Guide.
REGEXP_EXTRACT_NTH(string,pattern,index)
REGEXP_EXTRACT_NTH('abc 123','[a-z]+s+(d+)',1)
REGEXP_MATCH
is a function that works only with text files, Hadoop Hive, Google BigQuery, PostgreSQL, Tableau Data Extracts, Microsoft Excel, Salesforce, and Oracle datas sources.
The function returns a Boolean value (true/false) if a substring of the specified string matches the regular expression pattern. For Tableau Data Extracts, the pattern must be a constant.
For information on regular expression syntax, see your data source’s documentation. For Tableau extract files, syntax should conform to the International Components for Unicode (ICU) standard, an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. Search for the Regular Experessions page in the online ICU User Guide.
REGEXP_MATCH(string,pattern)
REGEXP_MATCH('ABC20DEF','[0-9]')
REGEXP_MATCH('ABCDEF','[0-9]')
REGEXP_REPLACE
is a function that works only with text files, Hadoop Hive, Google BigQuery, PostgreSQL, Tableau Data Extracts, Microsoft Excel, Salesforce, and Oracle datas sources.
The function replaces the characters within a string by using a matching pattern. For Tableau Data Extracts, the pattern must be a constant.
For information on regular expression syntax, see your data source’s documentation. For Tableau extract files, syntax should conform to the International Components for Unicode (ICU) standard, an open source project of mature C/C++ and Java libraries for Unicode support, software internationalization, and software globalization. Search for the Regular Experessions page in the online ICU User Guide.
REGEXP_REPLACE(string,pattern,replacement)
REGEXP_REPLACE('ABC20DEF','[0-9]', '*')
REGEXP_REPLACE('abc 123','s','-')
The REPLACE
function is an advanced function that allows specified data replacement within a string field. This does not change the data at the source level by using this function, but instead merely creates a new field that includes the replacement strings. The function searches a string field to find the stated substring. Once the substring is found, the replacement string replaces the substring data.
REPLACE(String,Substring,Replacement)
REPLACE("[Order Priority]","Not Specified","High")
IIF([Order Date] < dateadd('month',-2,today()) ,
REPLACE([Order Priority],"Not Specified","High"),[Order Priority])
RIGHT
is a String function that returns the rightmost characters from its designated string. This function can be used to create new dimensions directly or combined to create advanced calculated fields. This has the same principles as the LEFT
function.
RIGHT(String,Number)
RIGHT([Customer Zip Code],2)
The ROUND
function rounds numbers to the number of digits as specified with the decimals argument within the function. The decimals
argument specifies how many decimal points of precision to include in the final result, although it is not required. If the decimals variable is not included, then the number is rounded to the nearest integer. Tableau uses the following rounding rules:
ROUND(number, [decimals])
ROUND([Sales])
ROUND(SUM([Profit])/SUM([Order Quantity]),2)
The RTRIM
function removes trailing spaces that may be present within the data. This function, like the LTRIM
function, can be used as a data cleansing function so that the data is consistent and set correctly.
RTRIM(String)
RTRIM("Ruby Young ")
This is a Google Big Query–specific function that removes all of the rightmost characters from the first string that match the second string. It is case sensitive.
RTRIM_THIS(string1, string2)
RTRIM_THIS('Remove me', ' me'
This is a table calculation function that returns the running average of the provided expression from the first to the current row of the view/partition.
RUNNING_AVG(expression,[start],[end]
)
RUNNING_AVG(SUM([Sales]))
RUNNING_AVG(SUM([Sales]), FIRST(),LAST())
IF INDEX()=1 THEN RUNNING_AVG( SUM([Sales]) ) ELSE NULL END
This table calculation function returns the running count of the provided expression from the first to the current row of the view/partition.
RUNNING_COUNT(expression,[start],[end]
)
RUNNING_COUNT(SUM([Sales]))
RUNNING_COUNT(SUM([Sales]), FIRST(),LAST())
IF INDEX()=1 THEN RUNNING_COUNT(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END
This table calculation function returns the running maximum of the provided expression from the first to the current row of the view (partition).
RUNNING_MAX(expression,[start],[end]
)
RUNNING_MAX( SUM([Sales]) )
RUNNING_MAX( SUM([Sales]) ), FIRST(),LAST())
IF INDEX()=1 THEN RUNNING_MAX( SUM([Sales]) ) ELSE NULL END
This table calculation function returns the running minimum of the provided expression from the first to the current row of the view or partition.
RUNNING_MIN(expression,[start],[end]
)
RUNNING_MIN(SUM([Sales]))
RUNNING_MIN(SUM([Sales]) ), FIRST(),LAST())
IF INDEX()=1 THEN RUNNING_MIN (SUM([Sales]))ELSE NULL END
This table calculation returns the running sum of the provided expression from the first to the current row of the view/partition.
RUNNING_SUM(expression,[start],[end]
)
RUNNING_SUM(SUM([Sales]))
RUNNING_SUM( SUM([Sales]) ), FIRST(),LAST())
IF INDEX()=1 THEN RUNNING_SUM ( SUM([Sales]) ) ELSE NULL END
Functions 118, 119, 120, and 121 are the core of R integration in Tableau. Each invokes the R console and can send data from Tableau to R for manipulations and calculations. These functions are all table calculations, so it is important to understand how table calculations work before using them.
Each function is named after the type of data Tableau expects to receive from the R console. For example, the SCRIPT_REAL
function expects that R will return a real number. If it does not, the function will fail and Tableau will show an error. Thus it is important to think about the type of data you want R to return.
Before getting into the syntax, it is critical to know how Tableau passes data to and receives data from R. Because the functions are table calculations, Tableau sends vectors of data along the specified level of partition in the table calculation. As with other table calculations, the dimensions in the view determine the level of aggregation of each row, unless the Aggregate Measures option is turned off.
Once Tableau sends the data to R and the R script has been run, Tableau receives data back in vectors of the same length that it originally sent. This means that if the function sent ten rows of data to R, and the R script simply returns the number 2, Tableau will receive the number 2 back ten times.
The script functions have two parts: the R script, and arguments specifying the data that Tableau is to send to R. Consider this example:
SCRIPT_REAL(".arg1 + .arg2",SUM([Sales]),SUM([PROFIT]))
In the R code, the value ".arg#"
is used to represent corresponding data values in Tableau that are being passed to the R console. In this example, .arg1
represents SUM([Sales]
in the R code, while .arg2
represents SUM([PROFIT])
. For a data value to be passed to R through this function, it must be aggregated even if the level of detail at which you are passing data to R is not. The .arg#
values can also represent parameters thereby creating dynamic R scripts.
SCRIPT_BOOL ("insert R code here", .arg1, .arg2, ... .argN)
SCRIPT_INT("insert R code here", .arg1, .arg2, ... .argN)
SCRIPT_REAL ("insert R code here", .arg1, .arg2, ... .argN)
SCRIPT_STRING ("insert R code here", .arg1, .arg2, ... .argN)
SCRIPT_REAL("df <- data.frame(tire_size = .arg1, mpg = .arg2);
fit <- lm(mpg ~ tire_size, data = df);
scores <- predict(fit, df);
scores", SUM ([Tire Size]), SUM ([MPG])
)
This advanced function uses the fields Tire Size
and MPG
, then computes a simple linear regression model with Tire Size
as the independent variable and MPG
as the dependent variable. This would allow a user to plot a line of best fit. Note that using data frames and renaming your data can help make it easier to use R integration. This way, as with the preceding example, you do not have to continually use .arg1
, and can instead just use tire_size
.
The SIGN
function is used to highlight whether the value of the result is positive, negative, or equal to zero. The returned values are -1
if the number is negative, 0
if the number is zero, or 1
if the number is positive.
SIGN(number)
SIGN(-21)
IF SIGN(SUM([Profit]))=1
THEN "Profit"
ELSEIF SIGN(SUM([Profit]))=-1
THEN "Loss"
ELSE "Break-Even"
END
The SIN
function returns the sine of a given number specified in radians.
SIN(number)
SIN(PI()/4)
SIN(RADIANS(90))
This table calculation function returns the total number of rows in the view/partition.
SIZE()
SIZE()
WINDOW_SUM(SUM([Sales]))/SIZE()
IF INDEX()=1 THEN WINDOW_SUM(SUM([Sales]))/SIZE() ELSE NULL END
The SPACE
function is a simple function allowing the user to create a string of spaces that can then be used within other calculations.
SPACE(number)
SPACE(4)
[Customer]+SPACE(2)+[City]+SPACE(2)+[Zip Code]
The SPLIT
function cuts a string into multiple sections based on a user-specified delimiter or a character used to indicate the point of separation. A token number, which is assigned positive values if counting from the beginning of the string or negative values if counting backwards from the end of the string, must also be provided to indicate which piece of the string to include as the final result.
SPLIT(string, delimiter, token number)
SPLIT("Hi, how are you?", " ", -2)
INT(TRIM(SPLIT([Product ID], "-", -1)))
The SQRT
function is the inverse of the SQUARE
function. It returns the square root of a number. It gives the same return when using the POWER
function when raising the number to the power of 0.5.
SQRT(number)
SQRT(49)
The SQUARE
function returns the square of the number. In other words, it multiplies the expression by itself. It gives the same return when using POWER
function when raising the number to the power of two.
SQUARE(number)
SQUARE(7)
The STARTSWITH
function is similar in its approach to the CONTAINS
function, but it has limits on the way it searches the string. Whereas the CONTAINS
function searches the full length of the string for the specified substring, the STARTSWITH
function only searches the very beginning of the string.
STARTSWITH(String, Substring)
STARTSWITH([City],"New")
This function returns an estimate of the population standard deviation based on a sample of data from the populaton. It uses N-1
in the denominator to adjust for bias retlated to small sample size.
STDEV([Sales])
AVG([Sales])+STDEV([Sales])
AVG([Sales])+(([Number of deviations])*STDEV([Sales]))
This function returns the statistical standard deviation of the expression without adjusting for small sample bias. Use STDEVP
if the expression includes the entire population, even if there are a small number of values.
STDEVP([Sales])
AVG([Sales]) + STDEVP([Sales])
AVG([Sales])+ (([Number of deviations])*STDEV([Sales]))
Returns a string for a given expression.
STR(5.0)
"Total Products = " + STR([Qty])
STR([StartDate]) + ' to ' + STR([EndDate])
This SUM
function returns the sum of all the values in the expression. SUM
can be used with numeric fields only. NULL values are ignored.
SUM([Sales])
SUM([Sales])*[Commission Rate]
SUM([Sales]) / COUNTD([Customer ID])
The TAN
function returns the tangent of a given number specified in radians.
TAN(number)
TAN(PI()/4)
TAN(RADIANS(45))
The THEN
qualifier is used in logical expressions to transition from evaluating whether or not an expression is true to triggering a resulting action. It is an essential element of IF
/ELSEIF
/ELSE
/THEN
statements and CASE
statements.
IF [Profit] >= 0 THEN "Profitable" ELSE "Unprofitable" END
A Google Big Query–specific function that converts a timestamp data type to a Unix timestamp in microseconds. Often when working with dates you will need to convert to a datetime first.
TIMESTAMP_TO_USEC(expression)
TIMESTAMP_TO_USEC(#2012-10-01 01:02:03#)
A Google Big Query–specific function that returns a top-level domain of a URL string. The URL must include protocol to work.
TLD(string_url)
TLD("https://www.twitter.com/DGM885")
The TODAY
function returns the current date. This is similar to NOW()
but does not include the time component.
TODAY()
DATEADD('day', -30, TODAY())
TOTAL(Expression)
TOTAL(SUM([Sales]))
SUM([Sales])/TOTAL(SUM([Sales]))
WINDOW_MAX(SUM([Sales])/TOTAL(SUM([Sales])))=(SUM([Sales])/TOTAL(SUM([Sales])))
The TRIM
function encompasses the logic of both LTRIM
and RTRIM
into one function.
TRIM(String)
TRIM(" Gemma Palmer ")
This function allows the user to change all characters to uppercase within a string. The UPPER
function will only change the lowercased characters that exist in a string and thus ignore all uppercase characters that already exist.
UPPER(String)
UPPER("BatMan")
A Google Big Query–specific function that converts a Unix timestamp in microseconds to a TIMESTAMP
data type.
USEC_TO_TIMESTAMP(expression)
TIMESTAMP_TO_USEC(1349053323000000)
This function returns the domain of the person currently logged into Tableau Server. If the user is not logged on to Server, the function returns the Windows domain. This function can be used in conjunction with other user functions when you desire to create security based on username and domain.
Refer to the assumptions in the USERNAME() section that follows for the user and domain data.
USERDOMAIN()
CASE USERDOMAIN()
WHEN 'RETAIL' THEN 'Access Granted'
WHEN 'WSALE THEN 'Access Denied'
END
IF USERDOMAIN() = 'WSALE' THEN
IF ISMEMBEROF('Report Viewer')Then
'Access Granted'
ELSE
'Access Denied'
END
ELSEIF USERDOMAIN() = 'RETAIL' THEN
IF ISMEMBEROF('Management') THEN
'Access Granted'
ELSE
IF FULLNAME() = [Sales Person] THEN
'Access Granted'
ELSE
'Access Denied'
END
END
ELSE
'Access Denied'
END
The following assumptions used for the examples:
USERNAME()
returns the username of the user logged onto the server. If the user Malcolm was logged onto the server, then USERNAME()
would return m.reynolds
.
Expression
= Any valid discrete argument
USERNAME()='m.reynolds'
USERNAME()=[MANAGER]
IF ISMEMBEROF('Management')then 'Access Permitted'
ELSEIF USERNAME()=[Manager]then 'Access Permitted'
ELSE 'Access Denied' END
This function returns the statistical variance of the expression without adjusting for small sample bias. Use STDEV if the expression includes the entire population, even if there are a small number of values.
VAR(expression)
This aggregate function returns the statistical variance of the values in the given expression based on a biased sample of the population. Variance is a measure of dispersion and is calculated using the average of the squared deviations from the mean. Thinking about statistical variance, this function seems like a weigh-station on the journey to arriving at standard deviation—a more commonly used dispersion measure—that is the square root of variance. In normally distributed sets of data, standard deviation implies specific value ranges that are useful for plotting control charts. Variance by itself seems to have less practical use cases. If you have one, please share it.
VAR(expression)
The WHEN
qualifier is used in conjunction with a CASE
statement and identifies specific scenarios, also known as cases, that the CASE
structure will encounter and interact with. The WHEN
statement points to each individual scenario and specifies the appropriate action to take for each potential case.
CASE MONTH([Order Date])
WHEN 1 THEN "Manager A"
WHEN 2 THEN "Manager B"
WHEN 3 THEN "Manager C"
WHEN 4 THEN "Manager A"
WHEN 5 THEN "Manager B"
WHEN 6 THEN "Manager C"
WHEN 7 THEN "Manager A"
WHEN 8 THEN "Manager B"
WHEN 9 THEN "Manager C"
WHEN 10 THEN "Manager A"
WHEN 11 THEN "Manager B"
WHEN 12 THEN "Manager C"
END
CASE [Performance Metric]
WHEN "Sum of Sales" THEN SUM([Sales])
WHEN "Sum of Profit" THEN SUM([Profit])
WHEN "Quantity Sold" THEN SUM([Quantity])
WHEN "Average Shipping Cost" THEN AVG([Shipping Cost])
WHEN "Average Discount" THEN AVG([Discount])
END
This function returns the average for a given expression over a window (or pane) specified. Note that performance is affected with an increase in marks; if the dataset is large, the advanced method will offer better performance and scalability.
WINDOW_AVG(expression,[start],[end])
WINDOW_AVG(SUM([Sales]))
WINDOW_AVG(SUM([Sales]),FIRST,()LAST())
IF INDEX()=1 THEN WINDOW_AVG(SUM([Sales]),0,IIF(INDEX()=1,LAST(),0))END
This table calculation function returns the count for a given expression with a window (or pane) the user specifies. Note that performance is affected with an increase in marks; if the dataset is large, the advanced method will offer better performance and scalability.
WINDOW_COUNT(expression,[start],[end])
WINDOW_COUNT(SUM([Sales]))
WINDOW_COUNT(SUM([Sales]),FIRST(),LAST())
IF INDEX()=1 THEN WINDOW_COUNT(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END
This table calculation function returns the maximum value for a given expression within the window (or pane) specified. Note: Performance is affected with an increase in marks; if the dataset is large, the advanced method will offer better performance and scalability.
WINDOW_MAX(expression,[start],[end])
WINDOW_MAX(SUM([Sales]))
WINDOW_MAX(SUM([Sales]),FIRST(),LAST())
IF MAX([Ship Date]) = WINDOW_MAX( MAX([Ship Date]))
THEN SUM([Sales]) ELSE NULL END
This function returns the median for a given expression within a window (or pane) specified by the user. Performance is affected with an increase in marks; if the dataset is large, the advanced method will offer better performance and scalability.
WINDOW_MEDIAN(expression,[start],[end])
WINDOW_MEDIAN(SUM([Sales]))
WINDOW_MEDIAN(SUM([Sales]),FIRST(), LAST())
IF INDEX()=1 THEN WINDOW_MEDIAN(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END
This table calculation function returns the minimum value for a given expression within a window (or pane) the user specifies. Performance is affected with an increase in marks; if the dataset is large, the advanced method will offer better performance and scalability.
WINDOW_MIN(expression,[start],[end])
WINDOW_MIN(SUM([Sales]))
WINDOW_MIN(SUM([Sales]), FIRST(),LAST())
IF INDEX()=1 THEN WINDOW_MIN( SUM([Sales])) ELSE NULL END
This table calculation function returns values corresponding to the specified percentile within the window. The window is defined by means of offsets from the current row. Use FIRST()+n
and LAST()-n
for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
WINDOW_PERCENTILE(expression,number,[start],[end])
WINDOW_PERCENTILE(SUM([Profit]),0.95,-2,0))
This table calculation function will return the unbiased estimate of the population standard deviation based on a random sample of data in the expression. Performance is affected with an increase in marks; if the dataset is large, the advanced method will have better performance and scalability.
WINDOW_STDEV(expression,[start],[end])
WINDOW_STDEV(SUM([Sales]))
WINDOW_STDEV(SUM([Sales]),FIRST(),LAST())
IF INDEX()=1 THEN WINDOW_ STDEV(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END
This function will return the standard deviation of a given expression over a window (or pane) for which the user specifies. Performance is affected with an increase in marks; if the dataset is large, the advanced method will offer better performance and scalability.
WINDOW_STDEVP(expression,[start],[end])
WINDOW_STDEVP(SUM([Sales]))
WINDOW_STDEVP(SUM([Sales]), FIRST(),LAST())
This table calculation function will return the sum for a given expression over a window (or pane) for which the user specifies. Note that performance is affected with an increase in marks; if the dataset is large, the advanced method will offer better performance and scalability.
WINDOW_SUM(expression,[start],[end])
WINDOW_SUM(SUM([Sales]))
WINDOW_SUM(SUM([Sales]) ),FIRST(),LAST)()
IF INDEX()=1 THEN WINDOW_SUM( SUM([Sales]) ) ELSE NULL END
IF INDEX()=1 THEN WINDOW_VAR(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END
This table calculation function will return the unbiased estimate of the population variance of a given expression over a window (or pane) for which the user specifies. Performance is affected with an increase in marks; if the dataset is large, the advanced method will offer better performance and scalability.
WINDOW_VAR(expression,[start],[end])
WINDOW_VAR(SUM([Sales]))
WINDOW_VAR(SUM([Sales]), FIRST(),LAST())
This table calculation function returns the variance of a given population in the expression. Performance is affected with an increase in marks; if the dataset is large, using the advanced method will have better performance and scalability.
WINDOW_VARP(expression,[start],[end])
WINDOW_VARP(SUM([Sales]))
WINDOW_VARP(SUM([Sales]),FIRST(),LAST())
IF INDEX()=1 THEN WINDOW_VARP(SUM([Sales]),0,
IIF(INDEX()=1,LAST(),0)) END
Functions 159 through 165 are for the core of Tableau’s Hadoop Hive functions. See your Hadoop vendor’s user manual and Tableau’s website for code samples.
XPATH_BOOLEAN (XML string, XPATH expression string)
XPATH_DOUBLE (XML string, XPATH expression string)
XPATH_FLOAT (XML string, XPATH expression string)
XPATH_INT (XML string, XPATH expression string)
XPATH_LONG (XML string, XPATH expression string)
XPATH_SHORT (XML string, XPATH expression string)
XPATH_STRING (XML string, XPATH expression string)
This date function returns an integer representing the year of any given date. This is a shortened form of DATEPART
('year', [Date]).
YEAR(Date)
Date
is the time period from which the year is extracted.
YEAR(#March 14, 2013#)
YEAR(DATEADD('day', [Date], 5 ))
CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DAY([Date])
WHEN 'Month' THEN MONTH([Date])
WHEN 'Year' THEN Year([Date])
END
ZN(expression)
ZN([Profit])
ZN(SUM([Profit]))-LOOKUP(ZN(SUM([Profit])),-1)