Tableau Table Functions

Tableau provides various Table Functions to perform the calculation on entire visual data. They are FIRST, LAST, INDEX, RUNNING_SUM, RUNNING_AVG, WINDOW_SUM, WINDOW_VAR, etc. In this article, we will show you how to use Tableau Table Functions with an example of each.

Tableau Table Functions

The following examples will show you the list of Table Functions in Tableau.

To demonstrate these Tableau Functions, we use the below table. Remember, this is an Excel Worksheet. So, Please refer Connecting to the Excel Files in Tableau.

Tableau Table Functions 1

FIRST Function

The Tableau FIRST function returns values from 0 to -n in a table and the syntax of this FIRST is:

FIRST()

To demonstrate these Tableau functions, we have to use the table calculation. To create a table calculation, please navigate to Analysis Tab and select Create Calculated Field… option.

Create Table Calculated Field 2

Once you click on the Create Calculated Field… option, the following window will be opened. Here, we renamed the default calculation name as FIRST.

Click OK to close this window

Table First Function 3

Or you can click Default Table calculation hyperlink to alter the default settings. Clicking that link will open the following window.

Tableau Table Functions 4

Let me add this FIRST field to an existing table (by dragging the field to Measures Shelf). Please refer Create Table Report article to understand the process of creating a table

Table First Function 5

You can edit this table calculation by clicking the down arrow. Next, select the Edit Table Calculation option from the context menu.

Edit Tableau Table Functions 6

It will open a new window called Table calculation.

Tableau Table Functions 7

Use this window to change the calculation process. We already explained these properties in our previous article. So, please refer to Table Calculations article to understand these functionalities.

Table Functions 8

LAST Function

The LAST will return numbers from n to 0. I mean, the last record as 0, and first record as n. The syntax of this Tableau LAST Function on a table is:

LAST()

Let me add this LAST field to Measures shelf

Table LAST Function 9

INDEX

The Tableau Table INDEX function will return numbers from i to n. I mean, the first record as 1, and last record as n. The syntax of this INDEX is:

INDEX()

Let me add this INDEX field to Measures shelf

Tableau Table index Function 10

RUNNING_AVG

The Tableau RUNNING_AVG function will calculate the running average of the table and the syntax of this RUNNING_AVG is:

RUNNING_AVG(Expression)

The below statement calculates the running average of the Sales column across the table.

RUNNING_AVG(SUM([Sales]))

Let me add this RUNNING_AVG field to the Measures shelf.

Third record: (25 + 25 + 60) /3 = 36.667 = 37

running_avg Function 11

RUNNING_COUNT

The Tableau RUNNING_COUNT function will return the running count of the table and the syntax of this RUNNING_COUNT is:

RUNNING_COUNT(Expression)

The below statement returns the running count of the Sales column.

RUNNING_COUNT(SUM([Sales]))

Let me add this RUNNING_COUNT field to the Measures shelf.

running_count Function 12

RUNNING_MAX

The Tableau RUNNING_MAX function returns the maximum running value in a table and the syntax of this RUNNING_MAX is:

RUNNING_MAX(Expression)

Returns the maximum running value in a Sales column.

RUNNING_MAX(SUM([Sales]))

Let me add this RUNNING_MAX field to the Measures shelf.

running_max Function 13

RUNNING_MIN

The Tableau RUNNING_MIN function will return the running minimum value and the syntax of Table RUNNING_Min is:

RUNNING_MIN(Expression)

Returns the minimum running amount in a Sales column.

RUNNING_MIN(SUM([Sales]))

Let’s add the RUNNING_MIN field to the Measures shelf.

running_min Function 14

RUNNING_SUM

The Tableau Table RUNNING_SUM function will calculate the running total and the syntax of this RUNNING_SUM is:

RUNNING_SUM(Expression)

Returns the running total of a Sales column.

RUNNING_SUM(SUM([Sales]))

Let’s add the RUNNING_SUM field to the Measures shelf.

Third record: (50 + 60) = 110

running_sum Function 15

Tableau SIZE

The Tableau SIZE function returns total records in a table or pane and the syntax of this SIZE is:

SIZE()

Let me add this SIZE field to the Measures shelf. As you can see, we are changed the default calculation to display the size based on Occupation

Tableau Table Size Function 16

TOTAL

The Tableau TOTAL function will return the complete total in a window and the syntax of this Total is:

TOTAL(Expression)

Returns the total sales amount in this table. i.e., 25 + 25 + 60 + 24 +….. + 2320

TOTAL(SUM([Sales]))

Let me add this TOTAL field to the Measures shelf.

Tableau Table Total Function 17

This time we are computing using Occupation. It means the total function will sum based on occupation.

First set: 25 + 25 + 60 + 24 = 133

Table Total Function 18

WINDOW_AVG

The Tableau WINDOW_AVG function will calculate the average of the data in a table and the syntax of this WINDOW_AVG is:

WINDOW_AVG(Expression, start_point, end_point)

It calculates the average of Total Sales.

WINDOW_AVG(SUM([Sales]), FIRST(), LAST())

Let me add this WINDOW_AVG field to Measures shelf.

Here, the Sum of 15 records is 28, 794.

Average = 28,794/15 = 1919.6 = 1920

window_avg Function 19

This time we are calculating the average from the first record to Last – 12 (means 15 – 12). i.e., the first record to the third record

Window_Avg = 25 + 25 + 60 = 36.67 = 37

Tableau window_avg Function 20

WINDOW_MEDIAN

