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

Knowledge Drop

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:
Screen Shot 2020-04-04 at 11.29.51 AM.png

 

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.

Screen Shot 2020-04-04 at 11.20.27 AM.png

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.

Screen Shot 2020-04-04 at 11.28.46 AM.png


Then, we hide the no's from the first table calculation and our results are:
Screen Shot 2020-04-04 at 11.33.27 AM.png

This content is subject to limited support.                

Comments
mariias
New Member

Could you please update the article? That’s exactly something I need, but I cannot load the pictures.

Version history
Last update:
‎07-07-2021 01:15 PM
Updated by: