What is Window Average in Tableau?

Calculating a moving average to smooth out fluctuations in your data is one of the most common tasks in data analysis, but figuring out how to do it in Tableau can feel a bit tricky. The key is the WINDOW_AVG function, a powerful table calculation that lets you average values over a specific, sliding window of your data. This article will walk you through exactly what the WINDOW_AVG function is, how to use it step-by-step, and some practical examples for creating insightful visualizations.

What Are Table Calculations in Tableau?

Before we jump into WINDOW_AVG specifically, it helps to quickly understand its home: table calculations. In Tableau, a table calculation is a special type of calculation that operates on the aggregated data currently visible in your chart or table. Unlike regular calculated fields that perform operations on each raw row of your data source, table calculations work on the summarized numbers you see on screen.

Think of it this way: if you have a chart showing total monthly sales, a table calculation can compute values based on those monthly totals. For example, it could calculate the difference from the previous month, the running total since the beginning of the year, or, as we'll see, the average of the last three months.

They are essential for comparing values within your view, and WINDOW_AVG is one of the most versatile table calculations you can learn.

Breaking Down the WINDOW_AVG Function

The WINDOW_AVG function calculates the average of an expression within a specified window of data. The window is defined relative to the current row, making it perfect for moving averages that "slide" along your data.

The syntax looks like this:

WINDOW_AVG(expression, [start, end])

Let's break down each component:

• expression: This is the value you want to average. It must be an aggregated field, like SUM([Sales]) or AVG([Profit]).
• [start, end]: This optional part defines the "window" for your average. It tells Tableau how many rows before and after the current row to include in the calculation. You define the start and end of this window using relative offsets.

Here’s how those offsets work:

• -n: Refers to the nth row before the current row. For example, -2 means two rows prior.
• 0: Refers to the current row itself.
• +n: Refers to the nth row after the current row. For example, +1 means one row after.
• FIRST(): This keyword refers to the very first row in the partition.
• LAST(): This keyword refers to the very last row in the partition.

Putting it together, a formula like WINDOW_AVG(SUM([Sales]), -2, 0) tells Tableau: "For the current data point, average the SUM of Sales from two rows before it up to and including the current row." This gives you a 3-period moving average.

How to Create a Moving Average with WINDOW_AVG (Step-by-Step)

The most common application of WINDOW_AVG is to create a moving average on a time-series chart. This helps smooth out noisy data and makes it easier to spot underlying trends. Let's build one together using the Sample - Superstore dataset.

1. Set Up Your Time-Series Chart

First, we need a basic line chart showing sales over time.

• Drag the Order Date field onto the Columns shelf. Right-click it and choose Month (Continuous) — the one with the solid green background.
• Drag the Sales field onto the Rows shelf.

You should now see a line chart displaying your total sales for each month.

2. Create the Calculated Field

Next, we'll create our moving average calculation.

• Click the dropdown arrow in the data pane (top left) and select Create Calculated Field.
• Name the calculation something clear, like "3-Month Moving Average."
• In the formula box, type:
• Click OK.

3. Add the Moving Average to the Chart

Now, let's visualize our new calculation.

• Find your "3-Month Moving Average" field in the data pane and drag it onto the Rows shelf, to the right of your SUM(Sales) pill.

You'll see two separate line charts in your view.

4. Combine into a Dual-Axis Chart

To really see how the moving average smooths the data, overlay it on the original sales chart with a dual-axis chart.

• Right-click the "3-Month Moving Average" pill on the Rows shelf and select Dual Axis.
• Your two charts are now overlaid, but the y-axes might not line up. To fix this, right-click one of the axes on the right side and choose Synchronize Axis.

Your chart should now show both the original monthly sales and your new, smoother moving average line. Adjust the colors in the Marks card for clarity if needed.

Expanding The 'Window': Other Ways to Use WINDOW_AVG

The standard [-2, 0] window is great for trailing moving averages, but you can change the window parameters to get different insights.

Centered Moving Average

A centered moving average includes values before and after the current data point. It can sometimes give a more accurate picture of the trend at that point.

• Formula: WINDOW_AVG(SUM([Sales]), -1, 1)
• What it does: Averages the values of the previous month, the current month, and the next month, creating a 3-period centered average.

Cumulative Average

To find the average sales from the beginning of your timeframe up to the current point:

• Formula: WINDOW_AVG(SUM([Sales]), FIRST(), 0)
• What it does: Averages all sales values from the first data point (e.g., the first month) to the current data point.

Average of the Entire View

To get a single horizontal line representing the overall average across all months:

• Formula: WINDOW_AVG(SUM([Sales]), FIRST(), LAST())
• What it does: Calculates the average across all data points in the partition.

Important Considerations and Tips

Understanding "Compute Using"

When you use table calculations, the Compute Using setting determines the direction or partitioning of the calculation.

• For a time series, the default "Table (across)" works well, performing calculations from left to right along the date axis.
• Adding additional dimensions (like Region) can change how the calculation is performed. To compute independently for each region, set Compute Using to Pane (across) or specify the appropriate partitioning manually. This partitions the calculation accordingly.

Dealing with Nulls or Missing Data

If you have months with zero sales, they might appear as nulls, affecting your WINDOW_AVG.

• To treat nulls as zeros, wrap your expression in the ZN() function:

This ensures missing points are considered as zeros, smoothing your moving average accordingly.

Final Thoughts

The WINDOW_AVG function is a powerful tool in Tableau for calculating moving averages and other advanced windowed calculations. By understanding how to specify the expression and define the window's start and end points, you can effectively smooth noisy data, identify trends, and enhance your dashboards with meaningful insights.

Of course, mastering table calculation syntax can feel challenging at first. That's why tools like Graphed are useful, you can simply ask for what you need in plain English, and Graphed generates the exact chart with the proper calculations, saving time and reducing errors.