The Tableau WINDOW_SUM()
function is used to calculate the sum of the expression within the given window parameters.
The window is defined by the parameters you specify when writing the formula for the calculated field that uses this function.
This function allows you to perform a more selective sum formula than the regular SUM()
function.
For example, you can create a Window SUM calculated field that sums the value from the first row until the current row like this:
This tutorial will help you to understand how the WINDOW_SUM()
function works so that you can create a calculated field as in the example above.
First, you need to download the dataset for this tutorial here:
Once the dataset has been downloaded, you can load the file to Tableau as a Text File.
Next, open the Sheet view and create a Calculated Field named Total Sales Window SUM
with the following formula:
WINDOW_SUM(SUM([Total Sales]), FIRST(), 0)
The WINDOW_SUM()
function has three parameters:
- The first parameter is the field you want to compute using the function
- The second parameter is the
start
parameter. This row will be the first row computed by the function - The third parameter is the
end
parameter. This row will be the last row computed by the function
In the example above, the SUM()
of the Total Sales
field is used because you need an aggregated field to perform the WINDOW_SUM()
calculation.
Once the calculated field has been created, generate a table in your sheet with the following steps:
- Drag the
City
andItem
variables to the Rows shelf of the sheet - Add the
Total Sales
variable as a Text mark for the sheet
You should have the following table generated on your Tableau sheet:
Finally, left-click on the Total Sales Window SUM
variable two times and you will see the window sum column generated in your sheet:
And with that, you’ve added the window sum column to the table.
The second and third parameters of the WINDOW_SUM()
function are optionals. When you omit them, the calculated numbers will sum all the numbers in the view, giving the same result as the SUM()
function:
To take full advantage of the WINDOW_SUM()
function capabilities, you are recommended to add the start
and last
parameters when using the function.
You can use the FIRST()
and LAST()
functions to create a dynamic calculation range that gradually adds more rows to the calculation.
To add all values from the first row to the current row, use FIRST()
and 0
:
WINDOW_SUM(SUM([Total Sales]), FIRST(), 0)
To add all values from the current row to the last row, use 0
and LAST()
:
WINDOW_SUM(SUM([Total Sales]), 0, LAST())
To add the current row and the next row exclusively, use 0
and 1
:
WINDOW_SUM(SUM([Total Sales]), 0, 1)
To add the previous row and the current row exclusively, use -1
and 0
:
WINDOW_SUM(SUM([Total Sales]), -1, 0)
You can try the above formula in the example workbook that you can use here:
Tableau WINDOW_SUM Example Workbook
Now you’ve learned how the WINDOW_SUM()
function works in Tableau. Good job! 😉