Sum of previous n records

Ask for Help
1

This may have been asked before but I can’t find such a topic.

I have a table with columns Year (integer), month (integer), value (number). The data has 48 rows (i.e. four years worth of data) and will add 1 row per month (obviously)

I’d like fourth column with the previous 12 months total to show trends in the data - I’ve tried and failed to find a ‘pythonic’ solution to this that can work in a generic way such that I can change the 12 moths rolling total to, say, quarterly or half-yearly.

I’m sure some of you will have solved this problem, or something similar so any pointers would be most welcome.

M

2

Does this example work for you? It adds one column to aggregate the last N rows and a second column to add them up.

https://docs.getgrist.com/xoWve49bdNEY/Recurrance-Ordered-by-data/p/2

3

@JordiGH

Thanks - looks like that method will get me the solution I need. Thanks so much for chiming in

4

This is the formula I used - not the cleanest Python code, but it works!

ThisMonth=$Month
ThisYear=$Year
total = 0
for _ in range(12):
  row=Periods.lookupOne(Year=ThisYear,Month=ThisMonth)
  total += row.Distance
  ThisMonth=ThisMonth-1
  if ThisMonth==0:
    ThisMonth=12
    ThisYear=ThisYear-1
return total
5

Check out also the recently added PREVIOUS function.

E.g. it allows you to write a formula like this, for a column Rolling_Sum of type Numeric, which would give a rolling sum within a month:

prev = PREVIOUS(rec, group_by=("Year", "Month"), order_by="Date")
return prev.Rolling_Sum + $Distance

(Just be sure to set the column type to Numeric, so that it defaults to 0 for an empty record rather than None. Alternatively, replace prev.Rolling_Sum with (prev.Rolling_Sum or 0).)

For a rolling sum, in a table of months, you could use the related RANK function with a lookup to find a record that’s N steps back in order. Here’s an example to get a rolling 6-month sum:

Here, the Rank column is:

RANK(rec, order_by="Month")

And Rolling 6-months column:

prev = PREVIOUS(rec, order_by="Month")

old_rank = $Rank - 6
if old_rank > 0:
  old_amount = Activity_summary_Month.lookupOne(Rank=old_rank).Amount
else:
  old_amount = 0
  
prev.Rolling_6_months + $Amount - old_amount

For “average”, I would add a separate column to divide the sum by the number of months (6 in this example).

6

@dmitry-grist - I saw those functions but couldn’t get my head around how I’d use them so went with the vanilla python. Your solutions look really elegant and I’ll use them going forward.

Thanks