How to Create a running average/sum/count for a specific interval using table calculations? (Community)

View the original community article here

Last tested: Apr 4, 2020
 

If a user wants the trailing average/sum/count for the last n days, usually this can be done with a subquery, but you can also visualize it using table calculations.

Underlying Data:


 

First we create a table calculation using the modulus function to get the correct date interval. We use the row value and divide it by the interval we want. If the remainder is 0, we show yes, if it is not, we show no.

Then, we use the offset_list function to grab the values from the preceding 7 days. For more information on this function visit this article.

**You will need to adjust the offset list based on whether or not the dates are ascending or descending.

Then, we hide the no's from the first table calculation and our results are: