Tableau's Hidden Functions: A Deep Dive into the Undocumented (Guest Post by Yovel Deutel)
We are incredibly excited
to have Yovel Deutel join us today for a guest blog. Believe me when I tell you
it’s going to blow your mind!! After accidentally wandering into the world of
Tableau in 2018 and deciding to stick around, Yovel Deutel has been helping
data rookies turn into data rockstars ever since! Yovel is a Tableau Ambassador
and leader of the Paris Tableau User Group. He also administers the Tableau subreddit and Discord communities. He loves to
chat about all things data and dataviz—so don't hesitate to hit him up on LinkedIn! When he's not decoding
data mysteries, you'll find him smashing shuttlecocks on the badminton court or
chilling with his two cats.
------------------------
Hello, fellow data enthusiasts! My name is Yovel, and today I'm spilling the beans on a trove of hidden and undocumented functions in Tableau that are guaranteed to make you the talk of your next data meetup—or at least give you some fun tricks to play with. So grab your favorite data set, put on your explorer's hat, and let's embark on this exciting journey together!
The Spark of Curiosity
It all began when I
stumbled upon the GREATEST() function in Tableau, a seemingly
better MAX() for a great (eh) number of use cases, which
wasn't documented anywhere in any official resources. Naturally, my first
thought was, "What else is Tableau hiding from us?" Is there
an underground society of functions waiting to be discovered? Were these the
data equivalent of hidden menu items at a fast-food joint?
Driven by curiosity, I
decided to embark on a quest to uncover these secret functions, on the off
chance that others existed. Little did I know, this would lead me down a rabbit
hole of experimentation and discovery.
The Hunt for Hidden
Functions
Over the course of a
week, I delved deep into Tableau's inner workings, reverse engineering the app
and testing various function names, and meticulously documenting my findings.
It felt like a digital treasure hunt, each new function a shiny gem waiting to
be discovered.
Some functions turned out
to be incredibly useful, others were more obscure, and a couple even had the
potential to crash your worksheet. While a few, like RANDOM(),
were known to the community, most of these had never been discovered before!
Discovering new functions
is exciting, and most of them can be used freely in your dashboards. A word of
caution however: these functions will only work with data extracts (not live
connections), and they are not officially supported by Tableau—so always test
thoroughly before deploying them in a production environment.
The Hidden Functions
Unveiled
Without further ado,
let's dive into the list of Tableau's undocumented functions. I'll highlight
below some of the most intriguing functions, complete with descriptions and
examples, and I've also compiled them into an interactive dashboard on Tableau
Public, which you can explore here.
GREATEST and LEAST
Returns the largest (or smallest) value from a list of expressions. Need at least 2 arguments to do their thing.
Example Usage:
GREATEST(3, -5, 9.6, NULL, 1) // Returns: 9.6
LEAST(41, NULL, 12, NULL, 3) // Returns: 3
Use Case: Simplifies comparisons
across multiple fields. Begone, nested MAX() and
MIN() statements!
ICONTAINS
Performs a case-insensitive search to check if a string contains another string.
Example Usage:
ICONTAINS("TABLEAU", "tab") // Returns: TRUE
Use Case: Useful for string
matching without worrying about capitalization.
COALESCE
Returns the first non-null expression among its arguments. It’s a better IFNULL()!
Example Usage:
COALESCE([NULL field1], [NULL field2],
"plane", "airport")
// Returns: "plane"
COALESCE(SUM([City Sales]), SUM([District
Sales])) // Returns: SUM([City Sales])
Use Case: Useful when dealing with datasets that have multiple optional fields, and you want the first available non-null value.
LIKE and NOTLIKE
Check if a string matches a certain pattern.
Example Usage:
LIKE("This blog post is great!","%blog%gre_t%") // Returns: TRUE
NOTLIKE("These functions are
interesting!","%interesting")
// Returns: TRUE
Use Case: Easier alternative to
regex when trying to match string patterns.
HASH
Generates a fixed-size hash value for a given input. Maybe your dashboard needs a touch of cryptographic magic?
Example Usage:
HASH("plane") // Returns: 308277797614010554
Use Case: Can be used for creating
unique identifiers or anonymizing data.
OVERLAY
Overlay one string on top of another at a specified position.
Example Usage:
OVERLAY("This is not
possible","completely",9,3)
// Returns: “This is completely possible”
OVERLAY("1234-5678-9012","****",6,4) // Returns: “1234-****-9012”
Use Case: Very useful for string
manipulation and data masking.
TRUNC
Chops off the decimal places from a number.
Example Usage:
TRUNC(9876.543) // Returns: 9876
TRUNC(9876.543,-2) // Returns: 9800
Use Case: Useful when you need
precise control over rounding behavior, to present or group data with a
consistent number of decimal places.
The Complete List
Here's the full list of
the undocumented functions I've uncovered:
§
COALESCE - Returns the first
non-null expression among its arguments.
§
COLLATIONFOR - Returns the locale of
the workbook. Requires an existing string to work. Will refresh with extract.
§ COT_STRICT - Returns the cotangent
of the given angle in radians. It’s basically COT() with a fancy name.
§ CURRENT_DATABASE - Returns the name of the
current database.
§ CURRENT_SCHEMA - Returns the name of the
current schema.
§
CURRENT_TIME - Returns the current
time, without the date part.
§
CURRENT_TIMESTAMP - Returns the current
date and time (duplicate of NOW())
§
CURRENTUSER - Returns the name of the
current user (duplicate of USERNAME()).
§
EXISTS - Checks if a subquery
returns any rows. Not usable in Tableau.
§
GENERICCAST - Converts an expression
to a specified type.
§
GREATEST - Finds the largest value
from a list of expressions.
§
GREATESTPROPAGATENULL - Like GREATEST(), but
propagates NULL values.
§
HASH - Generates a hash value
of the input.
§
ICONTAINS - Case-insensitive
version of CONTAINS().
§
ISCURRENTUSER - Checks if the current
user is the specified user (duplicate of ISUSERNAME()).
§
ISNOTDISTINCT - Checks if two
expressions are not distinct from each other.
§
LAG - Supposed to retrieves
the value from a previous row. /!\ Not
stable, will crash your worksheet /!\
§
LEAD - Retrieves the value
from the next row. /!\ Not stable, will
crash your worksheet /!\
§
LEAST - Finds the smallest
value from a list of expressions.
§
LEASTPROPAGATENULL - Like LEAST(), but
propagates NULL values.
§
LIKE - Checks if a string
matches a certain pattern.
§
MVIFNEQ - Returns the value if it
matches the specified one; returns * otherwise.
§
NORMALIZEDATETIME - Tries to standardize a
datetime value but doesn't seem to do much, as far as I can tell.
§
NOTLIKE - Checks if a string does
not match a certain pattern.
§
NULLIF - Returns NULL if two
expressions are equal; otherwise, returns the first expression.
§
OVERLAY - Overlays one string on
top of another at a specified position.
§
POSITION - Returns the position of
a substring in a string. Less useful than FIND() since you cannot specify a
start position.
§
RANDOM - Generates a seeded
random number between 0 and 1. Most known Tableau hidden functions.
§
SUBSTRING - Extracts a substring
from a string, similar to MID(), but can also use two strings as arguments.
§
SYS_NUMBIN - Creates custom bins for
a measure, letting you control bin size and add reference lines.
§
TRUNC - Truncates a number to a
specified number of digits.
Exploring Further
I invite you to explore
these functions yourself, in Tableau or through the interactive dashboard I made. Test them out,
see how they can simplify your calculations or create new possibilities. And if
you use them in your visualizations, or discover any interesting use cases with
them, I'd love to hear about it!
Final Thoughts
This journey has been a
rollercoaster of discovery, occasional frustration, and plenty of
"Aha!" moments. It's amazing to think that even after years and years
of using Tableau, there are still secrets waiting to be discovered.
I hope you find these
hidden functions as fascinating and useful as I did. Whether they simplify your
calculations, enhance your data transformations, or simply satisfy your
curiosity, they add another layer to what we can achieve with Tableau.
Happy data viz adventures!
Yovel Deutel
October 7, 2024
Niiiiiice!!!!!!!!!!
ReplyDeleteVery helpful and interesting article!
Thank you!
ROW_NUMBER is another super helpful calculation!
ReplyDeleteI don't think that works in Desktop.
Delete