What are window functions?
In tableau, a window function is a table calculation that allows you to perform calculations over a defined range of rows (a "window").
Table calculations are based on what is currently in the view. They are also further down in the Tableau order of operations.
The general syntax for a window function is:
Example using WINDOW_AVG:
For the Table Calculation
WINDOW_AVG(SUM([Sales]), -2, 0)
This means:
- Start 2 rows before the current row
- End at the current row
- Calculate the average sales across that window
The example below shows the WINDOW_AVG moving down each row and the result aligned with the row Tableau is looking at.
Tableau recalculates the moving average for each row based on the row's position. The "window" shifts as Tableau moves from one row to the next.
Above demonstrates how window functions can be useful for calculating moving averages and running totals.
Example using WINDOW_CORR:
In Tableau, WINDOW_CORR calculates the correlation between two measures across a window of rows. This example also looks at using First(), which returns the number of rows from the current row to the first row.
For the Tableau Calculation
WINDOW_CORR(SUM([Sales]), SUM([Profit]), FIRST(), 0)
This means:
- Start at the first row in the partition
- End at the current row
- Calculate correlation between Sales and Profit across that window
What type of window functions are there?
Now you understand how a “window” works, you can now apply it across a wide array of calculations.
| Function | Description |
|---|---|
WINDOW_SUM() | Sum of values in the window |
WINDOW_AVG() | Average of values in the window |
WINDOW_MIN() | Minimum value in the window |
WINDOW_MAX() | Maximum value in the window |
WINDOW_MEDIAN() | Median value in the window |
WINDOW_STDEV() | Sample standard deviation in the window |
WINDOW_STDEVP() | Population standard deviation in the window |
WINDOW_VAR() | Sample variance in the window |
WINDOW_VARP() | Population variance in the window |
WINDOW_COUNT() | Number of non-null values in the window |
WINDOW_CORR() | Correlation between two expressions |
WINDOW_COVAR() | Sample covariance between two expressions |
WINDOW_COVARP() | Population covariance between two expressions |
WINDOW_PERCENTILE() | Percentile value within the window |