Calculating ticket count by Month for past 1 yr
Hello, I have created a metrics sheet that gets the count of closed tickets based on Month and Year. I want to display the last 12 months ticket count trend in the dashboard. I am stuck and unable to identify the last 12 months starting Jan 2023 to Feb 2022. Can you please help me how I can display the last 12months?
I have used the below formula to calculate the count of tickets but want to know how I can identify last 12 months from today.
=COUNTIFS({Demo - Use Cases for Trial Delivery CompletionMont}, [Completion Month]@row, {Demo - Use Cases for Trial Delivery CompletionYear}, [Completion Year]@row, {Demo - Use Cases for Trial Delivery Optimi Hierarc}, =1, {Demo - Use Cases for Trial Delivery ReqStatus}, OR(@cell = "Completed", @cell = "No Longer Tracking"))
Answers
-
@Smartsheet_User I believe this may have been answered in the post you commented on.
-
I tried using your solution in the post but I am getting an invalid value error. I am referencing the create date in another sheet. Not sure why it's not working for me.
-
That's not the formula I suggested for your particular case.
-
=COUNTIFS({Demo - Use Cases for Trial Delivery CreateDate}, AND(IFERROR(MONTH(@cell), 0) = MONTH(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1))), IFERROR(YEAR(@cell), 0) = YEAR(IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1), DATE(YEAR(TODAY()) + 1, MONTH(TODAY()) - 11, 1)))), {Demo - Use Cases for Trial Delivery Optimi Hierarc}, =1, {Demo - Use Cases for Trial Delivery ReqStatus}, OR(@cell = "Completed", @cell = "No Longer Tracking"))
I have used this formula from your other post and it is throwing invalid value error. I am also checking if the Hierarchy is 1 and if the status is "Completed" or "No longer tracking" in your formula.
Rolling previous 12 month count that spans into previous year. — Smartsheet Community
-
-
Thank you so much Paul. I am getting invalid operation with this formula but your other solution to get the rolling 12month grid worked for me.
https://community.smartsheet.com/discussion/69542/rolling-12-month-grid
-
Paul - I am using the same logic to get the weekly count of tickets for the last 12 weeks using weeknumber function. 1/1/2023 falls under week 52 but the year is 2023. The issue I am seeing is that the tickets that are completed on 1/1/2023 are not getting counted since M6 has the week as 52 and Year as 2023 whereas the below screenshot from the datasheet has completion week as 52 and Completion year as 2023. How can I handle this?
=COUNTIFS({Demo - Use Cases for Trial Delivery CompWeek}, [M6]21, {Demo - Use Cases for Trial Delivery CompYear}, [M6]22, {Demo - Use Cases for Trial Delivery Hierarchy}, =1, {Demo - Use Cases for Trial Delivery ReqStatus}, OR(@cell = "Completed", @cell = "No Longer Tracking"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!