Calculating Moving Averages

[quinn_morrison]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

Starting in Looker 21.12, you can use Quick Calculations to perform common calculations on numeric fields that are in an Explore's data table without using Looker functions and operators when the Custom Fields Labs feature is enabled, and you have permission to use them.

 

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
 

New in Looker 21.6, trend lines can be added to Cartesian charts without enabling a Labs feature. Improvements include power and polynomial regression trend types with degree selection; extendability across a full set of data (null values no longer cause breaks or trend lines to stop early); and greater compatibility with grid layout (by pivot) ‐ each pivoted chart will have a unique trend line.

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.
 

Ignoring or Using Nulls in Table Calculations

It's important to note that both trend lines and the table calculation moving average ignore nulls. However, you can adjust the table calculation to treat nulls 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 set nulls to 0s, using something like mean(offset_list(coalesce(${field_being_averaged}, 0),0,3)) to replace nulls with 0s. The average would then 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 0s in your data or whether they should be ignored. If you want them to be treated as 0s, then you can adjust your table calculation to replace nulls as 0s.