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.
I created an example for you at the link below:
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:
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.
Thank you so much. You guys are the best!