 # 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

``````

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!

Hello I used this but it seems if I dont have a “sale” every day then it starts the accumulation over. Is there a way for it to look at the last record instead of just one day back?

Hi Jordan!

You can do this using a helper table. I modified the original example here:

https://public.getgrist.com/6eECEP7S2P1c/Community-871-Cumulative-Calculations-last-record/m/fork

We are going to use the unique ID that is assigned to each record when the record is created. If you have deleted any records or re-arranged columns, the IDs won’t be in order. We use a helper table to make a list of IDs in the order they appear in the table.

The formula used in the Cumulative IDs column of the Sort table is:

``````Cumulative_Orders.lookupRecords(sort_by='manualSort').id
``````

You’ll see that the IDs are listed in the same order that the appear in the table.

Next, we’ll need to update the formula that was initially used to find the previous day’s orders. Now, we’ll just find the last record’s value for Order Received.

The formula in the ‘Last Record OR’ is:

``````order = Sort.lookupOne().Cumulative_IDs
index = order.index(\$id)
previousId = order[index-1] if index > 0 else 0
previous = Cumulative_Orders.lookupOne(id=previousId)