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.

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:

• An alphabetically sorted listing of every function by category
• An alphabetically sorted and numbered list with brief function descriptions
• Detailed discussion of each function with syntax examples

Organization and Key for Appendix E

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.

What Is RAW SQL and Why Do You Need It?

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.

R Integration via Script Functions

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.

Other Specialized Functions

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.

Alphabetical Function List—Summary

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.

1. ABS

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)

Basic

ABS([Budget Variance])

Intermediate

ABS(SUM([Budget Sales])-SUM([Sales]))/SUM([Budget Sales])

2. ACOS

The ACOS function returns the arc cosine of the given number. This is the inverse of the COS function.

ACOS(number)

Basic

ACOS(0.5)

Intermediate

DEGREES(ACOS(0.5))

Advanced

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)))

3. AND

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.

Basic

IF SUM([Sales]) > 10,000 AND AVG([Discount]) > .1
THEN "Review" ELSE "OK" 
END

Intermediate

IF (DATEPART('month', [Order Date]) = 6 
AND DATEPART('year',[Order Date]) = 2014)
AND [Profit] < 0 THEN "Review" ELSE "OK"
END

4. ASCII

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:

• 0–31: Non-printing characters/control characters
• 32–63: Numeric values, punctuation characters, and special characters
• 64–95: Uppercase alphabet characters and special symbols
• 96–127: Lowercase alphabet characters and special symbols

  ASCII(String)

Basic

ASCII([Customer])

Intermediate

IIF (ASCII([Customer Name])<32, 'Non Printable Characters','Printable Characters')

Advanced

ASCII(MID([Customer Name],FIND([Customer Name]," ")))

5. ASIN

The ASIN function returns the arcsine, in radians, of the given number. This is the inverse of the SIN function.

ASIN(number)

Basic

ASIN(1)

Intermediate

DEGREES(ASIN(1))

6. ATAN

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)

Basic

ATAN(1)

Intermediate

DEGREES(ATAN(1))

7. ATAN2

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)

Basic

ATAN2(1,1)

Intermediate

DEGREES(ATAN2(-1,-1))

8. ATTR

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 Mako¹ 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.

Basic

ATTR([field])

Basic

ATTR([Sub-Category])

9. AVG

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.

Basic

AVG([Discount])

Intermediate

AVG(DATEDIFF('day',[Order Date],[Ship Date]))

Advanced

AVG(IF(DATEDIFF('day',[Order Date],[Ship Date])<=[Time to Ship Goal])
THEN 1 ELSE 0 END)

10. CASE

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

Basic

CASE [Month]
WHEN 1 THEN "January"
WHEN 2 THEN "February"
WHEN 3 THEN "March"
WHEN 4 THEN "April"
ELSE "Not required"
END

Intermediate

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

11. CEILING

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.

Basic

CEILING(41.09)

Intermediate

CEILING(SUM([Sales]))

12. CHAR

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)

Basic

CHAR(65)

Intermediate

CHAR(IIF (ASCII([Customer])> 96 
and ASCII([Customer])<= 122, 
ASCII([Customer])-32, 
ASCII([Customer])))

Advanced

CHAR(ASCII([Customer]))+ "." +
CHAR(ASCII(LTRIM(MID([Customer],FIND([Customer]," ")))))+ "."

13. CONTAINS

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)

Basic

CONTAINS([City],"New")

14. COS

The COS function returns the cosine of a given number specified in radians.

COS(number)

Basic

COS(PI()/8)

Intermediate

COS(RADIANS(60))

Advanced

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)))

15. COT

Returns the cotangent of a given number specified in radians. The number is expressed in radians.

COT(number)

Basic

COT(PI( )/4)

Intermediate

COT(RADIANS(45))

16. COUNT

This function returns the count of the items in a group. NULL values are not counted.

Basic

COUNT([Ship Date])

Intermediate

COUNT(IIF([Discount]=0,1,NULL))

Advanced Example

COUNT(IIF([Discount]=0,1,NULL))/COUNT([Number of Records])

17. COUNTD

Count distinct returns the number of distinct items in a group. NULL values are not counted. Each unique value is counted only once.

Basic

COUNTD([Customer Name])

Intermediate

COUNTD([City]+[State])

Advanced

COUNTD(IF([Country]=[Country Parameter]) 
THEN [Customer Name] ELSE NULL END)

18. DATE

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.

Basic

DATE("March 15, 2013")

Intermediate

DATE([DateString])

Advanced

DATE(STR([Year]) + '/'+ STR([Month]) + '/' + STR([Day]))

19. DATEADD

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.

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.

