Window Functions in tableau

Window functions are essentially functions that operate on data within a graph. In Tableau, the area where we create a chart is called the “Canvas” or “Window.” This is where the graph takes shape as we drag and drop fields into columns, rows, and marks. When we work with the data displayed on the chart, it is referred to as a window calculation. By default, all window calculations are considered Table calculations because they work with the data in the canvas, and each graph in Tableau is also referred to as a table. Window functions enable you to derive valuable insights from your data by applying aggregations to aggregated data points in a worksheet.

Window functions are useful for calculating the minimum, maximum, standard deviation, and average of your data points. These functions can help you draw average lines, distinguish between values that are above or below average, and highlight the largest and smallest values in your visuals.

To gain a deeper understanding, let’s explore some examples and learn how to use window functions.

First, load the Sample Store dataset in Tableau. Drag the Order Date to the Columns shelf and present the data by month to view the monthly sales of the business. You will see some numbers on the chart.

How does Tableau generate these numbers? It accesses the underlying data source and sums up the sales for all transactions that occurred in January 2018, resulting in a total sales figure of 14,237. This is a regular aggregation of the data in the Sales column, where Tableau fetches the total sales for each month.

Now, to calculate the average of the monthly sales sums, we need to create a calculated field named “Window_Average.” If we use the syntax AVG(SUM([Sales])), Tableau will throw an error because we cannot aggregate an already aggregated value. Instead, we should use WINDOW_AVG(SUM([Sales])), which computes the average of the monthly sales sums present on the canvas.

To indicate this with a reference line, go to the Analytics pane, select Average Line, and choose the Table option. This will display an average reference line on the graph. When you hover over the average reference line, you will see the average value as 47,858. Alternatively, you can show this value in the title by dragging the Window_Average calculated field to the Details and inserting it into the sheet title. This represents the average of the data on the canvas.

Now, let’s explore the types of window functions in Tableau. All statistical functions, such as SUM, MIN, MAX, COUNT, and others, are available in their WINDOW versions, as shown below.

Window_SUM: In Tableau, this function calculates the total of the previous, current, and future values. It performs aggregation at the first level row in the Tableau sheet. To use this function, you need to create a calculated field. The syntax for this is WINDOW_SUM().

Let’s now look at an example to see how the WINDOW_SUM function works. We’ll use the Sample Superstore database to demonstrate how window calculations apply to the data.

First, drag Region and Sub-Category to the Rows shelf and drag Sales to the Marks shelf. Next, create a calculated field named Window_sum and enter the formula WINDOW_SUM(SUM([Sales]), -1, 0). This calculated field will appear in the Measures shelf. When you double-click on the Window_sum calculated field and compare it to the regular sum of sales values, you will notice that Measure Names are added to the Filters and Columns shelves, as shown in the visualization below.

Let’s understand how the WINDOW_SUM calculated field worked in the example above.

First, observe that the Sales value is 33,956 and the Window_SUM value is also 33,956. Both values are the same because, according to the formula (-1, 0), there is no previous value (as -1 is 0), and the current value is 33,956. Since there is no next value (0), the sum remains 33,956.

Now, let’s look at the Accessories in the East region. The sum of Sales is 45,033, and the Window_SUM value is 78,989. Here, the previous value is 33,956, the current value is 45,033, and there is no next value (0). Therefore, the Window_SUM value is 78,989.

(33,956 + 45,033 + 0) = 78,989

Window_Average: It is a table calculation in Tableau that calculates the average of a measure within a specified window of data. It's particularly useful for smoothing out data fluctuations and identifying trends over time. the syntax for this is - WINDOW_AVERAGE(). 

This function is useful for analyzing sales trends and smoothing out fluctuations.

Let’s see an example by creating a line chart with Order Date on the Columns shelf and SUM(Sales) on the Rows shelf. Next, create a calculated field using the formula WINDOW_AVG(SUM([Sales]), 0, 5). Here, 0 represents the current data point, and 5 represents the next five data points. This will calculate the average over a specific window, moving from the current data point to the next five data points. For clarity, let’s compare this with the regular SUM(Sales).

Now, drag the Moving Average calculation field to the Rows shelf. You will see a chart with a smoothed curve and a clear pattern.

Let’s understand how the WINDOW_AVG calculated field works in the example above.

First, it calculates the current point plus the next five data points, highlighted in the red box, and gives the average of those points, which you can see in the red box below chart. Then, it moves to the next data point and calculates the current point plus the next five data points, highlighted in the yellow box, and gives the average of those points, highlighted in the yellow box below chart.

Window_Minimum: window_minimum calculates the minimum value of a measure within a specified window of data. its particularly useful for identifying the lowest values within a group or over a period of time. the syntax for this is - WINDOW_MINIMUM().

In the example above, it calculates the minimum of the sum of sales from the canvas.