Data analysis often involves comparisons and calculations across a range of data. Tableau’s window functions enable such calculations, helping you unlock deeper insights. In this blog post, we’ll dive into the concept of window functions, understand their syntax, and see them in action with practical examples.

Basic Syntax of Window Functions

The basic syntax for a window function in Tableau is as follows:

WINDOW_<AGGREGATE_FUNCTION>(<expression> , start, end)

The <AGGREGATE_FUNCTION> can be any aggregate function like SUM, AVG, MIN, MAX etc., and <expression> is what you’re calculating. The optional start and end define the range of data the function covers.

The main types of window functions in Tableau include:

Window Sum: Calculates the total of the specified expression within the window. Syntax: WINDOW_SUM(SUM([Sales]))

Window Average: Calculates the average of the specified expression within the window. Syntax: WINDOW_AVG(AVG([Sales]))

Window Max/Min: Returns the maximum/minimum of the specified expression within the window. Syntax: WINDOW_MAX(MAX([Sales])), WINDOW_MIN(MIN([Sales]))

Window Count: Counts the number of items of the specified expression within the window. Syntax: WINDOW_COUNT(COUNT([Customer_ID]))

Running Total: Computes a running total of the specified expression. Syntax: RUNNING_SUM(SUM([Sales]))

Example Use Case: Calculating Moving Averages

Let’s consider a use case. Suppose you have sales data for the past four years and want to calculate a 2-year moving average to identify underlying trends. To see the moving average calculation, you can create a calculated field using the window function:

Here, the -1 and 0 indicate the window Tableau should consider for the calculation: current column and the column before it.

 

We can ascertain that the outcome derived from the ‘window_avg’ function corresponds to the average sales spanning the years 2022 and 2023.

Flory Hou
Author: Flory Hou