Basic

DATEADD('day',3,[Order Date])

Intermediate

DATEADD('day', -30, TODAY())

Advanced

DATEADD('month', -12, WINDOW_MAX(MAX([Date])))

20. DATEDIFF

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.

Basic

DATEDIFF('day', #June 3, 2012#, #June 5, 2012#)

Intermediate

DATEDIFF('day',[Ship Date], TODAY())

Advanced

CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DATEDIFF('day',[OrderDate],[ShipDate])
WHEN 'Week' THEN DATEDIFF('week',[OrderDate],[ShipDate])
END

21. DATENAME

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)

Basic

DATENAME('month', #2012-06-03#)

Intermediate

DATENAME('month',[StartDate]) + ' to ' + DATENAME('month',[EndDate])

Advanced

DATENAME('month', TOTAL(MIN([Date]))) + ' ' +
DATENAME('year', TOTAL(MIN([Date]))) + ' to ' +
DATENAME('month', TOTAL(MAX([Date]))) + ' ' +
DATENAME('year', TOTAL(MAX([Date])))

22. DATEPARSE

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.

Basic

DATEPARSE("MMyyyy", "082015")

Intermediate

DATEPARSE("MMddyyyy hh:mma", "08072015 10:07AM")

Advanced

DATEPARSE("MMddyy", RIGHT("Customer 1 – 080715", 6))

23. DATEPART

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.

Basic

DATEPART('month', #June 3, 2012#)

Intermediate

DATEPART('dayofyear',[Date])

Advanced

IF DATEPART('hour',[Datetime]) < 12 THEN 'Morning'
ELSEIF DATEPART('hour',[Datetime]) < 16 THEN 'Afternoon'
ELSEIF DATEPART('hour',[Datetime]) < 21 THEN 'Evening'
ELSE 'Night'
END

24. DATETIME

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.

Basic

DATE("March 15, 2013 5:30 PM")

Intermediate

DATETIME(STR([Date]) + ' ' + [Time]), 

Advanced

DATE(STR([Year]) + '/' + STR([Month]) + '/' + STR([Day]) + ' ' + STR([Time]))

25. DATETRUNC

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.

Basic

DATETRUNC('Month', #March 14, 2013#)

Intermediate

DATETRUNC('week', MIN([Date]))

Advanced

CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DATETRUNC('day', [Date])
WHEN 'Week' THEN DATETRUNC('week', [Date])
WHEN 'Month' THEN DATETRUNC('month', [Date])
END

26. DAY

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.

Basic

DAY(#March 14, 2013#)

Intermediate

DAY(DATEADD('day',[Date], 5 ))

Advanced

CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DAY([Date])
WHEN 'Month' THEN MONTH([Date])
WHEN 'Year' THEN YEAR([Date])
END

27. DEGREES

The DEGREES function converts a given number in radians to degrees.

DEGREES(number)

Basic

DEGREES(PI()*2)

28. DIV

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.

Basic

DIV(16,5)

Advanced

DIV(INT(SUM([Sales])), COUNTD([Customer ID]))

29. DOMAIN

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])

Basic

DOMAIN('http://www.twitter.com/DGM885')

30. ELSE

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.

Basic

IF [Sales] >= [Quota] THEN "Goal Met"
ELSE "Needs Improvement"
END

Intermediate

IF [Order Priority] = "Critical" or [Order Priority] = "High"
THEN [Shipping Cost] * 2
ELSE [Shipping Cost] * 1.5
END

31. ELSEIF

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.

Basic

IF [Profit Ratio] >= .25 then "Excellent"
ELSEIF [Profit Ratio] >= .1 then "Decent"
ELSEIF [Profit Ratio] >= 0 then "Needs Improvement"
ELSE "Urgent"
END

32. 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.

Basic

IF YEAR([Order Date]) = 2013 THEN [Sales] END

33. ENDSWITH

The ENDSWITH function does the same task as the STARTSWITH function; however, its focus is on the end of a string.

 ENDSWITH(String, Substring)

BASIC

ENDSWITH([City],"Orleans")

34. EXCLUDE

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]))}

BASIC

{ EXCLUDE [State]: SUM([Sales])}

35. EXP

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)

Basic

EXP(2)

36. FIND

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])

Basic

