While I was trying to figure out WOW 2024 week 49 I ran into issues with the WINDOW_AVG function, which made it clear that I hadn't fully understood it. To get a better idea of it I am going to go into the basics of the WINDOW_ functions and how they work and then dissect my issues with the WINDOW_AVG in more detail.
What does the WINDOW part do?
The WINDOW part means that Tableau looks at your chart and defines the boundaries. That means it uses whatever is in view on your chart to do the calculations. This is controlled by the "Compute Using" setting.
If you have a chart showing 12 months, the WINDOW is now those 12 months. Without the WINDOW , Tableau wouldn't know whether it should aggregate for each year, month, etc. Tableau gathers all the aggregated values for every point in that window.
In the background Tableau creates a "list" of those values to compute further. It now aggregates (depending on the type of WINDOW function you chose) and assigns that single result back to every single mark in the window. Other functions which only give a result for each specific row it belongs to.
Why this matters for reference lines?
Because the WINDOW functions return the same values for every single point in your view it creates a flat straight line, a perfect reference line.
As an example:
AVG() = calculates the average for each point (e.g. the average price per order). This means you get individual lines for each order.
WINDOW_AVG() = calculates the average across all points (eg. the average price of all orders in view). This creates a constant value needed to draw a horizontal reference band across the entire chart.
Summary of the WINDOW "Loop"
- Looks at all the points on the screen
- Grabs the value for each point
- Calculates the average/sum/etc of those values
- Stamps the average onto every mark so they all share the same number
My Issue in the WOW 2024 week 49
During the WOW, I wanted to calculate the upper and lower bound for a reference band on a scatterplot, which should change based on what country (a circle on the scatterplot) was selected. The upper bound was +1 and the lower bound was -1 of the value of the selected country.
So I wanted a field which:
For every country, check if it matches my selection; if it does, grab its value plus or minus 1, but if it doesn't, leave it completely blank.
In Tableau this translates roughly as
IF MIN([Selected Country= Country]) THEN SUM([Value]+1) END
In Tableau, a Reference Band needs a value that exists across the entire axis to draw a continuous shape. As I explained earlier this doesn't happen without a WINDOW function.
In this case it didn't matter which WINDOW function as we wanted just one value to be assigned to each row, so I chose WINDOW_AVG (it would have also worked with WINDOW_SUM or similar).
Because I didn't fully understand what the WINDOW function did (or what I wanted it to do) I wrote my calculation as follows.
IF MIN([Selected country = country]) THEN WINDOW_AVG((SUM([Value])+1)) END
This reads as: "If this is the selected country, calculate the WINDOW_AVG. Otherwise, do nothing."
Even though WINDOW_AVG is designed to look at the whole window, I told Tableau it's only allowed to "exist" for that one specific country. This essentially meant the WINDOW_AVG did nothing.
I noticed that because it hadn't assigned the value to any of the points that were not selected.
The correct way to do it
WINDOW_AVG( IF MIN([Selected country = country]) THEN (SUM([Value])+1) END)
This reads as: "Calculate the average of only the selected country's value."
When you put the IF statement inside the WINDOW_AVG, Tableau now looks at the whole view and assigns each point the value you specify, the average of the selected country's value+1.