Tableau Window Functions

What are Tableau Window Functions?

Tableau Window Functions are the table calculation functions that allow you to create and execute calculations over a set of records within the window. The window refers to the offsets from the current row. It includes a range of functions that allow you to perform calculations such as minimum, maximum, variance, covariance, average, etc., on a dataset.

A sample Tableau Window Function used in a Tableau visualization looks like the one shown below.

Tableau Window Functions - Sample
Key Takeaways
  • Tableau Window functions allow you to perform data calculations over a set of records within the window. The window refers to the offsets from the current row. 
  • Also, Tableau Window functions include a range of functions using which you can perform calculations such as minimum, maximum, variance, covariance, average, etc. on a dataset.
  • Tableau Window functions differ from the regular aggregate functions in data grouping and return values.
  • You can use Window functions to calculate the moving average, rank the data, or do other calculations.
  • Ensure that any null values are appropriately handled in your calculations using these functions.

Syntax of Window Functions

Tableau supports various Window functions. Below, we have provided a high-level overview and syntax usage to help you apply these functions in your visualization.

#1 – WINDOW_AVG

  • Description: This function returns the average of the specified expression within the window.
  • Syntax: This function accepts three arguments,, i.e.,., the expression, which is mandatory, whereas start and end are optional.
Tableau Window Functions - AVG

You can use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.

  • Example: WINDOW_AVG(SUM([Gross Sales]), FIRST()+1, 0) will return the average gross sales from the second record to the current record.

#2 – WINDOW_CORR

  • Description: It returns the Pearson correlation coefficient of two expressions within the window.
  • Syntax: This function accepts four arguments, i.e.,. 2 expressions (expr1 and expr2 are mandatory) and 2 optional arguments (start and end).
