Calculating ticket count by Month for past 1 yr

Smartsheet_User

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"))

Paul Newcome

@Smartsheet_User I believe this may have been answered in the post you commented on.

Smartsheet_User

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.

Paul Newcome

That's not the formula I suggested for your particular case.

https://community.smartsheet.com/discussion/comment/359115#Comment_359115

Smartsheet_User

=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

Paul Newcome

@Smartsheet_User

I was referring to this one:

Smartsheet_User

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

Smartsheet_User

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"))