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.

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.

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).

- 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).

- 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).

- 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).

- 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).

- 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).

- 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).

- 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.

- 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).

- 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).

- 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).

- 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).

- 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).

- 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.

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.

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

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

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.

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

It will change the visualization as below.

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

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

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

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

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

It will update the view as shown below.

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

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

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.

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

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

It will create a line chart in the Tableau view.

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

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.

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

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

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

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

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

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

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

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.

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

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

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

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

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

Now, your visualization is ready to be viewed.

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)
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.
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.
You can rank items using Table functions such as RANK, RANK_DENSE, RANK_MODIFIED, etc, in combination with the window function WINDOW_SUM.
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.
Leave a Reply