Using function is in Tableau is essential for being able to represent your data in the best way. Tableau has a list of functions that you can directly apply to your uploaded data easily. Check out, Tableau Tips as well as, the keyboard shortcuts for Tableau.
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
if) 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
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
Checkout the complete list of functions by following the link @ Tableau