FIND(([City],"City")

Intermediate

FIND(([Customer] ,"'",4)

Advanced

IIF(CONTAINS([Customer],"'"),
(RIGHT([Customer],LEN([Customer]),FIND([Customer],"'",4)+2)),NULL)

37. FINDNTH

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.

Basic

FINDNTH("Jon Doe", "o", 2)

Intermediate

FINDNTH("Is it hot or is it cold?", "it", 2)

38. FIRST()

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.

Basic

FIRST()

Intermediate

WINDOW_AVG(SUM([Sales]),FIRST(),LAST())

Advanced

IF FIRST()=0 THEN WINDOW_AVG(SUM([Sales]),0,IIF(FIRST()=0,LAST(),0)) END

39. FIXED

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]))}

Basic

{ FIXED : MIN([Order Date])}

Intermediate

{ FIXED [Customer Name] : MIN([Order Date])}

40. FLOAT

The FLOAT function returns a floating point number, or in other words, a decimal number.

Basic

FLOAT(5)

Intermediate

INT([Teachers]) + FLOAT([Students])

Advanced

FLOAT(MID(2,[DollarString]))

41. FLOOR

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.

Basic

FLOOR(123.55)

Advanced

FLOOR(FLOAT(REGEXP_EXTRACT('abc 123.55','(d+.d+)')))

42. FULLNAME()

The following assumptions are used for the examples:

1. User 1
   • Full name: Malcolm Reynolds
   • Active Directory name: DOMAINm.reynolds
2. User 2
   • Full name: River Tam
   • Active Directory name: DOMAIN .tam
3. User 3
   • Full name: Jayne Cobb
   • Active Directory name: DOMAINj.cobb

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.

Basic

FULLNAME()='River Tam'

Intermediate

FULLNAME()=[Sales Person]

Advanced

CASE FULLNAME()
WHEN [Sales Person] Then 'True'
WHEN [Junior Manager] Then 'True'
WHEN [Snr Manager] Then 'True'
ELSE 'False'
END

43. GET_JASON_OBJECT

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)

44. GROUP_CONCAT

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)

Basic

GROUP_CONCAT(Region)="Central,East,West"

45. HEXBINX

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.

Basic

HEXBINX([Lon],[Lat])

Intermediate

HEXBINX([Lon]*[Scalar Value],[Lat]*[Scalar Value])/[Scalar Value]

46. HEXBINY

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.

Basic

HEXBINY([Lon],[Lat])

Intermediate

HEXBINY([Lon]*[Scalar Value],[Lat]*[Scalar Value])/[Scalar Value]

47. HOST

This is a Google Big Query–specific function that returns the host name as a string for a given URL.

HOST(string_URL)

Basic

HOST('http://www.google.com:80:/index.html')='www.google.com:80'

48. IF

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

Basic

IF [Order Quantity] > 10 THEN "Bulk Buy" ELSE "Non Bulk" END

IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END

Intermediate

IF [Ship Mode] = "Regular Air" 
THEN "Customs Required" 
ELSEIF [Ship Mode] = "Express Air" 
THEN "Express Customs" 
ELSE "No Customs" END

49. IFNULL

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)

Basic

IFNULL([Customer],"Unidentified")

50. IIF

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])

Basic

IIF(1<2,"True","False")

Intermediate

IIF([Time to Ship]>12,"Within SLA", "Outside SLA" )

Advanced

IIF([Order Date]< Today()-14 and [Ship Mode] = "N" 
,"High Priority",IIF([Order Date]<Today()-4 and
[Ship Mode] = "N","Medium Priority","Low Priority"))

51. INCLUDE

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]))}

Basic

AVG({ INCLUDE [Sub-Category]: AVG([Sales]))})

52. INDEX()

The INDEX function returns the row number of the current row within the window (pane) or partition. This function does not require any arguments.

Basic

INDEX()

Intermediate

Index() <= 5

Advanced

IF INDEX()=1 THEN WINDOW_AVG(SUM([Sales]),0,LAST(),0)) END

53. INT

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).

Basic

INT(3.7)

Intermediate

INT([Date])

Advanced

INT(MID(4,[QtyString]))

54. ISDATE

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.

Basic

ISDATE("This is not a date") 

Intermediate

ISDATE("01 January 2013")=TRUE
ISDATE("1st January 2012")=FALSE
ISDATE("1/9/2012")=TRUE

Advanced

ISDATE(STR([Year]) + '/' + STR([Month]) + '/' + STR([Day]))

55. ISFULLNAME()

The following assumptions are used for the examples:

1. User 1
   • Full name: Malcolm Reynolds
   • Active Directory name: DOMAINm.reynolds
2. User 2
   • Full name: River Tam
   • Active Directory name: DOMAIN .tam
3. User 3
   • Full name: Jayne Cobb
   • Active Directory name: DOMAINj.cobb

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.

Basic

ISFULLNAME('River Tam')

