How do I Chart Monthly Summaries from a Metric Sheet into a Dashboard

VinceA

Hello Community,

I am having a grand 'ol time trying to display onto a dashboard, Monthly Summaries of certain metrics from our packaging lines. Was looking for both most recent month look back and last 3 months as well. I have a host of monthly data sets on a Metric Sheet both as Summaries and Averages and I didn't want to just display as a Text Widget but as a Chart of sorts, from the Sheet.

I'm not well versed on Sheet Summaries if that is even possible for this. I did review some of the tutorials but it didn't help me much.

The issue with using my "30 Day" Reports that I do have, is that I don't know how or if it's possible to pull these particular Summaries from the TOTALS row that sits at the top of Reports when you generate Summaries and even so, I would only best get a filtered result like "past 30 days" or "past 90 days" for all of the packaging lines not Summarized, which isn't what I am looking for. Anyway, here my Metrics Sheet screenshot. I can add screenshots if need be and thank you.

Vince

SoS | Dan Palenchar

Hello @VinceA

To clarify, you want to chart the Sum of Hours for UT on All Lines for the last 4 calendar months?

If so, this is doable somehow. I'm not completely sure the best way since I am not sure how you are adding new months into your Sheet. I see there are cell links for the numbers but the months appear to be text values so are you adding it new columns periodically, typing in the month, then linking the Data?

For the chart to be useful I suspect you will want it to always be pulling in the most recent data. So I would suggest a different Sheet that has some formulas in place to automatically show the last 4 months, something like this:

This works as follows:

1. Row 1 of Date displays the 1st day of the current month. Each subsequent row displays one month before rolling backwards.
2. Month has a formula that looks at Date and returns the appropriate month.
3. Sum of Hours for UT on All Lines would be a lookup formula or cell link to the Sheet you shared that pulls in the value for the month. With your Sheet structure this would likely require some modification to your Sheet so a lookup formula could be used (would need 1 column for each month so that an identifier could be added for the formula) or if you could use formulas to place the last 4 months in the same columns at all times you could cell link to them.

You could modify the Date formula so that row 1 starts with the previous month. When I first read your message I thought you wanted to start with the current month so I wrote it that way.

As built, to chart the last 4 months only you could make a report and filter out row 1 then chart that or chart rows 2-5 directly from the Sheet. I used the latter option and it looks like this after a few quick tweaks to the Chart options :

The formulas I am using are:

1. In row 1 of Date: =DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
2. In all other rows of Date: =IFERROR(DATE(YEAR([Date Column]1), MONTH([Date Column]1) - 1, 1), DATE(YEAR([Date Column]1) - 1, MONTH([Date Column]1) + 11, 1))
3. In Month: =IF(MONTH(Date@row) = 1, "January", IF(MONTH(Date@row) = 2, "February", IF(MONTH(Date@row) = 3, "March", IF(MONTH(Date@row) = 4, "April", IF(MONTH(Date@row) = 5, "May", IF(MONTH(Date@row) = 6, "June", IF(MONTH(Date@row) = 7, "July", IF(MONTH(Date@row) = 8, "August", IF(MONTH(Date@row) = 9, "September", IF(MONTH(Date@row) = 10, "October", IF(MONTH(Date@row) = 11, "November", IF(MONTH(Date@row) = 12, "December"))))))))))))

__________________________________________________________________________________________

👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

PS - If you have a follow up response attention use @Dan Palenchar so I get notified of your reply!

VinceA

Hello Dan,

thanks much for your input. The day of your response I'd actually found how to implement what I was looking to construct and so I wasn't and hadn't looked at your solution although I believe I understand to a degree how you went about it.

My information gets filtered from another larger Archived sheet for which I do have a column with a Formula that looks at the date when a Job was scheduled and retrieves the proper Month for it in that separate column. The only thing I'm needing to contend with will be to manually choose the rolling months for all of my Charts which will be a nuisance but I'm ok with it for now. Not a major priority to get that automated at this moment but will look to ask again in near future.

Here's a rendering of it with some #'s I just threw in to get it going.

VinceA

Thanks Dan for the reply and effort.

I'd already found a solution shortly after I'd posted and hadn't had the opportunity to get back to you with a response. I kind of see what you did but don't know that at the moment I will implement it as a change for now. The only drawback with my solution is that it isn't automated and so I will need to make small changes near the end of the month simply to reflect the most recent of the 3 Month look-back, meaning September becomes October etc. No big deal as it shouldn't take me long at all. To anser 1 of your questions, I pull this info from a very large Archive sheet and I have a MONTHLY column with a Formula that looks to a date to then recognize which MONTH that job started.

Anyway, here's what the dashboard looks like with some #'s I just threw in for the time being.

VinceA

Sorry for the 2 replies as I thought I'd lost the original reply and rewrote it again not realizing that the first actually ended up posting somehow.

Vince