Resource planning logic and calculation

Hello Coda Community,

I have an issue I’ve been trying to figure out for too long.

I’m trying to do some resource planning using a simple calculation - we were using Excel, but I can’t for the life of me figure it out how to do it in Coda.

Basically here’s what I have so far:

I have a table, in that table I have rows as “projects” in the projects, I have three columns for efforts for each of our three teams.

Those efforts are large, medium, small and micro - and for the sake of this example, let’s say they equal 4,3,2,1 in numerical value respectively.

Each project has an efforts assigned to it for each of our three teams - so on any given project, effort might look like: Team A has effort of (large) 4, Team B - (small) 2 and Team C - (micro) 1

So, looking at any given quarter, we have multiple projects within that quarter:

For example:

Q1 has 6 projects
Each project has as above, 3 effort levels as mentioned above.

What I want to do:

Count the number of large, medium, small and micro projects in any given quarter - sum up the values for each team’s efforts

(e.g.) Each team sees the 6 projects differently when it comes to effort - Team A has 4 “large” projects, 2 “small” projects and Team B has 1 “large”, 5 “medium”

So, in my resource table which is separate from the projects table, I have teams represented for each row, and as columns, I have quarters (1 through 4 for 2021)

I want to see under the Q1 column for Team A a total sum of “effort” adding up all the values from above and giving me a whole number - so the outcome using the example above would be for Team A a total amount of effort for the quarter is 20 (4 large = 16, and 2 small = 4)

Then I will use conditional formatting to tell me if that 20 number is more or less than the max capacity for that team for the quarter.

Hope this makes sense

Thank you!

Hey @Jason_Luna for this type of thing I think creating an example doc for the community would make it easier.

I think you can accomplish this by adding another column to your projects table, [Quarter], a date value.

Then you can find all these derivative metrics with formulas like:

`projects.filter(quarter="01/01/2021").count()`

`projects.filter(quarter="01/01/2021").[team A effort].sum()`

You could also factor this with a Team table, or a Quarters table, where you could have summary/count columns as well. Hope this makes sense! Good luck.

Thank you! - How do I share an example document?

Like this
(just be careful to not share sensitive datas )

Now, the trouble I’m having is getting the count from the projects table for each project of any given size, the Team Resourcing table is the end result I’m looking for.

Hello @Jason_Luna!
I suggest a change in schema, Coda works best when you structure your data following some principles of database design. Here I leave a redesign of your file that gives the result you are looking for.
Feel free to ask if you have any questions

2 Likes

I think you would be served in `Test Example` table to eliminate the `Team A Effort` & `Team B Effort`, etc columns and have more rows where you have a `Team` column and a `Effort` column. This will create more rows but allow you to do formulas and filters I suggested above. Does this make sense?