Intermediate

IF ISFULLNAME('Malcolm Reynolds') THEN 'Management' 
ELSEIF ISFULLNAME('River Tam') THEN 'Sales' 
ELSEIF ISFULLNAME('Jayne Cobb') THEN 'Public Relations' 
ELSE 'Unknown'
END

56. ISMEMBEROF()

This user function returns a Boolean (true/false) based on the logged-in user’s group membership defined on Tableau Server.

Basic

ISMEMBEROF('Sales')

Intermediate

IF ISMEMBEROF('Management') THEN 'Access Permitted'
ELSEIF ISFULLNAME('Sales') THEN 'Access Permitted' 
ELSE 'Access Denied'
END

57. ISNULL

The ISNULL statement is a simple Boolean function. It returns TRUE if expression is NULL; returns FALSE if it is not NULL.

ISNULL(expression)

Basic

ISNULL([Customer])

58. ISUSERNAME()

The following assumptions are used for the examples:

1. User 1
   • Full name: Malcolm Reynolds
   • Active Directory name: DOMAINm.reynolds
2. User 2
   • Full name: River Tam
   • Active Directory name: DOMAIN .tam
3. User 3
   • Full name: Jayne Cobb
   • Active Directory name: DOMAINj.cobb

Returns TRUE if the current user's username matches the specified username or FALSE if it does not match.

Basic

ISUSERNAME('j.cobb')

59. LAST()

This table calculation function does not require any arguments.

Basic

LAST()

Intermediate

WINDOW_COUNT(SUM([Sales]),FIRST(),LAST())

Advanced

IF INDEX()=1 THEN WINDOW_AVG(SUM([Sales]),0,LAST(),0)) END

60. LEFT

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)

Basic

LEFT([Customer Zip Code],3)

61. LEN

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)

Basic

LEN("Bob Hope")

Advanced

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)

62. LN

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)

Basic

LN(7.38905609893065)

63. LOG

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])

Basic

LOG(1000)

LOG(8,2)

64. LOG2

A Google Big Query–specific function that returns the logarithm base 2 of a number.

LOG2(number)

Basic

LOG2(16)

65. LOOKUP

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])

Basic

LOOKUP(SUM([Sales]),2)

Intermediate

LOOKUP(SUM([Sales]), FIRST()+1)

Advanced

LOOKUP( MIN([Region]),0)

66. LOWER

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)

Basic

LOWER("BatMan")

67. LTRIM

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)

Basic

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 "    ".

68. LTRIM_THIS

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)

Basic

LTRIM_THIS('Remove Me',' Me')

69. MAKEDATE

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)

Basic

MAKEDATE(2015, 6, 18)

70. MAKEDATETIME

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.

Basic

MAKEDATETIME([Order Date], #02:32:59#)

71. MAKETIME

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.

Basic

MAKETIME(15,37,59)

72. MAX

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)

Basic

MAX("Maureen","William")

Intermediate

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.

Advanced

MAX(ABS([Sales]-[Sales est]))

73. MEDIAN

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.

Basic

MEDIAN([Discount])

Intermediate

MEDIAN(DATEDIFF('day',[Order Date],[Ship Date])

74. MID

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])

Basic

MID("Michael Gilpin",9)

Intermediate

MID("Michael Gilpin",9,4)

75. MIN

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)

Basic

MIN([Sales])

MIN([Sales],[Profit])

Intermediate

MIN([Shipping Cost],[Maximum Shipping Cost])

Advanced

DATEDIFF('day',MIN([Order Date]),MAX([Ship Date]))

76. MONTH

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.

Basic

MONTH(#March 14, 2013#)

Intermediate

MONTH(DATEADD('day',[Date],5))

Advanced

CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DAY([Date])
WHEN 'Month' THEN MONTH([Date])
WHEN 'Year' THEN Year([Date])
END

77. NOT

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.

Basic

IF NOT QUARTER([Order Date]) = 1 THEN [Profit] END

Advanced

IF NOT ([Segment] = "Home Office" 
AND ([Order Priority] = "Low" OR [Order Priority] = "Medium"))
THEN [Shipping cost] END

78. NOW

The NOW function returns the current date and time.

Basic

NOW( )

Intermediate

DATEADD('hour', -5, NOW())

79. OR

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.

Basic

IF [Ship Mode] = "First Class" OR [Market] = "EMEA" THEN [Profit] END

Intermediate

IF ([Hours Worked]/[Hours Scheduled] < 0.8)
OR ([Hours Worked]/[Hours Scheduled] > 1.2)
THEN "Needs Attention" ELSE "Reasonable" END

80. PARSE_URL

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)

