Resource planning logic and calculation

1

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 :slight_smile:

Thank you!

2

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.

3

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

4

@Jason_Luna :

Like this :blush: :point_down:
(just be careful to not share sensitive datas :wink: )

5
6

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.

7

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
8

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?