0
0
Tableaubi_tool~10 mins

Why advanced analytics uncovers hidden patterns in Tableau - Formula Trace Breakdown

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

Sample sales data showing customer purchases with amounts and dates.

CellValue
A1CustomerID
B1PurchaseAmount
C1PurchaseDate
A2C001
B2120
C22024-01-05
A3C002
B380
C32024-01-06
A4C001
B4150
C42024-02-10
A5C003
B5200
C52024-02-15
A6C002
B690
C62024-03-01
Formula Trace
WINDOW_AVG(SUM([PurchaseAmount]), 0, 2)
Step 1: SUM([PurchaseAmount]) for each row
Step 2: WINDOW_AVG(SUM([PurchaseAmount]), 0, 2) at row 1
Step 3: WINDOW_AVG(SUM([PurchaseAmount]), 0, 2) at row 2
Step 4: WINDOW_AVG(SUM([PurchaseAmount]), 0, 2) at row 3
Step 5: WINDOW_AVG(SUM([PurchaseAmount]), 0, 2) at row 4
Step 6: WINDOW_AVG(SUM([PurchaseAmount]), 0, 2) at row 5
Cell Reference Map
     A           B              C
1 |CustomerID |PurchaseAmount|PurchaseDate
2 |   C001    |    120      | 2024-01-05
3 |   C002    |     80      | 2024-01-06
4 |   C001    |    150      | 2024-02-10
5 |   C003    |    200      | 2024-02-15
6 |   C002    |     90      | 2024-03-01

Arrows: WINDOW_AVG uses SUM([PurchaseAmount]) from current and next 2 rows
The formula uses PurchaseAmount values from the current row and the next two rows to calculate a moving average.
Result
     A           B              C             D
1 |CustomerID |PurchaseAmount|PurchaseDate|MovingAvg
2 |   C001    |    120      | 2024-01-05 | 116.67
3 |   C002    |     80      | 2024-01-06 | 143.33
4 |   C001    |    150      | 2024-02-10 | 146.67
5 |   C003    |    200      | 2024-02-15 | 145.00
6 |   C002    |     90      | 2024-03-01 |  90.00
The MovingAvg column shows the average purchase amount over the current and next two purchases, revealing trends and smoothing out spikes.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does the WINDOW_AVG function calculate in this example?
AThe average purchase amount for each customer
BThe average of the current and next two purchase amounts
CThe total sum of all purchase amounts
DThe maximum purchase amount in the dataset
Key Result
WINDOW_AVG calculates the average of a measure over a sliding window of rows.