GlideAggregate AVG not calculating as expected.

[TerryC03]

Hello,

I'm trying to (in the PDI), calculate the average of 3 SAFe Sprint's (sn_safe_sprint) Completed Points. However, when I use getAggregate('AVG', 'actual_points'), it only prints the value of the completed points, not the average. For example,

I want to calculate the average of the first three sprints. I'm expecting an output of (6+16+16)/3 = 12.66. However, I'm only getting the values 6, 16, 16; the average is not calculating.

Code:

(function executeRule(current, previous /*null when async*/) {
var ga = new GlideAggregate('sn_safe_sprint');
ga.addQuery('state', 3); //Complete
ga.orderByDesc('sys_updated_on');
ga.setLimit(2); //Select three completed sprints during query.
ga.addAggregate('AVG', 'actual_points'); //Calculate the 'Completed Points' column.
ga.query();

while(ga.next()){
gs.info(ga.getAggregate('AVG', 'actual_points')); // <-- Here, I'm expecting the average of the three sprints' completed points.
}

})(current, previous);

How may I get the average to print? 

[Sohithanjan G]

Hi @TerryC03 ,

To calculate the average of the completed points for the first three sprints, you need to sum up the completed points for all three sprints first, and then calculate the average. Here's the corrected code:

(function executeRule(current, previous /*null when async*/) {
    var ga = new GlideAggregate('sn_safe_sprint');
    ga.addQuery('state', 3); // Complete
    ga.orderByDesc('sys_updated_on');
    ga.setLimit(3); // Select three completed sprints during query.
    ga.addAggregate('AVG', 'actual_points'); // Calculate the 'Completed Points' column.
    ga.query();

    var totalCompletedPoints = 0;
    var sprintCount = 0;

    while (ga.next()) {
        sprintCount++;
        totalCompletedPoints += parseFloat(ga.actual_points.getAggregate('SUM'));
    }

    var averageCompletedPoints = totalCompletedPoints / sprintCount;
    gs.info("Average Completed Points for the first three sprints: " + averageCompletedPoints);

})(current, previous);

In this corrected version:

1. I changed the setLimit from 2 to 3 to ensure it fetches the first three completed sprints.
2. Inside the while loop, I sum up all the completed points for each sprint.
3. After exiting the loop, I calculate the average by dividing the total completed points by the number of sprints.
4. Finally, I print the calculated average outside the loop.

🙂

[TerryC03]

There's an error for totalCompletedPoints.

[Sohithanjan G]

Hi @TerryC03 , Whats the error you are getting

[TerryC03]

I'm receiving a compile error: