Getting Average Duration on Incidents by month

[D-a-n_L]

How do I get the average duration it took to open and resolve an all incidents by month?

Can I do this in any repeatable report?

[Robert H · Accepted solution]

Hello @D-a-n_L ,

Looking at your last screen shot, all you need to do is select "Trend by" to "Created", then "Per" to "Month", and "Aggregation" to "Average". This will make one more field appear where you need to select "Duration".

Basically the four fields that I had highlighted in red boxes in my original response.

Regards,

Robert

[D-a-n_L]

Not yet. Can this be done via a report without having to installing the other modules?

[Shivalika]

Hello @D-a-n_L 

Use below 👇 script - 

// Create a GlideAggregate object for the Incident table

var ga = new GlideAggregate('incident');

// Add aggregation for the 'duration' field (assuming 'duration' is a field that stores the incident duration)

ga.addAggregate('AVG', 'duration');  

// Use addTrend to group the results by the month of the incident creation date

ga.addTrend('sys_created_on', 'month'); // Group by month of the incident creation date

// Execute the query

ga.query();

// Iterate through the results

while (ga.next()) {

    var avgDuration = ga.getAggregate('AVG', 'duration'); // Get the average duration for the incident

    var month = ga.getValue('sys_created_on'); // Get the month of the incident

    // Print the result (you can replace this with your desired action)

    gs.info('Average Duration for month ' + month + ': ' + avgDuration);

}

This will give you month wise display of average duration . Replace the field names with exact value you have in the backend and this will work and is simple. 

2nd Solution 

Also via report "TIME SERIES" type report should work.  

### Steps to Add Trend in a ServiceNow Report:

1. **Navigate to the Report Module**:

   - Go to **Reports** > **Create New** or open an existing report.

2. **Select the Report Type**:

   - Choose a **Time Series** report type to visualize trends over time.

3. **Configure Data for the Report**:

   - Choose the **Incident** (or relevant) table as the source for the report.

   - Select the fields to display (e.g., `Duration`, `Opened By`, `Created On`, etc.).

4. **Apply Grouping**:

   - Group by **Date/Time** field (e.g., `sys_created_on`) and select the **Month** or **Day** for grouping.

   - You can also add other grouping such as **Opened By** if you want to break it down by user.

5. **Add Trend**:

   - In the report configuration, you can add a **trend** on the **Duration** field to show how the average (or other aggregation) changes over time.

   - Under the "Aggregation" section, select **Average**, **Sum**, or any other aggregate function for your field (e.g., Duration).

6. **Customize the Trend Visualization**:

   - Use the **Chart Type** options (Line, Bar, etc.) to represent the trend visually.

I hope these resolve your queries. 

Kindly mark my answer as helpful and accept solution if it helped you in anyway. This will help me be recognized for the efforts and also move this questions from unsolved to solved bucket. 

Regards,

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY*

[Robert H]

Hello @D-a-n_L ,

Yes. Did you see my response from Thursday?

Regards,

Robert

[D-a-n_L]

Yes, but the fields you outline to select due not exist or I am nothing following the steps properly.

1. is good

2. is good

3. This is where I get confused. 

[Shivalika]

Hello @D-a-n_L 

what are you confused about ?