Basic

PARSE_URL('http://www.tableau.com','HOST')

81. PARSE_URL_QUERY

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)

Basic

PARSE_URL_QUERY('http://www.tableau.com?page=1&cat=4','page')

82. PERCENTILE

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.

Basic

PERCENTILE([Sales],0.5)

83. PI

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()

Basic

2*PI()*5

84. POWER

The POWER function raises the number to the specified power.

POWER(number,power)

Basic

POWER(4,3)

Intermediate

[Profit]*POWER(1+0.12,6)

85. PREVIOUS_VALUE

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)

Basic

SUM([Sales])+ PREVIOUS_VALUE(1)

86. RADIANS

The RADIANS function converts the given number from degrees to radians.

RADIANS(number)

Basic

RADIANS(360)

87. RANK

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'])

Basic

RANK(SUM([Sales]))

88. RANK_DENSE

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'])

Basic

RANK_DENSE (SUM([Sales]),'asc'))

89. RANK_MODIFIED

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'])

Basic

RANK_MODIFIED(SUM([Sales]))

90. RANK_PERCENTILE

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'])

Basic

RANK_PERCENTILE(SUM([Sales]))

91. RANK_UNIQUE

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'])

Basic

RANK_UNIQUE(SUM([Sales]))

92. RAWSQL_BOOL()

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.

Basic

RAWSQL_BOOL("%1=%2",[Order Date],[Ship Date])

Intermediate

RAWSQL_BOOL("%1='Oklahoma' AND %2 > 100.00",[State],[Sales])

Advanced

RAWSQL_BOOL("PATINDEX('%Henry%',%1)>0 AND %2>100.00",[Customer Name],[Sales])

93. RAWSQL_DATE()

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.

Basic

RAWSQL_DATE("%1 + 10", [Order Date])

Intermediate

RAWSQL_DATE("COALESCE(%2, %1)", [Order Date], [Ship Date])

Advanced

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])

94. RAWSQL_DATETIME()

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.

Basic

RAWSQL_DATETIME("%1 + '06:30:00'", [Order Date])

Intermediate

RAWSQL_DATETIME("DATETIMEFROMPARTS(2013,2,24,9,40,35,0)")

Advanced

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])

95. RAWSQL_INT()

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.

Basic

RAWSQL_INT("1+2")

Intermediate

RAWSQL_INT("CEILING(%1)",[Unit Price])

Advanced

RAWSQL_INT("DATEDIFF(day,COALESCE(%2,%1),GETDATE())",[Order Date],[Ship Date])

96. RAWSQL_REAL()

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.

Basic

RAWSQL_REAL("5.39 + 3.56")

Intermediate

RAWSQL_REAL("RAND()")

Advanced

RAWSQL_REAL("ROUND(CASE WHEN %1 = 'East' THEN %2 * 1.15
WHEN %1 = 'West' THEN %2 * 0.85
ELSE %2 END, 2)",[Region],[Sales])

97. RAWSQL_STR()

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.

Basic

RAWSQL_STR("'Trivial Case'")

Intermediate

RAWSQL_STR("%1 + '-' + CONVERT(varchar, %2)",[State],[Zip Code])

Advanced

RAWSQL_STR("STUFF(%1,CHARINDEX(' ', %1), 0,' ''' + %2 + ''' ')",
[Customer Name],[State])

98. RAWSQLAGG_BOOL()

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.

Basic

RAWSQLAGG_BOOL("SUM(%1) = SUM(%2)", [Sales],[Profit])

Intermediate

RAWSQLAGG_BOOL("SUM(CASE WHEN %1='Oklahoma' THEN %2 ELSE 0 END) 
> 100.00", [State], [Sales])

Advanced

RAWSQLAGG_BOOL("SUM(CASE WHEN PATINDEX('%Henry%', %1) > 0 THEN %2 ELSE 0 END) 
> 100.00",[Customer Name],[Sales]) 

99. RAWSQLAGG_DATE()

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.

Basic

RAWSQLAGG_DATE("MIN(%1)",[Order Date])

Intermediate

RAWSQLAGG_DATE("MAX(COALESCE(%2, %1))",[Order Date],[Ship Date])

Advanced

RAWSQLAGG_DATE("MAX(CASE WHEN %1 = 'Critical' THEN COALESCE(%3, %2) END)",
[Order Priority],[Order Date],[Ship Date])

100. RAWSQLAGG_DATETIME()

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.

Basic

RAWSQLAGG_DATETIME("MAX(%1)",[Ship Date])

Intermediate

RAWSQLAGG_DATETIME("MAX(%2—%1)",[Order Date],[Ship Date])

Advanced

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])

