Dashboards Updating With Weekly Metrics

smitzel99941

Hello,

I'm trying to figure out how to have our dashboard update automatically when we work off of weekly periods. In other words, we are adding daily metrics that tally into weekly periods. Those weekly periods are collapsed and saved for historical data. Currently, I have to edit the data in the dashboard widget every week to get up to date information. Is there a way to automate the new week's information in order to not have to update the dashboard weekly?

Paul Newcome

My apologies, but even at full zoom, I am unable to clearly see your screenshots.

 

Are you able to provide more details as to how exactly you are pulling these metrics, what formulas you are using, what data is represented by the widget, etc.?

smitzel99941

Paul,

thanks for the response. I have attached a couple of better screenshots here. I’m trying to have the current period (each collapsible week is a current period) auto update to the dashboard without having to change the data every week on the dashboard. The running totals for 2019 are straightforward because of the parent/children relationship but the current period is throwing me for a loop.

Paul Newcome

Ah. Ok. The best way to achieve this would be to create another row either on the same sheet or different, and then use some basic INDEX/MATCH formulas to pull the correct data. You would then point your charts to this new row.

smitzel99941

Paul,

Thanks for answering that. How will that translate to a current period? I guess I'm not understanding how the INDEX/MATCH functions work.

Paul Newcome

The absolute easiest way would be to add a helper column we can call [Current Week] and set it as a checkbox type.

 

In this column you would use a formula that would automatically check the box if it is the current week's data. You may want to include an actual date column that you can compare against for this.

.

From here on, I will assume the row you created for the data is in row 1 of the same sheet. It is entirely up to you where it is located, but you would need to be sure to update column and row references accordingly.

.

Indenting all of the rows underneath of that will allow us to leverage hierarchy within the formulas.

.

In row 1 we would use something along the lines of this...

 

=INDEX(DESCENDANTS(), MATCH(true, DESCENDANTS($[Current Week]@row), 0))

.

Using the $ to lock in the [Current Week] column reference will allow you to dragfill across the rest of row 1. This will pull the data from each column the formula is in from the same row that is checked.

smitzel99941

Paul, thank you. I will mess around with these recommendations.

 

Paul Newcome

Happy to help! 

 

If you need any further explanation or details on any of those portions, don't hesitate to ask.

Dusty Robinson

I am trying to do something similar, but have not been able to get it to work. Here is a screen shot of my sheet. I don't need the Week Number column, but I thought it was necessary to use the Current Week function. All I want is for the top row to report the numbers from the current week.

Andrée Starå

Hi @Dusty Robinson,

Try something like this.

=SUMIFS([Total Green]:[Total Green]; Week:Week; IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()))

The same version but with the below changes for convenience.

=SUMIFS([Total Green]:[Total Green], Week:Week, IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Sheila B.

@Paul Newcome You mention above of creating a Helper Column and making it a checkbox and then adding a formula to it if it is the current week's data. How would I go about doing that? Is it possible to make it Current Day? I am trying to get a Metric Widget on my dashboard to pull Daily information.

Paul Newcome

@Sheila B. To check the box for "current day" you would use:

=IF([Date Column Name]@row = TODAY(), 1)

Sheila B.

Thank you Paul! I entered that formula but was still having issues with the other formula pulling the daily data to the top row. After studying your formula again I found my error. I now have Dashboard set up to show daily results.

Paul Newcome

Happy to help. 👍️