Better Trend Graphs

Darcy Gibbs

Hello,

I have several metrics I report on each week that would be best displayed by a trend line. Currently, each is in a dashboard with a bar graph showing the current totals, but a trend line would better show how projects progress.

I have created a graph for a one of my metrics based on the status of the projects. Each project has it's own Gantt sheet with all of the pertinant information to that project, and a report summarizes the total of active projects flagged 'Red', 'Yellow', or 'Green'. I can easily display the bar chart shown, but in order to display a line graph I have to manually enter these totals into a separate sheet, (shown), each week to create the line graph shown. I cannot for the life of me figure out how to automate this type of bar graph.

I have several other similar metrics I would like present trend lines on, such total types of active projects and total projects in different phase gates to show load on different departments. I thought data mesh or a sheet automation would be useful because both can be scheduled to run weekly, but those efforts have been unsuccessful.

Thanks in advance!

Paul Newcome

You would first need to create a sheet that has formulas to calculate the number of RYG on a single row. Then you would create a second sheet with an auto-number column (no special formatting) named "Week" and the RYG columns.

From there you would set up a weekly copy row automation from the sheet with the formulas to the second sheet.

Finally you would create a report that pulls rows that are not blank from the second sheet (copy recipient) and use the report to create your line graph.

Darcy Gibbs

Hey @Paul Newcome,

Thanks for your reply. My problem is that each project with a RYG status has it's own sheet and there are often many running at the same time, (in this case you can see I have 55 active projects at this time). So populating the first sheet you describe with the RYG totals would still be manual, correct? The bar chart shown above is from a report, but I don't know how to get the data from a report into a sheet without filling it out manually.

Paul Newcome

My apologies. I misunderstood what you were looking for. Do you happen to have access to the premium add-on Control Center?

Darcy Gibbs

@Paul Newcome I believe we do now as our organization just upgraded. My introduction to CC did not indicate that there may be a solution there, but I will investigate further. Any tips are appreciated.

Thanks!

Paul Newcome

Control Center will allow you to essentially roll out a template over and over again as needed. It also has the ability to create a Summary Sheet which is at the portfolio level and has basically a summary of all of your projects. Control Center creates cell links from each project's individual metrics sheet to the portfolio level summary sheet automatically. This can include the RYG counts and can be leveraged in the weekly copy row so that you have each project's totals on a running basis.

Darcy Gibbs

Thanks @Paul Newcome, I'll see if I can make that work.

Paul Newcome

Happy to help. 👍️