Introduction to Window Sum Tableau
Tableau has another finest function to do calculations. SUM is nothing but the total of the values presents in the block. Window SUM in tableau performs a total of the previous value, current value, and future value. It will perform aggregation of the first level row in the tableau sheet. We always need to create a calculated field for the calculations to be performed in the tableau. The syntax for this is – WINDOW SUM(), for these create calculated fields you can name anything you want. Windows in tableau offers different functions such as total, average, sum, etc. Windows SUM is one of them.
How does Windows Sum do in Tableau
I will explain the scenario with an example that how windows sum performs. Windows calculations perform Marks on the data.
- Connect to tableau public or desktop
- Extract the database as per your choice or connect to it
We have extracted the sample superstore database. From which I have extracted orders table to Tableau data source sheet.
-
- Drag category and sub-category dimension to
- Drag sales measure to Marks
- Create calculated field names as a
- Enter value as
WINDOW_SUM(SUM([Sales]),-1,0).
Sum(sales) measure we want to perform windows sum with, -1 is equal to the previous value and 0 is the current value.
- Win calculated field gets created at the Measure shelf.
- Double click on the calculated field Win.
- We can observe in the below, when we double click on the Win measures, measure names get added to the Filters and columns
- You can observe in the above image we have two columns, Sales and Win.
We will understand how the windows sum calculated field worked in the above example and how Win values are added to.
- Look first at sales value is – 167380 and win value is 167380, both values are same because as per the formula (-1,0) -1 is 0 and 0 is 167380 and that is the reason sum is 167380 because we have no previous value is and current values is
- Appliances Look at the next sales value is – 107532 and win value is 274912, the here previous value is 167380 and the current value is 107532 we have sum as 274912. (167380+107532=274912)
- Art SUM is 134651, the previous value is 107532 and the current value is 27119. (107532+27119=134651). We are aggregating the data of
- Binders win is 230532, the previous value is 27119 and the current value is 203413. (27119+203413=230532) and keep on adding further numbers in the
The current value is added to the previous and we get the sum of the window sales value for each category and sub-category.
- We will make a change in the category and sub-category position to have a better understanding. Move category first then sub-category.
- You can see in the below image there is a change in the win values due to row values interchangeability.
- For category as furniture with sub-category as Chairs we get the win value as 443,329 (328449 + 114880 = 443,329) 328449 is current value and 114880 is previous
- Now we are going to change front and end value in the window_sum.Edit the window_sum with -1 and 1. -1 is the previous value and 1 is the next value.
- Window_sum will calculate the previous value, current value, and
- For category as Furniture with bookcases category – win as 443,329, the previous value is 0, the current value is 114,880 and the next value is 328,449.
- For Category as furniture with Chairs as Sub-category – win value is 535,034. Previous value is 114,880, current value is 328,449 and next value is 91,705 (114,880+328,449+91,705=535,034).
Window_sum function will add previous, current and next value. This way the whole sum is performed.
Conclusion
We have learned about one of the WINDOW functions in tableau. It is a great way to use for the calculations. We need to understand the front and end values mentioned in the formula. Creating a calculating field for the function is needed. The SUM function will sum up all the values in the row from the first value to the current value or next value.
Recommended Articles
This is a guide to Window Sum Tableau. Here we discuss the introduction to Window Sum Tableau, how does windows Sum do, scenario with examples. You can also go through our other related articles to learn more –
