How to create an "average open ticket" report

[JMorgan2]

Hi,

I'm looking for a metric where it details how long a ticket has been open. I've tried everything thats available in the report suite. 

There's a "time to close" metric which is great but I also need the same for "open tickets". 

The reaason is I'm looking to report on the average open time of bugs tickets that are raised through the service desk.

Does anyone have any suggestions? 

Any advise would be greatly appreciated

Many thanks,

[JMorgan2]

Hi SMumbyHarrison,

The time to close metric only looks at closed tickets whereas I'm looking at currently open tickets where I want to find an average open time of those tickets, specifically bug tickets raised by customers.

I take it the calculation field would be a field that I could add on the ticket details itself? 

The field could be current date for example and then I could filter it by create date?

I'll see if I can get that to work, thanks for the suggestion, I'll let you know if it works or not.

Many thanks,

[SMumbyHarrison]

Hi JMorgan,

So you have two options:

If you do not need it to be a report on a dashboard, you could convert your 'create day' field to show the 'relative time' via the edit propertiy in the ticket's proprties settings, then have a custom view in tickets to narrow down to tickets you want to view and see how long they are open for and would look like this:

If you want it in reports, you would need to create a 'today's date' field and have it set each day by a workflow to run everymore to set the 'today's date' field as day of action. Then have a another field which will be your calcation field to get the time difference between the create date and the todays date field. You will then be able to create a report from that field.

Kind regards,

Sam

[JMorgan2]

Hi Sam,

So I have created a field that captures the current date. I then have a workflow to run everyday to update it.

I've then created a calculated field that looks at the difference between the create date and current date which is progress. However, its including all days and I'm not sure how to remove the weekends from the calculated field.

If I can get it to only calculate five business days into it then I think I have the solution.

Do you have any suggestions? 

Many thanks,

Jamie

[SMumbyHarrison]

Hi Jamie, 

So if you are wanting to run it for be weekdays only, you will need a different field type. I would recommend a number field instead. Have the workflow to set to run every morning and an additonal filter to say 'ticket is open' as the requeirment to run, and in 'settings' change it so that the workflow can only run on weekdays (You would need to manully add bank holidays if you want these excluded too) and then have the workflow to increase the value of the field by 1 each day.

This will then give you amount of days a ticket is open for, excluding days you are open.

Kind regards,

Sam

[JMorgan2]

Thanks for your help Sam,

I think I'll stick with seven days a week because I would need to also amend the closed tickets ootb metric too which I think would be messy.

Many thanks

[SMumbyHarrison · Accepted solution]

Hi JMorgon, 

Could you provide more context in what you are needing from the 'time open' element that a 'time to close' would not cover?

You could look at creating a Calculation field where you can set the metrics for the fields, for example, a time between to dates or a custom equation with the number format set as a duration so that you can use specific criteria to find what you need.

I hope this helps.

Kind regards

Sam