Cumulative sum within a summary table

Ask for Help
1

Hello all!

Cumulative sums are difficult to do in Grist. This is made even more difficult when I want to get the cumulative sum within a summary of different groups.
The possible solutions listed in this Grist Community (see Cumulative Calculation in Grist or How to create a formula to show accumulated amount per account) are great but don’t work for my needs.
The raw data look like:

Date Year Month Employee Project Hours Rate Cost
1/1/2020 Jan 2020 Joe Project X 8 $150 $1200
1/1/2020 Jan 2020 Tim Project X 8 $150 $1200
1/1/2020 Jan 2020 Xavier Project X 8 $150 $1200
1/1/2020 Jan 2020 Adrian Project X 8 $150 $1200
2/1/2020 Jan 2020 Joe Project Y 8 $150 $1200
2/1/2020 Jan 2020 Tim Project Y 8 $150 $1200
2/1/2020 Jan 2020 Xavier Project Y 8 $150 $1200
2/1/2020 Jan 2020 Adrian Project Y 8 $150 $1200

I created a summary table that displays the following (except the cumulative cost which is what I’m trying to create).

Project Year Month Cost Cumulative Cost For Each Project
Project X Jan 2020 $50,000 $50,000
Project X Feb 2020 $50,000 $100,000
Project X Mar 2020 $50,000 $150,000
Project X Apr 2020 $50,000 $200,000
Project Y Jan 2020 $25,000 $25,000
Project Y Feb 2020 $25,000 $50,000
Project Y Mar 2020 $25,000 $75,000
Project Y Apr 2020 $25,000 $100,000

So the question is, how can can the cumulative sum of a project across months be created in a summary table?

2

See the example here: Project Costs Demo - Grist

Look specifically at the formula for the column Cumulative Project Cost

rows = Project_Costs_summary_Project_Year_Month.lookupRecords(Project=$Project,sort_by='Year_Month')
total = 0
for r in rows:
  total += r.Cost
  if r.id == $id:
    break
return total

Basically, I load all the rows of the summary table for that project sorted by the Month_Year column. Then I start going through the list and add the row’s cost to the total until I get to the current row (when r.id == $id).

1 Like