Why might my moving average table calc plot differently than the moving average trend line?

[LookerExperts]

Knowledge Drop

Last tested: May 18, 2020

The trend line moving average includes nulls by treating them as 0, whereas (unless baked into your table calc) the table calc moving average will ignore nulls.

For instance, suppose I had results for null, 3, 3. If I use something like mean(offset_list(${value},0,-3)), the denominator would NOT include nulls and the average would be 6/2 = 3.

Conversely, if I use something like mean(offset_list(coalesce(${value},0),0,-3)) to replace nulls with 0s, the denominator would include nulls and the average would be 6/3 = 2.

NOTE: Both methods are correct ways to calculate moving averages. To decide which one to use, you'll want 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. If you don't want them to be considered in the calculation, then you can use the table calculation that was suggested in this Community article.

If there are no null values, check the ordering of the date.

This content is subject to limited support.