Weekly progress report - project related

Finops C'ship

Hi ,

Need a help of team. complex one :-

I have Project Plan sheet for following data

below screenshot 1

I want to create a report based on status and weekly progress % Complete as shown in Screen 2

to create a dashboard chart as shown in 3 to show it in dashboard..

Can you help me on it. Also, this weekly progress report should not stick on below dates..it should keep on changing next month picking up the Sunday Date for all four week in a month.

Genevieve P.

Hi @Finops C'ship

If I'm understanding you correctly, on each Sunday of the week you want to gather static information across all your projects/rows to identify how many tasks are in each status (irregardless of the date of the tasks). You want this number to stay static for the week that you gathered, but then the next week identify the next numbers, so that you can track progress. Is that correct?

If so, the way I would do this is to have two helper sheets. One would be to create the calculations each week, and then you would copy this data to a second helper sheet which is the source for your chart. This way the copied data is static and won't update as the formulas update each week with new numbers

First Helper Sheet: Formulas

The first Helper Sheet would have 3 Status Columns to gather the numbers.

Each column would have a COUNTIF formula looking at your source sheet, counting how many tasks are in each status. (Note: you could build this as 3 helper columns in your current sheet if you didn't want to create extra sheets - let me know if you'd prefer to go this route and I'll explain further).

Then you would have a Workflow set up that Every Sunday the row is COPIED to the second helper sheet.

Second Helper Sheet: Date Workflow

Then in your Second Sheet, set a Record a Date workflow in a new column that will record the Sunday Date every time a new row is created, or copied over from this other workflow.

(My Date Column is named "Sunday")

In my tester sheet I triggered this on a Friday, so the date is a Friday, but in your workflow it would be a Sunday:

Then as this changes, you should see an update each week as the source sheet continues to update with new status counts:

You can use this as the source of your Chart if you want to select your range:

Or you could create a Report from this second helper sheet to pull in non-blank rows and have the Report as your source. This means that the chart will automatically update without you needing to re-select a new range as your month goes on.

Is this what you were looking to do? Please let me know if I've misunderstood what your "2" represented!

Cheers,

Genevieve

Finops C'ship

Hi @Genevieve P

Hope you are doing well

I tried your way but nothing is reflecting on the 2nd Helper sheet

Finops C'ship

Hi @Genevieve P

Does this keep on updating below rows every week or you manually added the value for 16 may and 23 May

Genevieve P.

Hi @Finops C'ship

The second sheet will auto-populate each week, with the row information and the Sunday Date.

However in order to do that, you need to set up two workflows.

1. The First is a Copy Row workflow created in the sheet with your formulas to COPY the data once a week.
2. The Second is a Record a Date workflow in this second helper sheet (where the rows are copied to) to record the Sunday Date in the Date column.

Process:

• Formula in Sheet 1 calculates numbers
• Copy Row workflow (in Sheet 1) runs once a week to copy the data from Sheet 1 to Sheet 2
• Record a Date workflow (in Sheet 2) runs when a new row is added to auto-populate that date.
• Chart Widget is based off of Sheet 2's data.

Does that make sense? Let me know where you're getting stuck and I'd be happy to provide more screen captures explaining one of the points above!

Cheers,

Genevieve

Finops C'ship

Hi Genevieve,

Hope you are doing well!

One question I have here is does the update just count the weekly progress or we can have something that when it run on every Sunday it should give us the total count progress ...

let me know if you would like to view the screen capture .

Thanks

Bunny

Genevieve P.

Hi @Finops C'ship

The totals in the First Helper Sheet with the Formulas determines the data that you are counting. You can build this out however you would like, so yes, it's possible to create a total count progress.

The process I described above is so that each week, as the total count changes, you have a historical record of what the formula said last week, since this number will change over time.

What is the formula you are currently using?

Finops C'ship

Hi @Genevieve P

I am using the formula =COUNTIF({Project Plan}, "Complete")

I am looking for every sunday when it run it should give us the total count not just weekly count

Thanks

Genevieve P.

Hi @Finops C'ship

That's the correct formula to use! That will find the total projects that are "Complete", regardless of date.

Formulas will update as the source updates, so as soon as another row says "Complete" the number in the formula will change.

My suggestion was to set up a Copy Row workflow once a week so you can copy over that number in order to see the historical data of total counts. Otherwise this one formula will continue to change and update as your sheet updates, and you won't have a record of the previous numbers. Does that make sense?