101. RAWSQLAGG_INT()

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.

Basic

RAWSQLAGG_INT("FLOOR(SUM(%1))",[Sales])

Intermediate

RAWSQLAGG_INT("CEILING(STDEV(%1))",[Unit Price])

Advanced

RAWSQLAGG_INT("AVG(DATEDIFF(day, COALESCE(%2, %1), GETDATE()))", 
[Order Date],[Ship Date])

102. RAWSQLAGG_REAL()

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.

Basic

RAWSQLAGG_REAL("SUM(%1)",[Profit])

Intermediate

RAWSQLAGG_REAL("VAR(%1-%2)",[Product Base Margin],[Discount])

Advanced

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])

103. RAWSQLAGG_STR()

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.

Basic

RAWSQLAGG_STR("MIN(%1)",[Order Date])

Intermediate

RAWSQLAGG_STR("MAX(LEFT(%1, 3))",[City])

Advanced

RAWSQLAGG_STR("CASE WHEN (SUM(%1)/SUM(%2)) > 0 
THEN 'Compliant' ELSE 'Noncompliant' END",[Profit],[Sales])

104. REGEXP_EXTRACT

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)

Basic

REGEXP_EXTRACT('abc 123','[a-z]+s+(d+)')

Intermediate

REGEXP_EXTRACT('ABC20DEF','20(.*)')

105. REGEXP_EXTRACT_NTH

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)

Basic

REGEXP_EXTRACT_NTH('abc 123','[a-z]+s+(d+)',1)

106. REGEXP_MATCH

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)

Basic

REGEXP_MATCH('ABC20DEF','[0-9]')
REGEXP_MATCH('ABCDEF','[0-9]')

107. REGEXP_REPLACE

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)

Basic

REGEXP_REPLACE('ABC20DEF','[0-9]', '*')

Intermediate

REGEXP_REPLACE('abc 123','s','-')

108. REPLACE

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)

Basic

REPLACE("[Order Priority]","Not Specified","High")

Intermediate

IIF([Order Date] < dateadd('month',-2,today()) ,
REPLACE([Order Priority],"Not Specified","High"),[Order Priority])

109. RIGHT

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)

Basic

RIGHT([Customer Zip Code],2)

110. ROUND

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:

• If the value of the number to the right of the rounding digit is less than five, the rounding digit is left unchanged.
• If the value of the number to the right of the rounding digit is five or higher, the rounding digit is raised by one.

  ROUND(number, [decimals])

Basic

ROUND([Sales])

Intermediate

ROUND(SUM([Profit])/SUM([Order Quantity]),2)

111. RTRIM

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)

Basic

RTRIM("Ruby Young  ")

112. RTRIM_THIS

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)

Basic

RTRIM_THIS('Remove me', ' me'

113. RUNNING_AVG

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])

Basic

RUNNING_AVG(SUM([Sales]))

Intermediate

RUNNING_AVG(SUM([Sales]), FIRST(),LAST())

Advanced

IF INDEX()=1 THEN RUNNING_AVG( SUM([Sales]) ) ELSE NULL END

114. RUNNING_COUNT

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])

Basic

RUNNING_COUNT(SUM([Sales]))

Intermediate

RUNNING_COUNT(SUM([Sales]), FIRST(),LAST())

Advanced

IF INDEX()=1 THEN RUNNING_COUNT(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END

115. RUNNING_MAX

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])

Basic

RUNNING_MAX( SUM([Sales]) )

Intermediate

RUNNING_MAX( SUM([Sales]) ), FIRST(),LAST())

Advanced

IF INDEX()=1 THEN RUNNING_MAX( SUM([Sales]) ) ELSE NULL END

116. RUNNING_MIN

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])

Basic

RUNNING_MIN(SUM([Sales]))

Intermediate

RUNNING_MIN(SUM([Sales]) ), FIRST(),LAST())

Advanced

IF INDEX()=1 THEN RUNNING_MIN (SUM([Sales]))ELSE NULL END

117. RUNNING_SUM

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])

Basic

RUNNING_SUM(SUM([Sales]))

Intermediate

RUNNING_SUM( SUM([Sales]) ), FIRST(),LAST())

Advanced

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.

118. SCRIPT_BOOL

SCRIPT_BOOL ("insert R code here", .arg1, .arg2, ... .argN)

119. SCRIPT_INT

SCRIPT_INT("insert R code here", .arg1, .arg2, ... .argN)

120. SCRIPT_REAL

