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.

[Riya Verma]

Hi @MR Carvalho ,
You can use below fundtion :

var averageDuration =this.divide(totalDuration,totalCount);

divide : function (totalDuration,totalCount) {
return totalDuration/count;
}

[MR Carvalho]

@Riya Verma we are close 🙂 , your solution worked for the total, but not worked to the average, below I attached the result. Please do you know what can I do?

Thats the code updated:

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 = this.divide(totalDuration, totalCount);

divide: function(totalDuration) {
return totalDuration;
}

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

[Riya Verma · Accepted solution]

Hi @MR Carvalho ,
Could you try below code please , I m also trying on my end

var data = [];
var sum = new GlideDuration();
var count = 0;

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()) {
  var avgDuration = incidentGA.getAggregate('AVG', 'u_mttr');
  var duration = new GlideDuration(avgDuration);

  data.push(duration);

  sum.add(duration);
  count++;
}

gs.print("Average Duration: " + sum.getNumericValue() / count);
gs.print("Sum of Durations: " + sum.getDisplayValue());

, 

[MR Carvalho]

@Riya Verma it worked, thanks for your help.