URGENT * Glide Aggregate on duration field

[JérômeM]

Hello 
I'm trying to achieve the following requirement. 
On the incident table, I have a field called 'u_alarm_creation_delay' which is a duration field. 
I would like a script that goes in all the incidents and do the average of all the duration. 
I tried multiple things but I don't get the correct results. 
Here is my script : 

    var today = new GlideDateTime();

    var startOfMonth = new GlideDateTime();
    startOfMonth.setDisplayValue(startOfMonth.getDate().getByFormat('yyyy-MM') + '-01 00:00:00');

    var alarmGR = new GlideRecord('incident'); // Replace with your table name
    alarmGR.addEncodedQuery('sys_created_onONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()^u_alarm_creation_delayISNOTEMPTY^u_alarm_acknowledgment_delayISNOTEMPTY');
    alarmGR.query();
    var totalSeconds = 0;
    var count = 0;

    while (alarmGR.next()) {
        totalSeconds += parseInt(alarmGR.u_alarm_creation_delay.getDurationValue());
        count++;
    }

    var averageDuration = totalSeconds / count;
    var statGr = new GlideRecord('u_average_statistics');
    statGr.initialize();
    statGr.u_month = startOfMonth.getDate();
	statGr.u_metric_name = 'Alarm Creation Time';
	statGr.u_average_value = averageDuration;
	statGr.insert();

    gs.log('>>> Average Calculation : total seconds ' + totalSeconds);
    gs.log('>>> Average Calculation : count ' + count);

Can someone help me ? 
Thanks
Jérôme

[Ankur Bawiskar · Accepted solution]

@JérômeM 

try this

-> use getNumericValue() to get the milliseconds and then add up

var today = new GlideDateTime();

var startOfMonth = new GlideDateTime();
startOfMonth.setDisplayValue(startOfMonth.getDate().getByFormat('yyyy-MM') + '-01 00:00:00');

var alarmGR = new GlideRecord('incident');
alarmGR.addEncodedQuery('sys_created_onONThis month@javascript:gs.beginningOfThisMonth()@javascript:gs.endOfThisMonth()^u_alarm_creation_delayISNOTEMPTY');
alarmGR.query();

var totalSeconds = 0;
var count = 0;

while (alarmGR.next()) {
    var durationMillis = alarmGR.u_alarm_creation_delay.getNumericValue(); // in milliseconds
    totalSeconds += durationMillis / 1000; // convert to seconds
    count++;
}

if (count > 0) {
    var averageDuration = totalSeconds / count;

    var statGr = new GlideRecord('u_average_statistics');
    statGr.initialize();
    statGr.u_month = startOfMonth.getDate();
    statGr.u_metric_name = 'Alarm Creation Time';
    statGr.u_average_value = averageDuration;
    statGr.insert();

    gs.log('>>> Average Calculation : total seconds ' + totalSeconds);
    gs.log('>>> Average Calculation : count ' + count);
    gs.log('>>> Average Calculation : average seconds ' + averageDuration);
} else {
    gs.log('>>> No records found for average calculation.');
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@JérômeM 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[JérômeM]

Worked like a charm ! Thanks !