Rolling period calculations in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate 3-month rolling sum using Tableau LOD expressions

You have monthly sales data. Which Tableau LOD expression correctly calculates the 3-month rolling sum of sales ending at the current month?

A RUNNING_SUM(SUM([Sales]))

B { FIXED [Month] : SUM([Sales]) }

C { INCLUDE [Month] : SUM([Sales]) }

D WINDOW_SUM(SUM([Sales]), -2, 0)

💡 Hint

Think about how to sum sales over the current and previous two months in a rolling window.

WINDOW_SUM with offsets -2 to 0 sums sales for the current month and two prior months, creating a 3-month rolling sum.

❓ visualization

intermediate

Identify the correct rolling average line chart

You want to visualize a 6-month rolling average of revenue over time. Which chart best represents this rolling average?

A Bar chart showing monthly revenue with a 6-month rolling average line overlay

B Line chart with monthly revenue points connected directly

C Pie chart showing revenue distribution by month

D Scatter plot of monthly revenue vs. month number

💡 Hint

Rolling averages are best shown as a smooth line over time to show trends.

A bar chart with monthly revenue and a rolling average line overlay clearly shows both raw data and smoothed trend.

🧠 Conceptual

advanced

Understanding difference between RUNNING_SUM and WINDOW_SUM

Which statement correctly explains the difference between RUNNING_SUM and WINDOW_SUM in Tableau when calculating rolling totals?

A RUNNING_SUM calculates cumulative total from the start to current row; WINDOW_SUM sums over a specified window of rows.

B RUNNING_SUM sums only the current row; WINDOW_SUM sums all rows in the data source.

C RUNNING_SUM and WINDOW_SUM are identical and interchangeable.

D RUNNING_SUM sums over a fixed window; WINDOW_SUM sums cumulatively from the start.

💡 Hint

Think about cumulative totals versus sums over a sliding window.

RUNNING_SUM accumulates values from the first row up to the current row, while WINDOW_SUM sums values within a defined window around the current row.

🔧 Formula Fix

advanced

Identify the error in this rolling average calculation

Given this Tableau calculation for a 12-month rolling average: WINDOW_AVG(SUM([Sales]), 0, 11), what is the problem?

Tableau

WINDOW_AVG(SUM([Sales]), 0, 11)

A SUM([Sales]) cannot be used inside WINDOW_AVG.

B WINDOW_AVG requires three arguments, but only two are provided.

C The window offsets should be negative to include previous months, e.g., -11 to 0.

D The calculation will work correctly as is.

💡 Hint

Rolling averages usually include previous periods, not future ones.

The window offsets 0 to 11 sum the current and next 11 rows, which is forward-looking. For a rolling average including past 12 months, offsets should be -11 to 0.

🎯 Scenario

expert

Design a rolling 4-quarter growth rate calculation

You have quarterly sales data and want to calculate the rolling 4-quarter growth rate in Tableau. Which approach correctly calculates this?

A Use (SUM([Sales]) - LOOKUP(SUM([Sales]), -4)) / LOOKUP(SUM([Sales]), -4)

B Use WINDOW_SUM(SUM([Sales]), -3, 0) / WINDOW_SUM(SUM([Sales]), -7, -4)

C Use RUNNING_SUM(SUM([Sales])) / 4

D Use (SUM([Sales]) - LOOKUP(SUM([Sales]), 4)) / LOOKUP(SUM([Sales]), 4)

💡 Hint

Growth rate compares current 4 quarters sum to previous 4 quarters sum.

Option B calculates sum of current 4 quarters (offsets -3 to 0) and previous 4 quarters (offsets -7 to -4), then divides to get growth rate.

Practice

1. What is the main purpose of a rolling period calculation in Tableau?

easy

A. To filter data based on a fixed date range

B. To calculate values over a moving window of past data points

C. To create a static total of all data points

D. To sort data alphabetically

Solution

1. Step 1: Understand rolling period concept

   Rolling period calculations summarize data over a moving window, like last 3 months.

2. Step 2: Identify purpose in Tableau

   Tableau uses rolling calculations to track recent trends dynamically, not fixed totals or sorting.

3. Final Answer:

   To calculate values over a moving window of past data points -> Option B

4. Quick Check:

   Rolling calculation = moving window summary [OK]

