How to sum and average of a duration result ?

[MR Carvalho]

Hi guys,

I created a code to show me the average of a duration field, the result is about this year and the average per month, so the result is like this: 

Jan/2023 - 10 05:12:49

Feb/ 2023 -6 13:01:30

Mar/2023 - 6 21:34:11

Apr/2023 - 5 21:11:33

May/2023 - 4 14:11:49

PS: the first number of result means days, then hours, then minutes then second.

Thats my code:

var data = [];

var incidentGA = new GlideAggregate('incident');

incidentGA.addEncodedQuery("priority=2^resolved_atONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()^state!=8^category=Business^u_mttrISNOTEMPTY");

incidentGA.addTrend('resolved_at', 'month');
incidentGA.groupBy("category");
incidentGA.addAggregate('AVG', 'u_mttr'); //u_mttr is a duration field

incidentGA.query();

while (incidentGA.next()) {

data.push(incidentGA.getAggregate('AVG', 'u_mttr'));

}

gs.print(data);

But now I would like to sum and also get the average of my result, you guys have some function to sum and get the average of my result thats is a duration? Please, could someone help me?

Thanks in advance.

[Ratnakar7]

Hi @MR Carvalho ,

You can use the GlideDuration class available in ServiceNow. Here's an example of how you can modify your code to calculate the sum and average of the duration values stored in the data array:

var data = [];
var incidentGA = new GlideAggregate('incident');

incidentGA.addEncodedQuery("priority=2^resolved_atONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()^state!=8^category=Business^u_mttrISNOTEMPTY");

incidentGA.addTrend('resolved_at', 'month');
incidentGA.groupBy("category");
incidentGA.addAggregate('AVG', 'u_mttr'); // u_mttr is a duration field

incidentGA.query();

var totalDuration = new GlideDuration();
var totalCount = 0;

while (incidentGA.next()) {
  var avgDuration = new GlideDuration(incidentGA.getAggregate('AVG', 'u_mttr'));
  data.push(avgDuration);
  totalDuration = totalDuration.add(avgDuration);
  totalCount++;
}

gs.print(data); // Array of average durations

var averageDuration = totalDuration.divide(totalCount);
gs.print('Average Duration: ' + averageDuration.getDisplayValue());

var sumDuration = totalDuration;
gs.print('Sum Duration: ' + sumDuration.getDisplayValue());

If my response was helpful in resolving the issue, please consider accepting it as a solution by clicking on the ✅Accept solution button and giving it a thumbs up 👍. This will benefit others who may have a similar question in the future.

Thank you!

Ratnakar

[MR Carvalho]

Hi @Ratnakar7 I tried to use your code but something is wrong, below I attached the error and the result that is come as []. Do you know what can I do?

Many thanks. 

[Riya Verma]

HI @MR Carvalho ,
I trust you are doing great.

Certainly! To sum and calculate the average of your duration field results, you can make use of the GlideDuration class in ServiceNow. Here's an example code snippet that demonstrates how you can modify your existing code to achieve this:

var data = [];
var incidentGA = new GlideAggregate('incident');
incidentGA.addEncodedQuery("priority=2^resolved_atONThis year@javascript:gs.beginningOfThisYear()@javascript:gs.endOfThisYear()^state!=8^category=Business^u_mttrISNOTEMPTY");
incidentGA.addTrend('resolved_at', 'month');
incidentGA.groupBy("category");
incidentGA.addAggregate('AVG', 'u_mttr'); // u_mttr is a duration field
incidentGA.query();

var totalDuration = new GlideDuration();
var totalCount = 0;

while (incidentGA.next()) {
  var avgDuration = new GlideDuration(incidentGA.getAggregate('AVG', 'u_mttr'));
  totalDuration = totalDuration.add(avgDuration);
  totalCount++;
}

var averageDuration = totalDuration.divide(totalCount);

gs.print('Total: ' + totalDuration.getDisplayValue());
gs.print('Average: ' + averageDuration.getDisplayValue());

[MR Carvalho]

Hi @Riya Verma, I tried to use your code but something is wrong, below I attached the error when I print the result. Do you know what can I do?

Many thanks.