Week over Week Variance?
Hey Smartsheet Friends!
We are operating in a Construction Environment where we are tracking an entire build schedule, start to finish. At the beginning of the project, we set the baseline to track the original start and original finish date. This allows us to understand the length of time that we deviated from schedule overall, by trade, and by task.
We have created a weekly variance report, that allows us to see the number of days lost overall for each project (screenshot attached). However, we've been asked to report on a week over week basis. In other words, they want to be able to see last week's total variance (say, on Friday) against this week's total variance (on Friday).
We don't need to keep historicals; we just want to see the change in variance week to week. How can we do this without setting a new baseline? Our construction projects are very fluid, and sometimes last over a year. My thought is that we create some sort of automation that pulls the variance number into another sheet on a certain date - but I am not sure how this would work across 162 current projects, as projects are added and removed.
Any insight would be appreciated!
Answers
-
@PamelaAF, in the report you provide, the "Finish" date appears to be the newest estimated completion date. Presumably, next week that might be pulled in or pushed out. You are being asked to keep the previous weeks so you can show how that has moved. Correct?
Yes, you could simply have a weekly automation to send a copy of each row above into a reference archive. You would want to have a column that included today's date (use the column formula =TODAY() for reference, call the column DateRef) so you can sort the archive data using that date.
Wherever your source data is for the above report, add an extra column to lookup last week's "Finish Date", using the new DateRef column and the primary column above, which appears to be a unique ID. The formula would look something like:
=INDEX(COLLECT({Archive Finish Date}, {Archive DateRef Column}, >TODAY(-8), {Archive Primary}, Primary@row)
