Dashboards Updating With Weekly Metrics
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?
Comments
-
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.?
-
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.
-
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.
-
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.
-
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.
-
Paul, thank you. I will mess around with these recommendations.
-
Happy to help!
If you need any further explanation or details on any of those portions, don't hesitate to ask.
-
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.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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.
-
@Sheila B. To check the box for "current day" you would use:
=IF([Date Column Name]@row = TODAY(), 1)
-
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.
-
Happy to help. 👍️
