How to create Table with formulas summarizing Metrics from Project Plan Table

1

Hi, I have a Project Plan with Phases and Tasks. I’m using Phase as column and grouping them to visualize it on the project plan. I want to provide a summary table of the Phases, showing the overall Start and End Dates for the Phases (min/max date of Tasks within that Phase) and % task completion (# tasks in thisrow.Phase where status=done/ #tasks in thisrow.Phase). How can I set up the formulas to do so? I’m trying to reference the detailed Project Plan table but the formulas aren’t working. Below are the formulas I have. I’m not sure if it’s because I haven’t set up the Project Plan table correctly or if it’s my formulas. Newbie to Coda! Love the tool, just trying to learn everything that it can do.

Start Date of Phase: Filter([Test II/IM Table].Phase=thisRow.Phase, Min([Test II/IM Table].[Start Date]))

Task Completion %: [Test II/IM Table].Task.CountIf([Test II/IM Table].Phase.Contains(thisRow.Phase))

2

Hi Alison,

It would be much easier to help you if you shared a dummy doc

Guide: How to share your doc with the Coda Community - Showcase / Tips and Hacks - Coda Maker Community

3

Hi Pablo, Here is a dummy doc of the current setup: https://coda.io/d/_diU7KnP9Xtu/Dummy-Project-Plan_sunCClQN

1 Like
4

Hi, @Alison_Shen-Ichikawa!
Here is my solution: Dummy Project Plan (FIX)

Now let me explain the changes:

  1. I renamed the tables for easier understanding of the doc. They are now DB Phases and DB Tasks.

  2. On DB Tasks, I changed the type of the column “Phase” from a select list to a relation type, and connected it to the DB Phases, and created the linked column on the DB Phases too, called “Related Tasks“. Now both tables are connected.

  3. I fixed the formula of the column called “Task Completion %”.

    image
    image386×290 11.8 KB

  4. I fixed both formulas of “Start Date” and “End Date”.

    image
    image477×117 4.5 KB

    image
    image475×122 4.28 KB

Final Table Result:

image
image1008×369 34.1 KB

If you have any questions, feel free to ask! :wink:

1 Like
5

thank you so much, this was so helpful! New to Coda here so I have a lot to learn to take be able to take advantage of all of Coda’s functionality!