Today, we will talk about the different basic functions in Tableau. This was prompted by a poster on the Tableau forums asking for more information about these functions. I am not an expert on Tableau's data engine. Therefore, I will keep this discussion somewhat high-level.

__SQL FUNCTIONS__

These functions all exist in the SQL language. It is my hypothesis that they are passed directly to the underlying SQL for computational efficiency. However, this is one area I am currently looking into. If anyone knows for sure, please let me know in the comments.

**COUNT( [Any Row-Level Data Type] )**

This function returns the number of non-null rows in the pane. If you have a complete data set, this will be identical to SUM( 1 ). This function is useful if you want to know how many rows satisfy a certain condition. For example, you can find the number of rows from 2013 with

IF [Year] = 2013 THEN COUNT( [Year] ) END

**SUM( [Row-Level Numeric] )**

This function returns the sum of non-null rows in the pane. I believe it to be the easiest function to comprehend. For example, you can find total sales with

SUM( [Sales] )

**AVG( ****[Row-Level Numeric] ****)**

This function returns the average of non-null rows in the pane. Care should be taken when working with this function. It returns the sum of all non-null rows divided by the count of all non-null rows. If you want an average calculated at a level that isn't the row-level, you need to create your own SUM() and COUNT() functions. For example, you can find average profit with

AVG( [Profit] )

**COUNTD( ****[Any Row-Level Data Type] ****)**

This function returns the distinct count of non-null rows in the pane. It is especially useful for calculating averages at a different grain than your underlying data. If my data was at the transaction level, I could calculate the average profit per store as

SUM( [Profit] ) / COUNTD( [StoreNumber] )

**DATEADD( [Date Unit], [Integer], [Date] )**

This function adds a certain number of units to a date value. The unit type and number of units are supplied by the user. For example, 3 days after [Date] can be found as

DATEADD( "day", 3, [Date] )

**DATEDIFF( [Date Unit], [Start Date], [End Date] )**

This function returns the number of date units between the start and end dates. For example, the number of years between [Date1] and [Date2] is

DATEDIFF( "year", [Date1], [Date2] )

**LEN( [String] )**

This function returns the number of characters in a string. For example, the number of characters in "Tableau" ( 7 ) is

LEN( "Tableau" )

**LEFT( [String], [Integer] )**

This function returns the first [Integer] characters in a string. For example, the first 4 characters in "Tableau" (Tabl) is

LEFT( "Tableau", 4 )

__TABLEAU FUNCTIONS__

To my knowledge, these functions do not have direct relationships to SQL functions. Therefore, my hypothesis is that these are calculated within Tableau. If there is more to this, please let me know in the comments.

**LOOKUP( [Function], [Offset] )**

This is the workhorse of all window functions. It returns the value of the given function at another value outside of the current pane. This requires use of "Compute Using." If you are not familiar with "Compute Using", please refer to my previous post

Working with Table Calculations in Tableau. For example, if you want to the see the value of SUM( [Sales] ) in the previous row, you can use

LOOKUP( SUM( [Sales] ), -1 )

**RUNNING_SUM( [Function] )**

This function returns the sum of the function in all previous rows in the table. For example, the sum of all previous daily counts is

RUNNING_SUM( COUNT( [Sales] ) )

**WINDOW_SUM( [Function], { [StartOffset], [EndOffset] } )**

This function returns the sum of the function for all rows within the window defined by [StartOffset] and [EndOffset]. This is the first occurrence of optional parameters. If [StartOffset] and [EndOffset] are omitted, the entire table is used. For example, if you would like to the sum of sales for the last 3 rows (4 rows total including the current row), you can use

WINDOW_SUM( SUM( [Sales] ), -3, 0 )

**FIRST()**

This function returns the current offset from the first row. For example, if you want to emulate RUNNING_SUM using WINDOW_SUM, you can use

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

These are a good portion of the Tableau functions that I regularly use. However, there are quite a few useful functions that are not mentioned here. If you feel like I missed something important, let me know. I hope you found this informative. Thanks for reading.

Brad Llewellyn

Associate Consultant

Mariner, LLC

llewellyn.wb@gmail.com

https://www.linkedin.com/in/bradllewellyn