Cumulative Calculation in Grist

Hi,
I want to build a monthly project report, where I need a column of Cumulative order of the month.
I am struggling as I don’t know python. Can you guys tell me how to do it without an monthly calculation? and with a month calculation. For example, I want to reset the cumulative number when a new month starts.

Hi there!

I created an example for you at the link below:

https://public.getgrist.com/mkYnvqwgrzwH/Community-871-Cumulative-Calculations/m/fork

There’s a few different ways to do this. If you want to see orders received at a glance, you could use summary tables. In the screenshot below, I have a summary table that shows total number of orders received and a second table that shows orders received in each month.

To make the first summary table, select the green ‘Add New’ button > Add Widget to Page > Select Widget = Table > Select Data = Cumulative_Orders > click the green ‘Summation’ icon > Add to Page.

To make the second summary table, you’ll do all the same steps as above but after you click the green ‘Summation’ icon, group by ‘Month’ and ‘Year’ then Add to Page.

In order to group by Month + Year, I added two columns to the table with the following formulas: MONTH($Date) and YEAR($Date)

Because some dates have multiple records, it’s best to create a summary table and group by Date to find the running cumulative amounts.

To make this summary table, select the green ‘Add New’ button > Add Widget to Page > Select Widget = Table > Select Data = Cumulative_Orders > click the green ‘Summation’ icon > Group by = date > Add to Page

This creates the table where I have the ‘monthly cumulative’ and ‘total cumulative’ columns. The formula used for ‘monthly cumulative’ is:

current_day = $Date.day
day = 1
previous = None
while day < current_day:
  previous = table.lookupOne(Date=$Date - datetime.timedelta(days=day))
  if previous: break
  day += 1

if not previous: return $Order_Received

previous.monthly_cumulative + $Order_Received

This will count the orders received so far that month.

The formula used in ‘total cumulative’ is:

previous = table.lookupOne(Date=$Date - datetime.timedelta(days=1))

(previous.total_cumulative or 0) + $Order_Received

This will give you a cumulative total of all orders received.

1 Like

Thank you so much. You guys are the best!