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!
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)
return previous.Order_Received
We find the index of the current record’s ID then find the value to the left of this ID in the list of Cumulative IDs. This is our prior record. We then pull the Order Received value for the prior record.
You can hide the helper table from the document. Click the three-dot icon at the upper right of the helper table then ‘Delete Table’. It will be hidden from view but still is accessible under Raw Data views.
Let me know if you have any other questions!