Hint: Rolling means moving window over recent data points [OK]

Common Mistakes:

• Confusing rolling with fixed totals
• Thinking rolling sorts data
• Assuming rolling filters data

2. Which Tableau function is used to calculate a rolling sum over a window of data?

easy

A. RUNNING_SUM()

B. SUM()

C. WINDOW_SUM()

D. TOTAL()

Solution

1. Step 1: Identify rolling sum function

   WINDOW_SUM() calculates sum over a specified window, perfect for rolling sums.

2. Step 2: Differentiate from similar functions

   SUM() totals all data, TOTAL() sums entire partition, RUNNING_SUM() accumulates from start to current row, not a fixed window.

3. Final Answer:

   WINDOW_SUM() -> Option C

4. Quick Check:

   Rolling sum = WINDOW_SUM() [OK]

Hint: Use WINDOW_SUM() for sums over moving windows [OK]

Common Mistakes:

• Using SUM() which sums all data
• Confusing RUNNING_SUM() with rolling window
• Using TOTAL() which sums entire partition

3. Given the Tableau calculation WINDOW_AVG(SUM([Sales]), -2, 0), what does it compute?

medium

A. Average of sales for the current and previous two rows

B. Average of sales for the next two rows only

C. Sum of sales for all rows

D. Average of sales for the current row only

Solution

1. Step 1: Analyze WINDOW_AVG parameters

   WINDOW_AVG computes average over a window; -2 to 0 means from two rows before to current row.

2. Step 2: Understand SUM inside WINDOW_AVG

   SUM([Sales]) aggregates sales per row, then WINDOW_AVG averages over the window of 3 rows.

3. Final Answer:

   Average of sales for the current and previous two rows -> Option A

4. Quick Check:

   Window from -2 to 0 = current + 2 previous rows [OK]

Hint: Negative start index means look back rows [OK]

Common Mistakes:

• Thinking window looks forward only
• Confusing sum and average
• Ignoring window range parameters

4. You wrote this Tableau calculation for a 3-month rolling sum: WINDOW_SUM(SUM([Profit]), 0, 2). The results seem incorrect. What is the likely issue?

medium

A. SUM() cannot be used inside WINDOW_SUM()

B. The calculation needs to use RUNNING_SUM() instead

C. WINDOW_SUM() requires negative indices only

D. The window range is forward-looking, not backward-looking

Solution

1. Step 1: Check window range meaning

   Range 0 to 2 means current row and next two rows, which looks forward, not backward.

2. Step 2: Understand rolling sum intent

   Rolling sums usually look backward (previous periods), so range should be negative to zero, e.g., -2 to 0.

3. Final Answer:

   The window range is forward-looking, not backward-looking -> Option D

4. Quick Check:

   Rolling sum needs backward window range [OK]

Hint: Rolling sums usually use negative start index [OK]

Common Mistakes:

• Using forward window range for rolling sums
• Thinking SUM() is invalid inside WINDOW_SUM()
• Confusing RUNNING_SUM() with rolling sum

5. You want to create a 6-month rolling average of sales in Tableau, but your data has missing months. Which approach ensures accurate rolling calculations?

hard

A. Use a continuous date axis and fill missing months with zero sales before applying WINDOW_AVG

B. Apply WINDOW_AVG directly on raw data without adjustments

C. Use RUNNING_SUM instead of WINDOW_AVG to ignore missing months

D. Filter out months with missing sales before calculation

Solution

1. Step 1: Understand impact of missing months

   Missing months cause gaps, so rolling averages skip those periods, giving inaccurate results.

2. Step 2: Fill missing months with zero sales

   Using a continuous date axis and filling missing months with zero ensures the rolling window covers all months evenly.

3. Step 3: Apply WINDOW_AVG on adjusted data

   Now WINDOW_AVG calculates correctly over 6 months including zeros for missing months.

4. Final Answer:

   Use a continuous date axis and fill missing months with zero sales before applying WINDOW_AVG -> Option A

5. Quick Check:

   Fill gaps first for accurate rolling average [OK]

Hint: Fill missing dates with zeros before rolling average [OK]

Common Mistakes:

• Ignoring missing months causing wrong averages
• Using RUNNING_SUM which accumulates, not averages
• Filtering out missing months, shrinking window size