Chart the progress of several projects
My customer is looking for a chart to demonstrate weekly progress across several projects. All she wants to see is the project name, percentage and the weekly date. I am struggling with a clean way to provide the top level information from a project. I tried "copy a row" with a workflow but it copies all the subtask as well. She is looking for a chart like the attached image. She made the chart in Excel to demonstrate what she wants.
Answers
-
Are you wanting to show the current week and the past two weeks automatically updating to the current week, or a set range of dates, or...?
-
She wants to see several weeks leading to eventually show a year. We have 40+ projects in play at any time, so this would provide a quick glance at any projects stalling, starting or ending.
-
Would it be a rolling year up to today? How are you currently calculating/recording your percentages?
-
It's a new request but it will be a rolling year eventually. Percentages are being manually collected from the top level of the projects. I created a sheet and started copying the row weekly on Wednesdays. The issue is it copies all the rows instead of the top row.
-
Do you track start and end dates on each project for each task?
-
No, dates are floating. We track the start date, but use a random date for the finish date to allow the project to calculate the percentage. Unfortunately I work in a dynamic environment where the dates are not visible until something happens.
-
Can you provide an example of how exactly you are currently calculating your percentage complete?
-
I'm using a Project sheet where each task accumulates to a percentage of the total project.
-
Right. And how do you calculate the percentages for each task?
-
Depends on the project, but the percentage is a default field in the project settings.
-
Hmm...
What if you create a rollup sheet where the top row of each project is linked into a master/summary sheet? Then you can use the copy row automation from there to capture the static percentages each week.
-
I tired this but since the top row has sub-task, it copies the entire project. Most of the projects have a few hundred rows so week 1 resulted in a few thousand rows but we only needed the data from a few rows. The only reasonable solution so far is to use a workflow to send the projects as an attachment so we have a running record of the percentages and populate a sheet manually. Not a good solution when running this many projects.
-
Sorry. I wasn't clear in my suggestion.
You create a completely separate sheet and use cell linking to link the top row of each project onto it's own row within this Master sheet.
So this one sheet will be nothing but cell links and each row will be linked to the top row of each project.
Since this sheet only contains that top row from each project, the Copy Row Automation won't have any additional sub-task rows to copy.
-
Pretty sure I solved it. I used a sheet for "Project Collection" and cell linked for each active project. This gave me a list of projects with the current percentage. Then created another sheet "Project Percentage Collection". The Project Collection sheet has a workflow to copy the rows weekly to Project Percentage Collection. This creates a weekly log of the percentages for each project. From here I will have a modified date, percentage and project name. Creating the chart will be the next element.
-
Yes. That's exactly what I was referring to. Sorry for not being very clear. Coffee hasn't kicked in yet apparently. Haha.
As for building the chart, I personally would create yet another "Metrics" sheet where you can reformat the data from your "Percentage Collection" sheet into something a little more easily used.
The Metrics Sheet would have one Date type column and then an additional text/number column formatted to percentages for each project.
The Date column would have each of the dates used for copying the percentages into the "Percentage Collection" sheet. One entry for each date (I personally would start this in row 2).
Then in row 1 of each project column, I would enter the name of the project as it appears on the "Percentage Collection" sheet.
Then in row 2 of the first project column you can enter something along the lines of...
=INDEX(COLLECT({Percentage Collection Sheet Percentage Column}, {Percentage Collection Sheet Project Name Column}, [First Project Column]$1, {Percentage Collection Sheet Date Column}, $[Date Column]@row), 1)
This will look at the Project Name column on the Percentage Collection sheet and compare to the Project name in row 1. Then it will look in the Percentage Collection sheet's date column and compare to the date in the Metrics sheet date column. Once it finds a row where those two data points match (specific project on specific date), it will pull from the percentage column of that row.
Because of using cell references on the Metrics sheet ([First Project Column]$1 and $[Date Column]@row) and locking in either the row or column using the $, you can now dragfill this formula down and to the right to very quickly populate the rest of the Metrics table.
Now you have a table that you can use to build a chart in a dashboard that should look pretty close to what you have in your original post.
