Metrics

A Rose

Hi,

I have a dashboard that keeps on updating, how do I see last weeks data (In order to compare)?

Is there a way to view metrics every week, to auto-update from a week before?

Thank you!

Paul Newcome

Ah. Ok. So in the new sheet you will want to be sure to have a system generated column that captures the created date for when the new rows are copied over. In the top row of this sheet, you would use this formula to pull the most recent row's data to the top:

=INDEX([Column Name]:[Column Name], MATCH(MAX([Created Date]:[Created Date]), [Created Date]:[Created Date], 0))

Put this in the first row of every column that you want to display the data for, and then reference this top row in your dashboard.

Paul Newcome

My suggestion would be to find a way to capture the data from the sheets themselves using a Copy Row function. Unfortunately you cannot automatically capture an entire dashboard.

A Rose

I need a formula, to capture data from a week before, in some way, so that I can add it to metrics.

Thanks

Paul Newcome

What formula are you currently using to capture your metrics from the current week?

A Rose

Below is my current formula, do I need to create a new column weekly?

or is there a formula to do that?

=COUNTIF(Column1:Column1, "Yes")

Thanks!

Paul Newcome

You could set up a separate sheet and use a Copy Row automation at the end of every week or before your dashboard updates. This would capture that static data each week.

A Rose

Oh, now I get it,

this would be pretty good, but would still require some manual work, like selecting the new cell for the new metric.

Any solution for that?

Thanks!

Paul Newcome

I'm not sure I follow. You shouldn't have to add anything once you create the new sheet along with the automation.

It will copy the data over to the new sheet. Not move it. The formula on the source sheet will still continue to run as is.

A Rose

I undertand that, let me explain,

The new sheet, will have a new row every week,

in order to view metrics on a dashboard, the cells you want to view, need to be selected,

if you select cells for this week, next week will be a new row, that will not be in the window you've preiviusely selected, unless if you select more then 1 cell, to be in the same window.

Right?

Thanks.

Paul Newcome

Are you trying to SHOW the previous week's metrics on the same dashboard, or just capture them for historical documentation?

A Rose

yes, I want to show on the same dashboard, the numbers from last week, in addition to the current numbers.

Thanks

Paul Newcome

Ah. Ok. So in the new sheet you will want to be sure to have a system generated column that captures the created date for when the new rows are copied over. In the top row of this sheet, you would use this formula to pull the most recent row's data to the top:

=INDEX([Column Name]:[Column Name], MATCH(MAX([Created Date]:[Created Date]), [Created Date]:[Created Date], 0))

Put this in the first row of every column that you want to display the data for, and then reference this top row in your dashboard.

A Rose

@Paul Newcome,

that is absolutely amazing!

Thank you!😀

A Rose

@Paul Newcome ,

Would you know why it's the link is not showing any data?

not showing data, and not showing error.

Thank you!

Paul Newcome

Do you have any rows that are blank in the column you are pulling from but has a date in the created date column?

A Rose

there are definetely blank rows, is that the reason?

Thank you!