Creating a Moving Average with Table Calcs
Sometimes we want to display overall trends in our data more clearly, using moving (or rolling) averages. We can create flexible moving averages with table calculations in Looker, using the function offset_list(). This will return a list of values in a specified window, which we can then average.
To create a 7 day moving average, we will use the calculation:mean(offset_list(${field_being_averaged},0,7))
For every row, this will give you the average of the 7 rows below. You can change the number of rows (the last parameter of the function) to increase your window, and you can change where the window will start (the second to last parameter of the function). Positive numbers will move down, and negative numbers move up. So to get the average of the ten values above the current row, you can do:mean(offset_list(${value},-10,10))
Visualized, our moving average will look like this:
Creating a Moving Average with Trend Lines
We can also create a moving average using the trend line feature available in the visualization edit panel, in the Y axis tab.
Each point on the moving average trend line is the average value of the previous data points, and the window for the number of previous data points to include can be specified with the Period setting.
Difference Between Trend Line and Table Calc
When deciding between these two options, it is important to note that the trend line moving average includes nulls by treating them as 0, whereas the table calc moving average will ignore nulls (unless adjusted to do differently).
For example, let’s say you have results for null, 3, 3. In the table calc mean(offset_list(${field_being_averaged},0,-3)), the denominator would NOT include nulls, and the average would be 6/2 = 3.
We can adjust our table calc to match the behavior of the trend line, using something like mean(offset_list(coalesce(${field_being_averaged}, 0),0,-3)) to replace nulls with 0s. The denominator would therefore not include nulls and the average would be 6/3 = 2.
Both methods are correct ways to calculate moving averages. To decide which one to use, you will need to determine whether nulls represent 0’s in your data or whether they should be ignored. If you want them to be treated as 0’s, then you can use the trend line moving average, or adjust your table calculation to replace nulls as 0s.