Tableau Window Functions - CORR
  • Example: WINDOW_CORR(SUM[Stock Price]), SUM([Gold Price]), -3, 0) will return the Pearson correlation coefficient of SUM[Stock Price] and SUM([Gold Price] from the 3 previous rows to the current one.

#3 – WINDOW_COUNT

  • Description: This function provides you with the count of expressions within the window.
  • Syntax: It has a mandatory argument (expression) and 2 optional arguments (start and end).
Tableau Window Functions - Count
  • Example: WINDOW_COUNT(SUM([Gross Sales]), FIRST()+3, 0) returns the count of SUM([Gross Sales]) from 3rd row to the current row.

#4 – WINDOW_COVAR

  • Description: It calculates the sample covariance of two expressions within the window.
  • Syntax: The function accepts 2 mandatory arguments (expr1 and expr2) and 2 optional arguments (start and end).
Tableau Window Functions - COVAR
  • Example: WINDOW_COVAR(SUM([Net Income]), SUM([Gross Sales]), -5, 0) will return sample covariance between SUM(Net Income) and SUM(Gross Sales) from the 5 previous rows to the current row.

#5 – WINDOW_COVARP

  • Description: It calculates the population covariance of two expressions within the window.
  • Syntax: The function accepts 2 mandatory arguments (expr1 and expr2) and 2 optional arguments (start and end).
Tableau Window Functions - COVARP
  • Example: WINDOW_COVARP(SUM([Net Income]), SUM([Gross Sales]), -5, 0) will return population covariance between SUM(Net Income) and SUM(Gross Sales) from the 5 previous rows to the current row.

#6 – WINDOW_MEDIAN

  • Description: It calculates the median of the expression within the window.
  • Syntax: It accepts expression as a mandatory argument and optional arguments (start and end).
Tableau Window_Sum - Median
  • Example: WINDOW_MEDIAN(SUM([Net Income]), FIRST()+1, 0) returns the median of SUM([Net Income] from the 2nd row to the current one.

#7 – WINDOW_MAX

  • Description: It determines the maximum of the expression within the window
  • Syntax: expression is mandatory argument and optional arguments (start and end).
Tableau Window Functions - MAX
  • Example: WINDOW_MAX(SUM([Gross Sales]), FIRST()+1, 0) determines the maximum of SUM(Gross Sales) from the 2nd row to the current row.

#8 – WINDOW_MIN

  • Description: It determines the minimum value of the expression within the window.
  • Syntax: It accepts expression as a mandatory argument and optional arguments (start and end).
Tableau Window Functions - MIN
  • Example: WINDOW_MIN(SUM([Gross Sales]), FIRST()+1, 0) determines the minimum of SUM(Gross Sales) from the 2nd row to the current row.

#9 – WINDOW_PERCENTILE

  • Description: It calculates the percentile values based on the expression within the window.
  • Syntax: It accepts 2 mandatory arguments, which include expression and number, and then optional arguments, i.e.,. start and end.
Tableau Window Functions - PERCENTILE
  • Example: WINDOW_PERCENTILE(SUM([Population]), 0.95, -1, 0) calculates the 95th percentile for SUM(Population) from the 1 previous row to the current row.

#10 – WINDOW_STDEV

  • Description: It calculates the sample standard deviation of the specified expression within the window.
  • Syntax: It accepts expression as a mandatory argument and optional arguments (start and end).
Tableau Window Functions - STDEV
  • Example: WINDOW_STDEV(SUM([Total Sales]), FIRST()+1, 0) calculates the standard deviation of SUM(Total Sales) from the 2nd row to the current row.

#11 – WINDOW_STDEVP

  • Description: It calculates the biased standard deviation of the specified expression within the window.
  • Syntax: expression is mandatory argument and optional arguments (start and end).
Tableau Window Functions - STDEVP
  • Example: WINDOW_STDEVP(SUM([Total Sales]), FIRST()+1, 0) calculates the standard deviation of SUM(Total Sales) from the 2nd row to the current row.

#12 – WINDOW_SUM

  • Description: It calculates the sum of the specified expression within the window.
  • Syntax: expression is mandatory argument and optional arguments (start and end).
Tableau Window Functions - SUM
  • Example: WINDOW_SUM(SUM([Inventory]), FIRST()+1, 0) computes the sum of SUM(Inventory) from the 2nd row to the current row.

#13 – WINDOW_VAR

  • Description: This function calculates the sample variance of the expression within the window.
  • Syntax: It accepts expression as a mandatory argument and optional arguments (start and end).
Tableau Window Functions - VAR
  • Example: WINDOW_VAR((SUM([Sales])), FIRST()+1, 0) calculates the variance of SUM(Sales) from the 2nd row to the current row.

#14 – WINDOW_VARP

  • Description: This function calculates the biased variance of the expression within the window.
  • Syntax: It accepts expression as a mandatory argument and optional arguments (start and end).
Tableau Window Functions - VARP
  • Example: WINDOW_VARP((SUM([Sales])), FIRST()+1, 0) calculates the variance of SUM(Sales) from the 2nd row to the current row

Examples

Let’s now work out a few examples to understand how to use window functions in your calculations for Tableau visualizations.

Example #1

In this example, we will create a visualization using the Window functions in Tableau to display maximum and minimum values for different regions across the products. We have leveraged the Sales_Product_Details dataset, which contains sales details across different product categories in different regions.

To display maximum and minimum values using window functions in Tableau, follow the instructions below:

Step 1: Import the Sales_Product_Details dataset into Tableau. 

Example 1 - Step 1

Step 2: In a new worksheet, drag Region to the Columns shelf and Product Description to the Rows shelf. Drag the Sales Revenue measure to the Text on the Marks card. Tableau will create a text table in the view.

Example 1 - Step 2

Step 3: Drag the Sales Revenue to Color. Change the Mark type to Square. Tableau will update the view.

Tableau Window Functions - Example 1 - Step 3

Step 4: Right-click on the Data pane and choose the Create Calculated Field option from the context menu. 

Tableau Window Functions - Example 1 - Step 4

Step 5: In the Calculation Editor, specify the logic to create a calculated field Maximum/Minimum Value. Here, we have used window functions WINDOW_MAX and WINDOW_MIN to create the Tableau window functions calculated field. 

Tableau Window Functions - Example 1 - Step 5

Step 6: Drag the calculated field to the Color on the Marks pane.

Example 1 - Step 6

It will change the visualization as below.

Tableau Window Functions - Example 1 - Step 6 - visualization

Step 7: Create another calculated field 1 by using the specified logic in the calculation editor.

Tableau Window Functions - Example 1 - Step 7

Step 8: Drag the calculated field 1 to Size. Now, increase the size to the maximum possible.

Tableau Window Functions - Example 1 - Step 8

Step 9: Click on Color. Change the Border to White.

Tableau Window Functions - Example 1 - Step 9

Step 10: Right-click on the Maximum/Minimum Value calculated field. Then, choose Edit Table Calculation…

Example 1 - Step 10

Step 11: In the Table Calculation window, choose the Table(down) option under Compute Using.

Tableau Window Functions - Example 1 - Step 11

It will update the view as shown below.

Tableau Window Functions - Example 1 - Step 11 - View

Step 12: Click on Color – Edit Colors. Change the color of the Others to White.

Tableau Window Functions - Example 1 - Step 12

As seen below, the visualization displays the maximum and minimum values for each region across all the products in the view.

Tableau Window Functions - Example 1 - Step 12 - displays

Example #2 – Calculating Moving Averages

In this example, we have shown how to calculate the moving averages using window functions in Tableau using the Alibaba dataset, which contains the list of sales orders transacted throughout the year across various stores in different cities in the US.

To calculate the moving averages using window functions in Tableau, follow the instructions below:

Step 1: Import the Alibaba dataset into Tableau using the Tableau Desktop interface.

Example 2 - Step 1

Step 2: Drag Order Date to the Columns shelf. Drag Sales to the Rows shelf.

Tableau Window Functions - Example 2 - Step 2

Step 3: Right-click on the Order Date and choose Month from the context menu.

Example 2 - Step 3

It will create a line chart in the Tableau view.

Tableau Window Functions - Example 2 - Step 3 - line chart

Step 4: Click on the down arrow next to the Search bar and choose Create Parameter.

Example 2 - Step 4

Step 5: In the Create Parameter window that opens, specify the parameter’s name and properties. Here, we have defined a range of values for the parameter.

Example 2 - Step 5

Step 6: Right-click on the Data pane. Choose Create Calculated Field.

Example 2 - Step 6

Step 7: In the calculation editor, specify the name and use the WINDOW_AVG function to calculate the moving average.

Tableau Window Functions - Example 2 - Step 7

Step 8: Drag the Moving Average field to the Rows shelf. Tableau will create the visualization below in the view.

Example 2 - Step 8

Step 9: Right-click on the parameter and choose Show Parameter.

Tableau Window Functions - Example 2 - Step 9

Step 10: Right-click on the vertical axis and choose the Dual Axis option.

Tableau Window_Sum - Example 3 - Step 10

Also, you will notice both Sales and moving average trends in the view.

Tableau Window Functions - Example 2 - Step 10 - Trends

Step 11: Change the parameter to 4. Tableau will update the view.

Tableau Window Functions - Example 2 - Step 11

Example #3

Here, we will create a visualization showing how many Item Types have more than the average sales using the Europe Sales Records dataset. The Europe Sales Records dataset contains the sales details of various stores within the EU including countries, regions, and Item Types.

Follow the steps outlined below:

Step 1: Import the Europe Sales Records dataset to Tableau. It is done as shown below.

Example 3 - Step 1

Step 2: Create a data visualization by dragging the Total Revenue to the Columns shelf and Item Type to the Rows shelf.

Example 3 - Step 2

Step 3: Add a reference line to the visual by dragging the Reference Line from the Analytics pane. Drop it to the Table.

Tableau Window Functions - Example 3 - Step 3

Step 4: Specify any changes on the Edit Reference Line, Band, or Box window.

Example 3 - Step 4

Step 5: Create a Tableau window functions calculated field Above Average Sales. It is done using the Window function.

Tableau Window Functions - Example 3 - Step 5

Step 6: Drag the Tableau window functions calculated field to the Color. Click on Color – Edit Colors, and then change the color to Green. 

Tableau Window Functions - Example 3 - Step 6

Now, your visualization is ready to be viewed.

Tableau Window Functions - Example 3 - Step 6 - visualization

Important Things to Note

  • Using Tableau Window functions partition can help you perform calculations with a group.
  • Window functions allow you to specify the window, i.e., start and end rows, to perform calculations.
  • Be aware of using Window functions in a large dataset due to the performance implications.

Frequently Asked Questions (FAQs)

1. How do window functions differ from regular aggregate functions in Tableau?

While regular aggregate functions perform similar operations to what window functions do, there are some key differences that you should be aware of:

1. Regular aggregate functions apply data aggregations to the entire dataset that match the expression, whereas window functions calculate the values based on expressions across a set of records.
2. Regular aggregate functions return a single value per group, whereas window functions return a value per row within the window.

2. How do I create a running total using window functions in Tableau?

To create a running total using window functions in Tableau, you can use the WINDOW_SUM() function in a Tableau window functions calculated field and then use this field to display the running total values. It will allow you to perform running total calculations by specifying the choice of start and end row in your dataset.

3. How can I rank items using window functions in Tableau?

You can rank items using Table functions such as RANK, RANK_DENSE, RANK_MODIFIED, etc, in combination with the window function WINDOW_SUM.

4. How do I handle NULL values in window functions in Tableau?

There are multiple ways you can handle NULL values in window functions in Tableau. These include:

Applying filters in the Filters pane to exclude null values
Using calculated fields where you can use functions such as IFNULL or ZN to replace null values with another substitute.
You can also use formatting options to handle null values using the Format pane.

Recommended Articles

Guide to What Is Tableau Window Functions. We learn how to use window functions in calculations for Tableau visualizations with examples and points to note. You can learn more from the following articles.

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *