I’ve been scratching my head around this particular problem. We’re a small manufacturing plant and build campervans for various clients. We’re currently pretty much invested in Coda for planning, ordering, etc.
Now is the time to crunch some numbers!
We have a table, displayed as a timeline so all the team members can visually see which projects will start and end. I would like to build a small separate table which aggregates the end and start date and displays per week the total value of the project. In this case, when we shift projects around on the timeline it’s impact on revenue becomes immediately visible. I’ve been searching around on the forum, made enough use of chatgpt then I would like but haven’t found an answer yet.
Is this something that is possible? I don’t mind making extra tables, just as long as this proces is automated because our planning can shift quite a lot (mainly because of clients changing orders midway a project… but that’s a story for another time .
Thanks for you solution. That’s an interesting approach. However, maybe I’m missing something, but this doesn’t provide the total value per week if i’m not mistaken? This solution gives the total value in de starting week.
Say I have 2 projects with a value of € 100,- each. One that lasts from week 2 till 5 and one that’s going from week 3 till 6, any summary would provide the following:
yes this possible. My suggestion below works fine, but is not fool proof. That is to say that when the production time gets shorter, the rows with the week numbers you don’t need anymore are not removed. That would require an extra check, but is beyond the free support provided by me.
that said, here we go:
we create a button in your first table with this function inside
Here is my approach to your problem. Due to the fact that projects can be in multiple years, the week number approach is not ideal, but it can be done. Changes in table 1 are real-time reflected in table 2, you only need to update table 2 (with the button) when you go beyond the last period in table 2.
The calculation for the value of the work is not ideal, see the note in my sample doc. I would change it to a per day calculation and include only the number of (work) days that apply to the project (since projects don’t always start on Monday and end on Friday.
I didn’t thoroughly double check my doc, but it should get you on started.
Hi @Peter_Conijn , I spent a little time to craft a solution to this problem, as I see it quite regularly on other projects where reporting/summarising is needed.
The short answer, is that another table is needed to easily summarise the data in the way you want it. I created a Project Weekly Value table and added the necessary logic via a button column on the table. This allows for manual or automated updating of the summary table as needed.
I find using dates is better than week numbers for these types of reporting, so you can apply filters such as Future > Next 90 days to the table. To do this accurately, I normalised the project start and end dates. I added notes on the logic to the doc below.
.
