1. Calculations with window functions
Before diving into the exercises on water trading activity, we will be revisiting the Tableau superstore dataset where we'll explore the concepts of window sizing, window aggregation functions, and window correlations.
Let’s imagine we’ve been asked to chart the sales for a fictional retail company, where we want to better understand sales performance over a weekly period spread over a few years.
We’ll first drag our date to the columns, change this to a week number and drag in our sales. We’ll then color code this by year.
We can see the distribution of sales, but we note that it’s quite peaky.
Let’s smooth this with a window sum function that will help us aggregate our data via a window.
We’ll look at a calculated field we prepared earlier called window_sum_sales() and understand how this works.
Let’s unpack what we’ve done here. As this is a window function, we need to aggregate our measure.
We want to specify that we’re going to use the past 3 weeks' worth of data, plus the current week, so we maintain a 4-week window size.
The 0 means we’re referencing the current row and this is always the case.
Dragging the window_sales_sum field into our rows, we can clearly see that our overall sales are much less peaky now.
Additionally, you might note it’s easier to tease out spikes in our data over the 2014 to 2017 period.
Now as a data professional, we might be curious about what’s driving these spikes - is it multiple products or a singular product?
Let’s duplicate our canvas here and create another window_sum expression using quantity.
We’ll now drag this into our rows.
Lastly, we’ll drag the Category field into our color field for all of our line chart visuals.
Focusing on the window_sales_sum and window_sales_quantity visualization, it appears that technology is responsible for some of the largest sales peaks, but from a quantity lens, this is driven by office supplies.
In other words, the technology product appears to retail at a higher margin than that of office supplies!
Now beyond our sales, we might be interested in understanding where we might have volatility in our sales. This is where we’ll introduce you to the window standard deviation function.
Let’s open up our window standard dev field we created earlier and unpack this.
Similar to the window_sum argument, we need to include the relevant aggregation for our measure, followed by the size of our window.
We’ll now duplicate our previous canvas but remove the sum of sales and our window sales sum field.
Dragging in our window standard dev field, we can clearly see there is far more volatility when compared against our window sum function.
This is important to note. Window sum is more focused on helping smooth out uneven peaks in our data, where-as the Window standard deviation helps to highlight volatility more.
Lastly, we might also be curious regarding the relationship between our sales and profits.
Can we assume just because we have high sales, we’ll have high profits?
Firstly, let’s visualize the two-time series, sales and profits at a weekly granularity in a new canvas.
While some peaks and troughs seem to correspond to each other - it’s not easy to see.
Let’s create a Window Correlation to make this breakdown a bit easier.
We’ve prepared this field earlier, where you would note we’ve aggregated our sales and profits accordingly.
Note we haven’t specified a time period, as we want to look at the entire range of values.
Before adding this into our visual, let’s drag in our region.
It’s worth calling out that as we’ve added in our region, our window calculation will be partitioned with respect to the regions.
Adding in our window correlation and we can start to see some weak linear relationships. .
We can see that the East and West regions tend to have a stronger relationship between sales and profits, as opposed to the Central and South regions that appear to have less of a linear relationship!
2. Let's practice!
Now that we’ve shown you how these window functions work - it’s over to you!