Need to report on date and category

Clydene Horrigan

I have a table for help desk tickets. Among other things, we capture the closed date , category, and "rolled category".

I need a monthly report that will tell me for each of the last 13 months, how many tickets were closed for each of the "rolled categories."

Mark Cronk

Good evening,

Creating a separate metric sheet will give you the most flexibility. Create a new sheet with Columns [Rolled Category], [current], [-1], [-2],.....[-12]

In the Rolled Category column enter your categories. Then reference [rolled category]@row in your COUNTIFS formulas; @cell = [rolled category]@row

See my response here on creating a rolling 12 month table:

https://community.smartsheet.com/discussion/73464/dashboard-chart-to-show-rolling-12-months

Help?

Mark

Cody Holmes

Hi Clydene.

So there are many steps here, but I think I can give you a rough outline of the process to building such a report. At the very least, you will gain some familiarity and hopefully guide yourself into the right answer. I have outlined some steps for you below.

1. In the desired folder/workspace, hit the Create button and select Report. Name it whatever you would like to call the report, and select the "Row report" option. Open the report.
2. In the new report view (looks very much like the typical grid), go up to the "Source Sheets" option. Ensure that no sheets are selected, and navigate to the desired sheet to build the report off of. Hit Okay.
3. Select the Group option in the navigation bar, and select the "Rolling Category" column. Sort however you prefer. You can also select another grouping if you wish.
4. Select the Summarize option. Choose "Sheet Name" from the dropdown, and then select the "Count" option for your aggregation.
5. Optional: If you want to add children under the Rolling Category headers of your report, you can do so from the Column option in the navigation bar.
6. To make the report go only back 13 months, navigate to the Filter Criteria.
7. Select your "Closed Date" column as the criterion column.
8. (These next steps are not a precise filter; keep that in mind.) In the second dropdown, select the option that says "is in the last (days)." Days is the only option, hence the impreciseness here...
9. In the third input box of the Filter Criteria dialogue box, you probably want to put either 395 to 397. Why? Because a year can be 365 days (12 months), or 366 for leap years, plus the 30-31 days for the 13th month.
10. And that's about it. You can now set up the report to be sent out monthly through an attachment by going up to File -> Send as attachment -> (Fill out necessary info.) -> Set Delivery Schedule.

Hope that helps! Please accept my answer as a solution if it got you to your goal so others can see this answer. And be sure to give me a vote if I proved helpful.

👍😎👍 -C.

Clydene Horrigan

https://community.smartsheet.com/discussion/78742/need-to-report-on-date-and-category

Mark,

That gives me the total for each rolled category that was found in the filter, but it does not give me the count for each category for each month. Thanks

Clydene Horrigan

Mark,

I ended up adding some helper columns to find the month and year of the closed date. Then I concatenated the month and year and used that as my recorded date. I created a row report using a filter on the date < 397 days ago. Finally, I created a pivot table that used my new recorded date and the rolled category. Thanks for the help.

Mark Cronk

Beautiful. Glad you found a solution. Thank you for contributing to the Community.

Mark