Question about displaying current month data on dashboards
Hello,
We have a sheet tracking monthly metrics and I would like to find a way to flow only current metrics onto a dashboard. My goal is to have the dashboard be a snapshot of the prior month metrics. So if I were viewing the dashboard in June, I'd see metrics for May for several different data points.
I'm thinking maybe the only way to do this would be to do a report and only bring in the prior month's column? And then each month have to go in and update which month that is?
Or is there some other way I could accomplish this goal?
If it is relevant, the sheet is set up with each metric as a row and the columns are the months.
TIA for your help!
Jackie
Best Answer
-
Hey Jackie
I've tried to break this down into 3-4 steps, which makes this appear longer and perhaps more complicated than it actually is. If I didn't explain something well, please don't hesitate to ask me to clarify.
The formulas may look intimidating, but they really are not. The long formula uses a nested IF/AND statement so we can take into account both the month and the year since, year after year, you will want to only show the correct month of the correct year. I use the RIGHT function to look for the last 4 characters (from the right) of your Month@row cell. This assumes you will always write the year last and use 4 characters. If this isn't true, let me know and I'll replace it with a different function.
To make this approach work, you will need to add a helper column - format this helper column as a checkbox column, not a Text/number column. The formula will go into this checkbox helper column. I continued to call this helper as Completed Month, as it is set up to look at last Month's data. You can call it anything you like but if you change the name you'll need to change the name also in the summary sheet formula. After you add the helper checkbox column, you should be able to copy paste the formula directly into your sheet. Here's what it will look like.
Here's the formula to copy paste in the checkbox helper column:
=IF(AND(MONTH(TODAY()) = 1, CONTAINS("December", Month@row), YEAR(TODAY()) - 1 = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 2, CONTAINS("January", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 3, CONTAINS("February", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 4, CONTAINS("March", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 5, CONTAINS("April", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 6, CONTAINS("May", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 7, CONTAINS("June", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 8, CONTAINS("July", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 9, CONTAINS("August", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 10, CONTAINS("September", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 11, CONTAINS("October", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 12, CONTAINS("November", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1))))))))))))
Since you want to eventually use this data in a dashboard widget, we need to provide a single reference that the widget can connect to. We do this by adding a Sheet summary field (we're still in your spreadsheet grid). If you haven't used this before, you can find the sheet summary icon on your right menu of your grid sheet.
After clicking the icon, press the New Field button. You can name the sheet summary field anything you like. I called it Top Page Metric.
Copy Paste the formula below into the Sheet summary field
=JOIN(COLLECT([Top Page]:[Top Page], [Completed Month]:[Completed Month], 1, Month:Month, <>""))
This formula collects cells from the the [Top Page] column where, in the [Completed Month] column the cell equals 1 and, in the Month column, it is not an empty cell. After entering the formula, you should see the correct result appear in this field.
Finally, when you add the Metric Widget on your dashboard, you need to select the Sheet Summary data as you select the source sheet so you can access the field you created.
After this selection, everything else is the same as usual with the metric widget.
cheers,
Kelly
PS. If interested, below are info links to the functions I used:
Answers
-
Hello @Jackie Callahan
There are multiple ways to solve your question. Here is one quick solution doing a similar operation. In this example the data is collected in a monthly scorecard sheet- a column each for January through December. The additional column "Completed Month score" automatically reflects last month's score using a straightforward nested IF. The data from this Completed Month Score is shown on a dashboard using both a report and metric widgets depending what is needed with the data
The formula in the Completed Month score is not pretty but adequate.
=IF(Metrics@row <> "", IF(MONTH(TODAY()) = 2, January@row, IF(MONTH(TODAY()) = 3, February@row, IF(MONTH(TODAY()) = 4, March@row, IF(MONTH(TODAY()) = 5, April@row, IF(MONTH(TODAY()) = 6, May@row, IF(MONTH(TODAY()) = 7, June@row, IF(MONTH(TODAY()) = 8, July@row, IF(MONTH(TODAY()) = 9, August@row, IF(MONTH(TODAY()) = 10, September@row, IF(MONTH(TODAY()) = 11, October@row, IF(MONTH(TODAY()) = 12, November@row, IF(MONTH(TODAY()) = 1, December@row)))))))))))))
cheers,
-
@KDM Fantastic, thank you so much! I'm fairly new to this type of formula though. Can you help me identify what in that formula needs to be edited by me? Just straight copying and pasting gave me an #UNPARSEABLE. I tried updating the word Metrics in your formula to the row name and that didn't help. What needs to be done to it to make it apply to my sheet?
Thank you so much!
Jackie
-
Hey @Jackie Callahan
Happy to help. The column names in my formula (ex: Metrics@row) need to be replaced by the actual column names in your sheet. For instance, my Metrics is the column header for the column that shows the name of all the metrics we collect. I would assume you have a similar column. Remember that column names that have a space, special character or number must be enclosed by a square bracket. [Column 1]@row . The @row is used in place of a specific row number and is a best practice whenever possible. Can you share a screenshot of your sheet? If the column names are straightforward, like my are, I don't need to see any data. If the data will help me interpret what the column isc, then showing a row or two of data (or fake data) would be helpful. (You'll find you will almost always get a faster reply in the community if you can share a screenshot of what you're asking about. 😉). When I know the names of your columns I can give you the exact formula to use.
Kelly
-
@KDM Awesome, thank you!! I was able to get it via your explanation. I updated the column name, including the brackets as it did have a special character. Thanks for the insider tip! :) I'll always shoot to include screenshots.
Thanks so much for your help!!
-
@KDM Hi again Kelly! One other question, is it feasible to execute this same formula if the columns and rows are switched? In another sheet, we have lots of metrics as columns and the rows are the month names. We expect this sheet to last for several years so we wanted the columns to be the longterm consistent part.
But I'd love to show in a metric widget on a dashboard just the current month's data point. So if column X is Top page and row 1 is January and Row 2 is February, etc. is there a formula to show what the top page is for last month's top page? See screenshot.
Thank you!
-
Hey Jackie
I've tried to break this down into 3-4 steps, which makes this appear longer and perhaps more complicated than it actually is. If I didn't explain something well, please don't hesitate to ask me to clarify.
The formulas may look intimidating, but they really are not. The long formula uses a nested IF/AND statement so we can take into account both the month and the year since, year after year, you will want to only show the correct month of the correct year. I use the RIGHT function to look for the last 4 characters (from the right) of your Month@row cell. This assumes you will always write the year last and use 4 characters. If this isn't true, let me know and I'll replace it with a different function.
To make this approach work, you will need to add a helper column - format this helper column as a checkbox column, not a Text/number column. The formula will go into this checkbox helper column. I continued to call this helper as Completed Month, as it is set up to look at last Month's data. You can call it anything you like but if you change the name you'll need to change the name also in the summary sheet formula. After you add the helper checkbox column, you should be able to copy paste the formula directly into your sheet. Here's what it will look like.
Here's the formula to copy paste in the checkbox helper column:
=IF(AND(MONTH(TODAY()) = 1, CONTAINS("December", Month@row), YEAR(TODAY()) - 1 = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 2, CONTAINS("January", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 3, CONTAINS("February", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 4, CONTAINS("March", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 5, CONTAINS("April", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 6, CONTAINS("May", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 7, CONTAINS("June", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 8, CONTAINS("July", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 9, CONTAINS("August", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 10, CONTAINS("September", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 11, CONTAINS("October", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1, IF(AND(MONTH(TODAY()) = 12, CONTAINS("November", Month@row), YEAR(TODAY()) = RIGHT(Month@row, 4)), 1))))))))))))
Since you want to eventually use this data in a dashboard widget, we need to provide a single reference that the widget can connect to. We do this by adding a Sheet summary field (we're still in your spreadsheet grid). If you haven't used this before, you can find the sheet summary icon on your right menu of your grid sheet.
After clicking the icon, press the New Field button. You can name the sheet summary field anything you like. I called it Top Page Metric.
Copy Paste the formula below into the Sheet summary field
=JOIN(COLLECT([Top Page]:[Top Page], [Completed Month]:[Completed Month], 1, Month:Month, <>""))
This formula collects cells from the the [Top Page] column where, in the [Completed Month] column the cell equals 1 and, in the Month column, it is not an empty cell. After entering the formula, you should see the correct result appear in this field.
Finally, when you add the Metric Widget on your dashboard, you need to select the Sheet Summary data as you select the source sheet so you can access the field you created.
After this selection, everything else is the same as usual with the metric widget.
cheers,
Kelly
PS. If interested, below are info links to the functions I used:
-
@KDM This was such a clear explanation Kelly, thank you so much! I was able to implement this on my sheet and dashboard today and turn it around within my deadline. So so grateful for your help, thank you!
-
@Kelly Moore Okay so months later I used this and it worked when I only had 2 months of data - Jan and Feb, but as soon as I went to fill in March data, it still only reported Feb and didn't populate to March's. I'm not sure where I messed up or why it isn't populating the most recent data. I included the formula I used and a screen shot. Grey areas don't have data but I did black out the other metric name for privacy.
"Current" column should show most recent, filled in, data point of the year.
=IF(Goal@row <> "", IF(MONTH(TODAY()) = 2, Jan@row, IF(MONTH(TODAY()) = 3, Feb@row, IF(MONTH(TODAY()) = 4, Mar@row, IF(MONTH(TODAY()) = 5, Apr@row, IF(MONTH(TODAY()) = 6, May@row, IF(MONTH(TODAY()) = 7, Jun@row, IF(MONTH(TODAY()) = 8, Jul@row, IF(MONTH(TODAY()) = 9, Aug@row, IF(MONTH(TODAY()) = 10, Sep@row, IF(MONTH(TODAY()) = 11, Oct@row, IF(MONTH(TODAY()) = 12, Nov@row, IF(MONTH(TODAY()) = 1, Dec@row)))))))))))))
-
Hello @John24
Help me understand what you're trying to achieve and we can tweak the formula to meet your needs. The formula you show says if the Month of Today = 3, then show me February's data. It looks like it is showing you February's data. When the Month =4, it will show you March's data.
What data were you expecting to see and we can build to that.
Kelly
-
@Kelly Moore Thank you for calling that out.
Monthly I will add new data to the columns Jan, Feb, Mar, etc. and my hope is for the column listed as "current" to reflect the most recent month's data for each of the goal metrics under medical.
What I hear you saying, if i understand it right, is that if I wait to look at this data in april then it will report up march's data?
-
Yes , the formula above will show March's data for the entire month of April.
This formula was originally written to post the last month's data. Different types of data came into the month at different times during the month so the only time one knew that the data was 'final' for given any metric was at month's end.
If you want to show the current month's data, we can re-write the formula for that. If we simply convert the above formula, you will have blank metrics until data is added. Is that what you intend? Please advise.
Kelly
-
That was super helpful! I did rewrite it by adjusting what month today would be and what month I wanted it to show. This worked for me!
-
John, that's great! I'm glad you got it to work. Nice job!
Holler back at the community if there's ever a smartsheet question.
Kelly
-
@Kelly Moore if I don't want to use the summary sheet and want to pull the metrics from a separate sheet, how would I rewrite your formula so it sources the original sheet? Control Center doesn't allow me to pull metrics from the summary sheet so I've had to link calculations to a separate sheet.
=JOIN(COLLECT([Top Page]:[Top Page], [Completed Month]:[Completed Month], 1, Month:Month, <>""))
-
Remember - since these are cross sheet references you cannot simply copy paste the formula into your sheet but must build the cross sheet references physically through the formula window.
=JOIN(COLLECT({source sheet column that you want joined}, {source sheet Month NUMBER column}, =1, {source sheet Month NUMBER column}, <>""))
This exactly re-write the above formula. You could change any of the criteria or columns that you wish. Let me know if you have something specific. If you do have specifics - know that screenshots of the source sheet and target sheet will always help the community (and usually get you a faster response from the community)
Kelly
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
