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.