plotting % complete over time

Jason Lowe

I'm trying to add a graph to my dashboard that displays a project's % complete over time. Does Smartsheet have any way to automatically report that data (eg. take a snapshot of a cell value on a certain date/time each week)? I don't want to add a bunch of columns or enter data manually for this chart.

Thanks!

Paul Newcome

How do you currently calculate your % Complete?

Right off, the easiest way to do it would be to create a new sheet and use a Copy Row Automation to pull the static data regularly, but depending on your setup, you may be able to get away with just two additional columns.

Jason Lowe

I'm using the gannt chart view and using summary rows to calculate an overall % complete for a project (based on the durations and % complete of it's children). If I could extract the value from the "% complete" cell of the summary row on a regular schedule and plot that over time that would be ideal. I could manually go in once a week and pull that value and put it in excel, but I'm trying to avoid the manual part of it and use the Smartsheet dashboard.

Paul Newcome

Do you have Start and Finish Dates?

Jason Lowe

yes

Paul Newcome

You could actually use two columns then to pull a tracking table that can then be used to plot a graph on a dashboard or in a metrics widget.

It would consist of two columns. A date type column and a text/number type.

If you are wanting to show weekly (assuming Friday is the end of the week), then put each Friday into the first column.

Then in the text/number column, you could use a formula such as...

=COUNTIFS({Finish Date Column}, AND(ISDATE(@cell), @cell <= [Table Date Column]@row)) / COUNTIFS({Finish Date Column}, OR(@cell <> "", @cell = ""))

Basically we would count how many finish dates are prior to the date on that row and divide it by how many total dates there should be. Format this column to show as a percentage instead of a decimal, and that should work for you as well.

edelente

Thanks for this! Is it possible to use this line (e.g. against a "baseline complete date" ) and compare it to an actual complete date, where such data exists, as it is 'actually completed?

Paul Newcome

@edelente I'm not sure I understand your question...

edelente

sorry I was unclear. I duplicated this as a 'question' because I wasn't sure you'd see it. So I successfully used a variation of this:

=COUNTIFS({Finish Date Column}, AND(ISDATE(@cell), @cell <= [Table Date Column]@row)) / COUNTIFS({Finish Date Column}, OR(@cell <> "", @cell = ""))

To plot a line chart in a widget against my baseline that roughly shows the expected completion over the whole project. I'm asking if it's possible for me to add another column with actual completion dates, as they are entered, to plot another line that shows schedule variance against my baseline. Does that make sense?

My actual formula now is:

=COUNTIFS({DMS Project Range 3}, AND(ISDATE(@cell), @cell <= Fridays@row)) / COUNTIFS({DMS Project Range 3}, OR(@cell <> "", @cell = ""))

Does this make sense?

Paul Newcome

@edelente Yes. You would add another column to the sheet being referenced by the chart and then use the same logic to calculate the actual % Complete of dividing the total completed within the date range by the total to be completed for the duration of the project.