The Tableau WINDOW_MEDIAN function will calculate the median from the start point to end in a table. The syntax of this WINDOW_MEDIAN is:

WINDOW_MEDIAN(Expression, start_point, end_point)

Calculate the median of Total Sales.

WINDOW_MEDIAN(SUM([Sales]), FIRST(), LAST())

Let me add this WINDOW_MEDIAN field to the Measures shelf.

window_median Function 21

WINDOW_MAX

The Tableau WINDOW_MAX function returns the maximum value from the start point to the end in a table. The syntax of this WINDOW_MAX is:

WINDOW_MAX(Expression, start_point, end_point)

Finds the maximum value from the first record to last – 8 (means, 15 – 8 = 7)

WINDOW_MAX(SUM([Sales]), FIRST(), LAST() - 8)

Let me add this WINDOW_MAX field to the Measures shelf.

window_max Function 22

WINDOW_MIN

The Tableau WINDOW_MIN function returns the Minimum value from start point to end in a table. The syntax of the WINDOW_MIN is:

WINDOW_MIN(Expression, start_point, end_point)

Finds the minimum value from first record + 4 to the last record

WINDOW_MAX(SUM([Sales]), FIRST(), LAST() - 8)

Let me add this WINDOW_MIN field to the Measures shelf.

Tableau window_min Function 23

WINDOW_SUM

The Tableau WINDOW_SUM function calculates the sum from start point to endpoint in a table and the syntax of this WINDOW_SUM is:

WINDOW_SUM(Expression, start_point, end_point)

Calculate the Total Sales from 11 to 15.

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

Let me add this WINDOW_SUM field to the Measures shelf.

Sum from 11 to 15 = 3078 + 540 + 4320 + 699 + 2320 = 10957.

window_sum Function 24

WINDOW_VAR

The Tableau WINDOW_VAR function returns the variance of the sample population from the start point to the endpoint in a table. The syntax of this WINDOW_VAR is:

WINDOW_VAR(Expression, start_point, end_point)

Calculate the variance from the current row to the previous row.

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

Let me add this WINDOW_VAR field to the Measures shelf.

window_var Function 25

WINDOW_VARP

The Tableau WINDOW_VARP function returns variance of the complete population from start point to end point in a table. The syntax of this WINDOW_VARP is:

WINDOW_VARP(Expression, start_point, end_point)

Calculate the variance from the third row to the previous row.

WINDOW_VARP(SUM([Sales]), FIRST()+ 2, 1)

Let me add this WINDOW_VARP field to Measures shelf.

window_varp Function 26

WINDOW_STDEV

The Tableau WINDOW_STDEV function returns the standard deviation of the sample population from the start point to the endpoint in a table. The syntax of this WINDOW_STDEV is:

WINDOW_STDEV(Expression, start_point, end_point)

Calculate the standard deviation from the second row to the current row.

WINDOW_STDEV(SUM([Sales]), FIRST()+ 1, 1)

Let me add this WINDOW_STDEV field to the Measures shelf.

Tableau window_stdev Function 27

WINDOW_STDEVP

The WINDOW_STDEVP returns the standard deviation of the complete population from start point to endpoint in a table. The syntax of the Tableau WINDOW_STDEVP Function is:

WINDOW_STDEVP(Expression, start_point, end_point)

Calculates the standard deviation of the complete population.

WINDOW_STDEVP(SUM([Sales]), FIRST()+ 4, 2)

Let me add this WINDOW_STDEVP field to the Measures shelf.

window_stdevp Function 28

WINDOW_PERCENTILE

The Tableau WINDOW_PERCENTILE function returns specified percentile from start point to endpoint in a table. The syntax of the WINDOW_PERCENTILE is:

WINDOW_PERCENTILE(Expression, percentile, start_point, end_point)

Calculate the 25% percentile from the previous two rows to the current row.

WINDOW_STDEVP(SUM([Sales]), FIRST()+ 4, 2)

Let me add this WINDOW_PERCENTILE field to the Measures shelf.

window_percentile Function 29

WINDOW_CORR

The Tableau WINDOW_CORR function returns the correlation between two measures from the start point to endpoint in a table. The syntax of the WINDOW_CORR is:

WINDOW_CORR(Expression1, Expression2, start_point, end_point)

Finds the correlation between total cost and sales amount from the previous three rows to the current row.

WINDOW_CORR(SUM([Total Cost]), SUM([SalesAmount]), -3, 2)

Let me add this WINDOW_CORR field to Measures shelf.

window_corr Function 30

WINDOW_COVAR

The Tableau WINDOW_COVAR function returns the covariance of the sample population from the start point to the endpoint in a table. The syntax of the WINDOW_COVAR is:

WINDOW_COVAR(Expression1, Expression2, start_point, end_point)

It finds the sample covariance between total cost and sales amount from the previous three rows to the current row.

WINDOW_COVAR(SUM([Total Cost]), SUM([SalesAmount]), -3, 0)

Let me add this WINDOW_COVARP field to Measures shelf.

wiondow_covar Function 31

WINDOW_COVARP

The WINDOW_COVARP function returns total population covariance from the start point to endpoint in a table. The syntax of the Tableau WINDOW_COVARP is:

WINDOW_COVARP(Expression1, Expression2, start_point, end_point)

It finds the population covariance between total cost and sales amount from the previous two rows to the current row.

WINDOW_COVARP(SUM([Total Cost]), SUM([SalesAmount]), -2, 0)

Let me add this WINDOW_COVARP field to Measures shelf.

window_covarp Function 32

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.