Dashboard

jgneely72151

I have a metrics sheet that I send every Monday to leadership. However, I'm finding that I need to look at prior weekly metrics for comparison. Since my metrics sheet updates constantly, how would I capture that data without having to log it manually each week? I know this is a vague question. My formula knowledge is limited so I imagine a formula must be applied. I track the # and % of participants that are compliant, partially compliant and non-compliant accross 3 programs each weekly.

If possible can you provide a simple example and explanation.

I want to build a dashboard that will visually show the current week and at least 3 past weeks to check progress.

Thank you for any guidance!

Paul Newcome

You could set up a copy row automation to send the rows to another sheet on a regular basis. The data pushed by the copy row automation will be static data, so you won't have to worry about it changing when the formulas on the main metrics sheet change.

You can put a [Created Date] type column in the second sheet to have a date stamp of when the rows were copied so that you can easily filter the historical data.

Paul.Woodward

Hello, @jgneely72151 .

I do not have an exact answer but I wanted to talk it out below. I would LOVE to hear what someone else thinks or their answer

The only thing I can think of is if you have your MASTER Report, and create a sub-report off of it. The sub-report, could be sorted by dates, which would allow you to see the metrics for the selected dates. You could have the master report on the dashboard showing current metrics, and then below that you could have the sub-report displayed or metric widgets / data widgets for the selected dates?

Thoughts?

jgneely72151

Would this date field you mentioned for past report data be a modified date field? I do not track any dates on my master sheet besides participants DOB and a date recorded for when they become compliant. I'm a little lost still because if last weeks metrics was 50% compliant and this week 75%, I want to be able to capture both, except I'd like a running list of past metrics. Right now I am only capturing realtime metrics automatically and manually documenting prior weeks metrics. There has to be a better way.

Paul Newcome

You could set up a copy row automation to send the rows to another sheet on a regular basis. The data pushed by the copy row automation will be static data, so you won't have to worry about it changing when the formulas on the main metrics sheet change.

You can put a [Created Date] type column in the second sheet to have a date stamp of when the rows were copied so that you can easily filter the historical data.

jgneely72151

@Paul Newcome Now why had I not thought of this? Duh…….I only use the copy to sheet for archival purposes but I can so see how this can give me the historical data I need on my dashboard without it updating. Goodness! It is an excellent idea and I’ll implement it as soon as I return from our winter break. This is going to be a game changer for sure. OMG thank you!!!!!

Paul Newcome

Happy to help. 👍️