Sign in or start a free trial to avail of this feature.
9. WINDOW Functions
Window functions are a very popular family of functions, that enable you to access all the data showing in the view. In this lesson, we will use WINDOW_AVG in a new bar-chart visualization.
To explore more Kubicle data literacy subjects, please refer to our full library.
- Enable you to perform calculations on all the data points showing in the current view
- Very convenient for adding AVERAGE and MEDIAN calculations to your formulas
- Most popular functions within this family are:
In the previous lesson I used the Total function to calculate the percentage of customers combining it with the Count D function and the Customer ID column. A similar but not identical function is called Window Sum which basically adds together the results of an expression that are available in the window. The Total function queries the data source directly whereas the Window Sum function queries the view. Window Sum belongs to a larger set of functions such as Window Average, Window Median et cetera. And you'll regularly use these in Tableau. And they can be found under the Table calculation when I scroll down to the bottom.
Let's see the Window functions in action with a simple example.
I'm going to duplicate the existing sheet, and I'm going to remove Percentage of Customer.
And what I'd like to show here is the difference between the sales for each product versus the average sales by product.
If I go to my Analytics tab, I can simply drop the average line onto this particular sheet and this gives me a value of just under 57,000. What I want to do in a formula is to subtract this value for each of these bars and to do this I'm going to use Window Average. So, back in the Data pane, I'll create a new calculated field called Difference from Average Sales.
And this formula will take the sum of sales and subtract the window average of the sum of sales.
And that's simply it.
The first part of the formula simply takes each bar that we have in our window which corresponds to a product and the Window Average function simply calculates the average of the sum of sales for the dimensions that are included in the window which in this case is the product name.
I'll then press OK and add to this to my chart.
And as you can see, the top five products are actually above the average but all the remaining products are below the average sales.
And when you're creating charts like this, the Window Average function is incredibly useful. An alternative to this is Window Median. Let's now take another example but this time I'm going to use Window Max.
So, I'll right click and duplicate.
I'll remove difference from the average and this time round I'm going to create a new calculated field that calculates the distance for each product from the top performing product which is Byresta. So, I'll right click and Create Calculated Field.
And this will be called Difference from Top Product.
And in this scenario I'm going to take the max value which is going to be Window Max for the sum of sales.
And then I'll simply subtract the sum of sales.
Just remember to put in my bracket and my formula is complete. As you may have noticed, the Window functions generate a consistent single value that can be applied across each bar in my bar chart. When you're creating Window functions, it's always good to remember that a window function is creating a single value. And most of the time when using Window functions, the expression within the brackets will be the same as the expression you're adding or subtracting. And in this case, my expression is sum of sales.
Let's now plot this value.
And as you can see, the difference between Byresta and the max sales is zero because Byresta has the max sales. For Polinia it's 86,538 and as we we move on down, the distance gets greater.
In addition to Window Sum, Window Average and Window Max, we have all the same functions that you would normally see in the Aggregate List.
So, when I go to Table Calculation, you'll see Window Count, Max, Median, Min, Percentile, Sum and Standard Deviation and Variance options as well.
In most cases I tend to use Window Average, Window Max and Min and Window Sum by far the most. Try experimenting with these in some of your own calculations but be careful to always check your answer at least initially using Excel or by hand to make sure you're using these functions correctly.