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 following calculation:
mean(offset_list(${field_being_averaged},0,7))

offset_list takes three arguments: the column from which you want to grab the offset values, how far from the current row you want to start the offset, and how long you want the offset list to be. You can change the number of rows (the last argument of the function, or 7 in this case) to increase your window, and you can change where the window will start (the second to last argument of the function, or 0). Positive numbers will move down, and negative numbers will 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. 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 using a trend line or a table calculation, it is important to note that the trend line moving average includes nulls by treating them as 0, whereas the table calculation 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 0s, then you can use the trend line moving average, or adjust your table calculation to replace nulls as 0s.