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