SCRIPT_REAL ("insert R code here", .arg1, .arg2, ... .argN)

121. SCRIPT_STRING

SCRIPT_STRING ("insert R code here", .arg1, .arg2, ... .argN)

Advanced

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.

122. SIGN

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)

Basic

SIGN(-21)

Intermediate

IF SIGN(SUM([Profit]))=1
THEN "Profit"
ELSEIF SIGN(SUM([Profit]))=-1
THEN "Loss"
ELSE "Break-Even"
END

123. SIN

The SIN function returns the sine of a given number specified in radians.

SIN(number)

Basic

SIN(PI()/4)

Intermediate

SIN(RADIANS(90))

124. SIZE()

This table calculation function returns the total number of rows in the view/partition.

SIZE()

Basic

SIZE()

Intermediate

WINDOW_SUM(SUM([Sales]))/SIZE()

Advanced

IF INDEX()=1 THEN WINDOW_SUM(SUM([Sales]))/SIZE() ELSE NULL END

125. SPACE

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)

Basic

SPACE(4)

Intermediate

[Customer]+SPACE(2)+[City]+SPACE(2)+[Zip Code]

126. SPLIT

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)

Basic

SPLIT("Hi, how are you?", " ", -2)

Intermediate

INT(TRIM(SPLIT([Product ID], "-", -1)))

127. SQRT

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)

Basic

SQRT(49)

128. SQUARE

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)

Basic

SQUARE(7)

129. STARTSWITH

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)

BASIC

STARTSWITH([City],"New")

130. STDEV

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.

Basic

STDEV([Sales])

Intermediate

AVG([Sales])+STDEV([Sales])

Advanced

AVG([Sales])+(([Number of deviations])*STDEV([Sales]))

131. STDEVP

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.

Basic

STDEVP([Sales])

Intermediate

AVG([Sales]) + STDEVP([Sales])

Advanced

AVG([Sales])+ (([Number of deviations])*STDEV([Sales]))

132. STR

Returns a string for a given expression.

Basic

STR(5.0)

Intermediate

"Total Products = " + STR([Qty])

Advanced

STR([StartDate]) + ' to ' + STR([EndDate])

133. SUM

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.

Basic

SUM([Sales])

Intermediate

SUM([Sales])*[Commission Rate]

Advanced

SUM([Sales]) / COUNTD([Customer ID])

134. TAN

The TAN function returns the tangent of a given number specified in radians.

TAN(number)

Basic

TAN(PI()/4)

Intermediate

TAN(RADIANS(45))

135. THEN

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.

Basic

IF [Profit] >= 0 THEN "Profitable" ELSE "Unprofitable" END

136. TIMESTAMP_TO_USEC

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)

Basic

TIMESTAMP_TO_USEC(#2012-10-01 01:02:03#)

137. TLD

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)

Basic

TLD("https://www.twitter.com/DGM885")

138. TODAY

The TODAY function returns the current date. This is similar to NOW() but does not include the time component.

Basic

TODAY()

Intermediate

DATEADD('day', -30, TODAY())

139. TOTAL

TOTAL(Expression)

Basic

TOTAL(SUM([Sales]))

Intermediate

SUM([Sales])/TOTAL(SUM([Sales]))

Advanced

WINDOW_MAX(SUM([Sales])/TOTAL(SUM([Sales])))=(SUM([Sales])/TOTAL(SUM([Sales])))

140. TRIM

The TRIM function encompasses the logic of both LTRIM and RTRIM into one function.

TRIM(String)

Basic

TRIM("  Gemma Palmer     ")

141. UPPER

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)

Basic

UPPER("BatMan")

142. USEC_TO_TIMESTAMP

A Google Big Query–specific function that converts a Unix timestamp in microseconds to a TIMESTAMP data type.

USEC_TO_TIMESTAMP(expression)

Basic

TIMESTAMP_TO_USEC(1349053323000000)

143. USERDOMAIN

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.

Basic

USERDOMAIN()

Intermediate

CASE USERDOMAIN()
WHEN 'RETAIL' THEN 'Access Granted' 
WHEN 'WSALE  THEN 'Access Denied' 
END

Advanced

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

144. USERNAME()

The following assumptions used for the examples:

1. User 1
   • Full name: Malcolm Reynolds
   • Active Directory name: DOMAINm.reynolds
2. User 2
   • Full name: River Tam
   • Active Directory name: DOMAIN .tam
3. User 3
   • Full name: Jayne Cobb
   • Active Directory name: DOMAINj.cobb

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

Basic

USERNAME()='m.reynolds'

Intermediate

