Knowledge Drop

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

  • 7 July 2021
  • 1 reply
  • 59 views

Userlevel 4
Badge

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.                

 

 


1 reply

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

Reply