Month to Month Trend Over Time to Show Months without Trend
I have an escalation sheet and we're trying to get a report to show how many escalations over time. I was able to bucket them to show by month, but want it to show months that = 0 so it can show a true trend over the year. As you can see in my current report months with no escalations do now show up. (i.e. May, June)
Is this possible?
Answers
-
Yes, you will just need a data point for that. How are you doing your roll up? Is it just a pivot that does it for you? You could most likely use a roll up sheet that aggregates this for you with the month being what the info is grabbing.
-
How I have my current report being exported:
- Columns: Date of Escalation and Month #
- Filter: Date of Escalation 1/1/23 - 12/31/23
- Group: Month # - ascending
- Summary: Month # - count
What data point would I need to add to see those without escalations in them?
-
You will need to use a separate metrics sheet with formulas containing cross sheet references instead of a report. Then you would ad 0.00001 to the end of each COUNTIFS so that there are no values that actually equal zero, use the button on the top toolbar to hide the decimals, and then finally chart from this table.
=COUNTIFS({Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023)) + 0.00001
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!