0
0
Tableaubi_tool~10 mins

Why time analysis reveals trends in Tableau - Formula Trace Breakdown

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

Monthly sales data from January to May 2024 showing sales values.

CellValue
A1Date
B1Sales
A22024-01-01
B2100
A32024-02-01
B3150
A42024-03-01
B4130
A52024-04-01
B5170
A62024-05-01
B6160
Formula Trace
WINDOW_AVG(SUM([Sales]), -2, 0)
Step 1: SUM([Sales]) at each date
Step 2: WINDOW_AVG(SUM([Sales]), -2, 0) at 2024-03-01
Step 3: WINDOW_AVG(SUM([Sales]), -2, 0) at 2024-04-01
Step 4: WINDOW_AVG(SUM([Sales]), -2, 0) at 2024-05-01
Cell Reference Map
    A          B
1 | Date     | Sales |
2 |2024-01-01|  100  |
3 |2024-02-01|  150  |
4 |2024-03-01|  130  |
5 |2024-04-01|  170  |
6 |2024-05-01|  160  |

Arrows: WINDOW_AVG uses SUM([Sales]) from current and previous 2 rows.
The formula looks at the Sales column values for the current month and two months before to calculate the average.
Result
    A          B          C
1 | Date     | Sales |  3-Month Avg |
2 |2024-01-01|  100  |      -       |
3 |2024-02-01|  150  |      -       |
4 |2024-03-01|  130  |   126.67     |
5 |2024-04-01|  170  |    150       |
6 |2024-05-01|  160  |   153.33     |
The 3-month moving average smooths sales data to reveal underlying trends by averaging current and previous two months.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does the WINDOW_AVG function calculate at 2024-04-01?
ASum of sales for Apr only
BAverage of sales for Feb, Mar, and Apr
CAverage of sales for Jan, Feb, and Mar
DSum of sales for Jan to Apr
Key Result
WINDOW_AVG calculates the average of a measure over a sliding window of rows defined by offsets.