Weekly hourly forecast by task and assignee

I’m new and coming from gSheets. In gSheets I have a resourcing forecaster that has weeks of the year as column headers along with a task description and assignee (think, hours per week across the year for assignee working on X).

I started in Coda by creating one doc within which I have a table for each project, following similar columns (Task | Resource Type | Assignee | Week 1 | week 2 | … | Week 53). When I tried to insert a column summarizing total hours for all weeks for that row, the sum() function become unwieldly.

I’ve read about the power of Coda, but I’m definitely having to change how I think about this stuff.

What’s the right way to get summary views of my tasks and projects, and aggregate hours by week per assignee (i.e. not booking 90h/w against a single person spread across multiple projects)?

1 Like

Dear @Patrick_Mazzotta, welcome to the community :handshake:

There are some differences between Gsheets (Excel) and Coda explained here

I recommend to have a look at the templates section, copy the one that looks interesting and start to look “under the hood” to see how its build up. This is a good example

You will understand that the initial structure is different and that you will get tables for projects, tasks, resources, assignee’s, weeks and with “lookup’s” connecting information, that can be filtered to meet the criteria for your business needs.

The YouTube channel is another good source to start from

Thanks for the response Jean Pierre,

I have read and gone through these examples. I think there’s a fundamental leap I still need to make which is specific to the level of detail on large sets of data. Running a table with columns representing every week of the year (so >52 columns) feels unweildy to work with in Coda when it comes to the name-based functionality (that “what” reference vs “where” reference feels like it breaks down for larger tables).

The task management examples (which I’ve looked at) all seem to focus on a) describe the task and b) set a delivery date. Resource management (hours per week per person) appears to be too complex for the Coda interface.

I assume I’m wrong here and that it’s a matter of thinking of the problem differently. Really looking for cognitive advice as I have been reading these posts, playing with a prototype, and watching the videos before posting.

Dear @Patrick_Mazzotta,

Just a quick basic sample how I suggest to build, hopeful it gives the right inspiration.
Of course you could create a table with the weeks and select the week from there, but then you need also to mention the year, as I see it!

See also the weekly overview page if this "brings" you what you are looking for :thinking:

Thanks Jean Pierre!

This validates something I was thinking through: that I needed to break down and simplify the tables. I think, being a bit of a nerdy engineering type I was trying too hard to make tables work like DBs instead of just tables.

I think where I landed (which is what your example helps to validate) is that I need to break down my original table into smaller tables that each do less, but add up / combine fast.I’ve done something like what you’ve suggested.

I tweaked your example and added in a chart showing how I’m trying to think of the planning. Note that I roll up hours by person/week (aggregating across all tasks). This gives me a forecast of how much work Ben vs Iva has weekly, to load-balance future work that I sell in (by the hour). I don’t have permissions to adjust the settings on the chart, but I think you get the point.

Now I just need to figure out how to make a nicer form so the inputs for 20 week projects aren’t too tedious.

REALLY appreciate your help here and giving me this new spin.

Thanks!

1 Like

Hi Patrick,

I’m working right now on that exact same problem :slight_smile: And I’m a Coda enthusiast, so I’m going to talk to you about the way of the thinking and the rationale, rather than the technical solution. So, IMHO, Coda has, basically, no limits when it gets to the model. And DB, rather than tables, is actually the proper way to think.

Now limitations comes mostly from the display (and then later with the interactions). In your example, you want to display weeks as columns. But before jumping into how doing this easily, you have to open a bit more your mind and stick to the “problem space” a bit longer, rather than jumping into the “solution space” too soon. So … you want to display the workload of your talents, and you need to see this week by week. But maybe weeks won’t de displayed as columns, and if it still solves the problem, that would be perfectly acceptable, right? Going back to DB. Would you add a column for each week in your DB? I think you wouldn’t, you would create a second table with weeks as lines, and cross-reference talents loads with week numbers. That would be the exact same way you would go in Coda.

And then, back to the solution space. Now that the model is defined in a very clean and neat way (just as a DB), you’re going to work on displaying this in a fashion you fancy. This is where things get a bit more tricky, because now you need to know exactly what Coda can and can’t do. And that takes a bit of time. For your specific example, there are 2 tricks you want to know:

  • you will use many different views of the same table, and add columns to these views dedicated to displaying this view correctly rather than enhancing your model. Most of theses columns will contain formulas and will be hidden.
  • you will “group by” with a “top” display, to mimick columns

And so, with these 2 tricks, you can create a view on your big table, and reach the solution you’re looking for. Or another one, because most of the time, you tend landing in another place, as good or even better as the one you were aiming for initially.

Hope this helps :wink:

-Laurent

2 Likes

Laurent, thanks!
I think you hit the nail on the head: I was trying to rush the output vs. being more holistic in the design phase.

I’m chugging away now, and think I’ve got a bead on the right solution. I want to really nail this as a prove-point for Coda’s adoption in my company.

Looks like data entry will be a problem though. I couldn’t see a way to create forms that feed into tables. How do you handle adding new entries? I’d love to add some text fields with a button that triggers table inserts

Dear @Patrick_Mazzotta,

I have just added a button to add a task in the file shared above.
You are able to adjust this form according your needs and even hide fields

In case you want to get external input, I recommend to check out the “Typeform pack”

Concerning your longer term projects, have you considered to add quarterly column (13 weeks), so 20 weeks become more or less 1,5 quarter.

The charts aren’t yet as rich as in Excel, but basic things are possible to implement with some creativity :thinking:

1 Like

Haha @Patrick_Mazzotta , you need to get to the videos and spend some time on the youtube channel :slight_smile: Entry is actually a breeze, there’s everything you need built-in and waiting for you!

Good luck with the learning curve :sweat_smile: