40 Most Common Tableau Functions
Using function is in Tableau is essential for being able to represent your data in the best way. Tableau luckily has a list of functions that you can directly apply to your uploaded data. If you’ve used other function in SQL or Excel these should seem familiar. If you are looking for ways to upgrade your Tableau skill, Check out a list of Best Tableau Online Courses
Number Functions
In Tableau, a user can use different types of built-in functions which can be applied to numbers only. Following are the some of the commonly used “Number” functions:
a) ABS: This function returns the absolute value of the specified number.
Example: ABS(-10) = 10
b) CEILING: It rounds the given number to the nearest integer of equal or greater value.
Example: CEILING(5.1265) = 6
c) FLOOR: It rounds the given number to the nearest integer of equal or lesser value.
Example: FLOOR(5.1265) = 6
d) LOG: “LOG” returns the logarithm value of the number for the given base. If the base value is omitted, base 10 is being used by default.
Example: LOG(100) = 2, LOG(256,2) = 8
e) MIN: Returns the minimum value of an expression across all records
Example: MIN([Profit]) – This expression will return the minimum value of the profit across all the profit values
f) MAX: Returns the maximum value of an expression across all records
Example: MAX([Profit]) – This expression will return the maximum value of the profit across all the profit values
g) POWER: It returns the result of a number raised to the given power
Example: POWER(5,2) = 25
h) SQRT: It returns the square root of a given number
Example: SQRT(25) = 5
i) ZN: “ZN” stands for “Zero if Null”. It means it returns the given expression if it is not null, otherwise returns zero.
String Functions:
For the manipulation of different types of strings, a user can use built-in string functions in Tableau. Following are the some of the commonly used “String” functions:
a) CONTAINS: This function returns “True” if the user’s mentioned sub-string is present in the given string.
Example: CONTAINS(“University”, “versi”) = True
b) ENDSWITH: It returns “True” if the given string ends with the user’s mentioned sub-string
Example: ENDSWITH(“University”, “sity”) = True
c) FIND: Similar to excel “Find” functions, in tableau also FIND functions returns the position of the mentioned sub-string within the given string. If the sub-string isn’t found then it returns 0(zero)
Example: FIND(“University”, “versi”) = 4
d) ISDATE: This function is used to check whether the given string is a valid date. If the string is valid date then it returns “True”
Example: ISDATE(“2017-05-12”) = True
e) LEFT: It returns the specified number of characters from the start of the string.
Example: LEFT(“Calculation”, 4) = Calc
f) LEN: This function returns the number of characters in the given string.
Example: LEN(“Calculation”) = 11
g) LOWER: Convert a text string to all lowercase letters
Example: LOWER(“CalCulation”) = calculation
h) MID: It returns the characters from the middle of a text string given a starting position and length.
Example: MID(“Tableau”,2,4) = able
i) REPLACE: It returns a string in which every occurrence of the substring is replaced with the replacement string.
Example: REPLACE(“Calculation”, “ion”, “ed”) = Calculated
j) RIGHT: It returns the specified number of characters from the end of the given string.
Example: RIGHT(“Calculation”, 4) = tion
k) TRIM: Returns the string with both trailing and leading spaces removed
Example: TRIM(“ Budget “) = Budget
l) UPPER: Convert a text string to all uppercase letters
Example: LOWER(“Budget”) = BUDGET
no code needed
Date Functions:
For the manipulation of dates, a user can use built-in date functions in Tableau. Following are the some of the commonly used “Date” functions:
a) DATEADD: Add an increment to the specified date and returns the new date.
Example: DATEADD(‘month’, 3, #2017-07-12#) = 2017-10-12 12:00:00 AM
b) DATEDIFF: Returns the difference the two dates where start_date is subtracted from end_date
Example: DATEDIFF(‘month’, #2017-04-12#, #2017-07-15#) = 3
c) DATENAME: Returns the part of the given date as string.
Example: DATENAME(‘month’, #2017-05-14#) = May
d) DATEPARSE: It converts a string to a date in the specified format
Example: DATEPARSE(“dd.mmmm.yyyy”, “15.April.2017”) = 2017-04-15 12:00:00 AM
e) DATEPART: Returns a part of the given date as an integer where the part is defined by date_part.
Example: DATEPART(‘month’, #2018-01-17#) = 1
f) MONTH: Returns the month of the given date as an integer
Example: MONTH(#2018-02-19#) = 2
g) TODAY: Returns the current date
h) YEAR: Returns the year of the given date as an integer
Example: YEAR(#2018-02-19#) = 2018
Data Type Conversion Functions:
Using “ Data Type Conversion” functions of Tableau, a user can convert the values of one data type to another data type.
a) DATE: Returns a date given a number, string or date expression
Example: DATE(“2011-03-12”) = 2011-03-12
b) FLOAT: It returns the floating number from the given expression of any type
Example: FLOAT(“3”) = 3.00
c) INT: Returns an integer given an expression
Example: INT(-9.7) = -9
d) STR: Returns a string given an expression
Example: STR(90) = “90”
Logical Functions:
The following are some commonly used Logical Functions in Tableau:
a) AND: This function performs the logical conjunction of two or more expression. “AND” returns “True” when all the given expressions are true.
b) CASE: This function finds the first that matches the given and returns the corresponding
Example:
CASE [Name]
WHEN “John” THEN 90
WHEN “Emma” THEN 95
END
c) IF-ELSE: It tests a series of expressions and returns only that value corresponding to the first expression is true
Example:
IF [Profit] >0 THEN “Profitable”
ELSEIF [Profit] = 0 THEN “Breakeven”
ELSE “Loss”
END
d) OR: This function performs the logical disjunction on two or more expression. “OR” returns “True” when any of the given expressions are true.
Aggregation Functions:
In Tableau, a user can use a different type of aggregating Functions to aggregate the required fields at the required data level. Following are commonly used aggregating Functions:
a) ATTR: It returns the value of the given expression if it only has a single value for all the row in the group., otherwise it displays an asterisk (*). NULL values are ignored by this function
b) AVG: It returns the average value of the given expression or array of values
c) COUNT: It returns the count of items in the group. NULL values are not counted
d) COUNTD: It returns the unique count of items in the group.
e) MAX: Returns the maximum value of an expression across all records
Example: MAX([Profit]) – This expression will return the maximum value of the profit across all the profit values
f) MEDIAN: Returns the median value of an expression across all records
Example: MEDIAN([Profit]) – This expression will return the median value of the profit across all the profit values
g) MIN: Returns the minimum value of an expression across all records
Example: MIN([Profit]) – This expression will return the minimum value of the profit across all the profit values
h) PERCENTILE: an aggregate calculation that returns the percentile value from the given expression, corresponding to the specified percentile value
Example: PERCENTILE([Sales],0.9) = This expression will return the 90th percentile value from all the Sales value
i) SUM: It returns the sum of the given expression or array of values
Check out