USERNAME()=[MANAGER]

Advanced

IF ISMEMBEROF('Management')then 'Access Permitted'
ELSEIF USERNAME()=[Manager]then 'Access Permitted'
ELSE 'Access Denied' END

145. VAR

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.

Basic

VAR(expression)

146. VARP

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)

147. WHEN

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.

Basic

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

Intermediate

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

148. WINDOW_AVG

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])

Basic

WINDOW_AVG(SUM([Sales]))

Intermediate

WINDOW_AVG(SUM([Sales]),FIRST,()LAST())

Advanced

IF INDEX()=1 THEN WINDOW_AVG(SUM([Sales]),0,IIF(INDEX()=1,LAST(),0))END

149. WINDOW_COUNT

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])

Basic

WINDOW_COUNT(SUM([Sales]))

Intermediate

WINDOW_COUNT(SUM([Sales]),FIRST(),LAST())

Advanced

IF INDEX()=1 THEN WINDOW_COUNT(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END

150. WINDOW_MAX

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])

Basic

WINDOW_MAX(SUM([Sales]))

Intermediate

WINDOW_MAX(SUM([Sales]),FIRST(),LAST())

Advanced

IF MAX([Ship Date]) = WINDOW_MAX( MAX([Ship Date]))
THEN SUM([Sales]) ELSE NULL END

151. WINDOW_MEDIAN

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])

Basic

WINDOW_MEDIAN(SUM([Sales]))

Intermediate

WINDOW_MEDIAN(SUM([Sales]),FIRST(), LAST())

Advanced

IF INDEX()=1 THEN WINDOW_MEDIAN(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END

152. WINDOW_MIN

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])

Basic

WINDOW_MIN(SUM([Sales]))

Intermediate

WINDOW_MIN(SUM([Sales]), FIRST(),LAST())

Advanced

IF INDEX()=1 THEN WINDOW_MIN( SUM([Sales])) ELSE NULL END

153. WINDOW_PERCENTILE

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])

Basic

WINDOW_PERCENTILE(SUM([Profit]),0.95,-2,0))

154. WINDOW_STDEV

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])

Basic

WINDOW_STDEV(SUM([Sales]))

Intermediate

WINDOW_STDEV(SUM([Sales]),FIRST(),LAST())

Advanced

IF INDEX()=1 THEN WINDOW_ STDEV(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END

155. WINDOW_STDEVP

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])

Basic

WINDOW_STDEVP(SUM([Sales]))

Intermediate

WINDOW_STDEVP(SUM([Sales]), FIRST(),LAST())

156. WINDOW_SUM

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])

Basic

WINDOW_SUM(SUM([Sales]))

Intermediate

WINDOW_SUM(SUM([Sales]) ),FIRST(),LAST)()

Advanced

IF INDEX()=1 THEN WINDOW_SUM( SUM([Sales]) ) ELSE NULL END

Advanced

IF INDEX()=1 THEN WINDOW_VAR(SUM([Sales]),0, IIF(INDEX()=1,LAST(),0)) END

157. WINDOW_VAR

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])

Basic

WINDOW_VAR(SUM([Sales]))

Intermediate

WINDOW_VAR(SUM([Sales]), FIRST(),LAST())

158. WINDOW_VARP

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])

Basic

WINDOW_VARP(SUM([Sales]))

Intermediate

WINDOW_VARP(SUM([Sales]),FIRST(),LAST())

Advanced

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.

159. XPATH_BOOLEAN

XPATH_BOOLEAN (XML string, XPATH expression string)

160. XPATH_DOUBLE

XPATH_DOUBLE (XML string, XPATH expression string)

161. XPATH_FLOAT

XPATH_FLOAT (XML string, XPATH expression string)

162. XPATH_INT

XPATH_INT (XML string, XPATH expression string)

163. XPATH_LONG

XPATH_LONG (XML string, XPATH expression string)

164. XPATH_SHORT

XPATH_SHORT (XML string, XPATH expression string)

165. XPATH_STRING

XPATH_STRING (XML string, XPATH expression string)

166. Year

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.

Basic

YEAR(#March 14, 2013#)

Intermediate

YEAR(DATEADD('day', [Date], 5 ))

Advanced

CASE [Parameter].[Date Unit]
WHEN 'Day' THEN DAY([Date])
WHEN 'Month' THEN MONTH([Date])
WHEN 'Year' THEN Year([Date])
END

167. ZN

ZN(expression)

Basic

ZN([Profit])

Intermediate

ZN(SUM([Profit]))-LOOKUP(ZN(SUM([Profit])),-1)