Sum then average of an array

[John Reynolds]

I'm trying to calculate the average backlog age of items in a freeform board from the vtb_card table. I'm grabbing the sys_created_on date, converting it into seconds, and then converting that into days, before putting it into an array.   What I seem to be stuck on is the summing the value of the array.   It doesn't actually appear to be adding the values together, but instead merging them.

I'm sure there is probably an easier way to do this, but this is what I have. I am not a coder by nature (or nurture).

// Get Today's Date
var today = gs.nowDateTime();
var dc = new DurationCalculator();
// Query for cards in the backlog
var age = new GlideRecord('vtb_card');
age.addQuery('board', '189201660f6b5e0032de6ab8b1050e7f'); // Deskside
age.addQuery('lane', 'b80401660f6b5e0032de6ab8b1050e49'); // Backlog
age.addQuery('removed', 'false');
age.query();
// Initialize the array
var holdThis = new Array(0);
// Loop through and list the backlog items
while (age.next()){ 
var created = age.task.sys_created_on;
var seconds = dc.calcScheduleDuration(created,today);
var calc = (seconds / 86400).toFixed(0);
   holdThis.push(calc);
   gs.print('Task: ' + age.task.number + " Created: " + age.task.sys_created_on + " Duration (seconds): " + seconds + " Days Open: " + calc);
}
// Print the array
gs.print("Array: " + holdThis);
// Sum the array
var total = holdThis.reduce(function (a, b) {
   return a + b;
}, 0);
// Average the array
var avg = (total / holdThis.length);
gs.print("Total: " + total);
 gs.print("Length: " + holdThis.length);
 gs.print("Average: " + avg);

Here is the output:

*** Script: Task: PTSK0002271 Created: 2017-04-26 15:02:07 Duration (seconds): 3810600 Days Open: 44
*** Script: Task: PTSK0002272 Created: 2017-04-26 15:15:01 Duration (seconds): 3809826 Days Open: 44
*** Script: Task: PTSK0001826 Created: 2017-01-25 18:40:13 Duration (seconds): 11659914 Days Open: 135
*** Script: Task: PTSK0002297 Created: 2017-05-04 14:22:12 Duration (seconds): 3121795 Days Open: 36
*** Script: Task: PTSK0002366 Created: 2017-05-19 17:54:19 Duration (seconds): 1813068 Days Open: 21
*** Script: Task: PTSK0002094 Created: 2017-03-14 12:09:14 Duration (seconds): 7536173 Days Open: 87
*** Script: Task: PTSK0002209 Created: 2017-04-12 16:30:52 Duration (seconds): 5014875 Days Open: 58
*** Script: Task: PTSK0001926 Created: 2017-02-10 15:43:14 Duration (seconds): 10288133 Days Open: 119
*** Script: Array: 44,44,135,36,21,87,58,119
*** Script: Total: 0444413536218758119
*** Script: Length: 8
*** Script: Average: 55551692027344770

[Goran WitchDoc]

Perhaps do something like this:

// Get Today's Date  

var today = gs.nowDateTime();  

var dc = new DurationCalculator();  

// Query for cards in the backlog  

var age = new GlideRecord('vtb_card');  

age.addQuery('board', '189201660f6b5e0032de6ab8b1050e7f'); // Deskside  

age.addQuery('lane', 'b80401660f6b5e0032de6ab8b1050e49'); // Backlog  

age.addQuery('removed', 'false');  

age.query();  

// Loop through and list the backlog items  

var total = 0:  

while (age.next()){    

var created = age.task.sys_created_on;  

var seconds = dc.calcScheduleDuration(created,today);  

total += seconds;

}

var avg = (total / age.getRowCount());

//to days

var avgDays = (avg / 86400).toFixed(0);

gs.print("Average:" + avgDays);

//Göran

[kevinlenienweav · Accepted solution]

The problem is that the values in your array are strings and are only being concatenated by the reduce call.

As you push into the array do a parseInt so that the values are integers.

So in this case:

holdThis.push(calc);  

becomes:

holdThis.push(parseInt(calc));  

[John Reynolds]

Thanks Kevin! That was exactly what I needed.   It is now returning the correct values.

*** Script: Task: PTSK0002271 Created: 2017-04-26 15:02:07 Duration (seconds): 3814618 Days Open: 44
*** Script: Task: PTSK0002272 Created: 2017-04-26 15:15:01 Duration (seconds): 3813844 Days Open: 44
*** Script: Task: PTSK0001826 Created: 2017-01-25 18:40:13 Duration (seconds): 11663932 Days Open: 135
*** Script: Task: PTSK0002297 Created: 2017-05-04 14:22:12 Duration (seconds): 3125813 Days Open: 36
*** Script: Task: PTSK0002366 Created: 2017-05-19 17:54:19 Duration (seconds): 1817086 Days Open: 21
*** Script: Task: PTSK0002094 Created: 2017-03-14 12:09:14 Duration (seconds): 7540191 Days Open: 87
*** Script: Task: PTSK0002209 Created: 2017-04-12 16:30:52 Duration (seconds): 5018893 Days Open: 58
*** Script: Task: PTSK0001926 Created: 2017-02-10 15:43:14 Duration (seconds): 10292151 Days Open: 119
*** Script: Array: 44,44,135,36,21,87,58,119
*** Script: Total: 544
*** Script: Length: 8
*** Script: Average: 68

[andrewpilachows]

You could simplify the script if you didn't really care about all the individual bits at different stages.   Also need to add in a check for divide by zero.

var sum = 0;
var count = 0;

while (age.next()) {     
       var created = age.task.sys_created_on;   
       var seconds = dc.calcScheduleDuration(created,today);   
       sum += Math.trunc(seconds / 86400);   
       count++;   //Increment the count
}   

// Average
var avg = 0;
if (count > 0)   
       avg = (sum / count);