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? 

[Anirudh Pathak]

Hi @TerryC03 ,

Did you try the below code - 

function getAggregateByType(pTable, pColumn, pEncodedQuery, pAggregateType) {
 var ga = new GlideAggregate(pTable);
 if (pEncodedQuery) {
  ga.addQuery(pEncodedQuery);
 }
 ga.setGroup(false);
 ga.setOrder(false);
 ga.addAggregate(pAggregateType, pColumn);
 ga.query();
 if (ga.next()) {
	gs.info(pEncodedQuery);
  return ga.getAggregate(pAggregateType, pColumn);
 } else {
  return null;
 }
}

function getAvg(pTable, pColumn, pEncodedQuery) {
 return getAggregateByType(pTable, pColumn, pEncodedQuery, 'AVG');
}

gs.info(getAvg('sn_safe_sprint','actual_points','state=3'));

For your reference -

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0852541

[Sohithanjan G]

Use this @TerryC03 

(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('SUM', '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);

[TerryC03]

It's literally giving the same error...

[Anirudh Pathak]

Hi @TerryC03 ,

Please use below code - 

function getAggregateByType(pTable, pColumn, pEncodedQuery, pAggregateType) {
 var ga = new GlideAggregate(pTable);
 if (pEncodedQuery) {
  ga.addQuery(pEncodedQuery);
 }
 ga.setGroup(false);
 ga.setOrder(false);
 ga.addAggregate(pAggregateType, pColumn);
 ga.query();
 if (ga.next()) {
	gs.info(pEncodedQuery);
  return ga.getAggregate(pAggregateType, pColumn);
 } else {
  return null;
 }
}

function getAvg(pTable, pColumn, pEncodedQuery) {
 return getAggregateByType(pTable, pColumn, pEncodedQuery, 'AVG');
}

gs.info(getAvg('sn_safe_sprint','actual_points','state=3'));

For your reference - 

https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0852541

[Deborah Brown L]

Hi @TerryC03 ,

Try the below script,

var ga = new GlideAggregate('sn_safe_sprint');
ga.addQuery('state', 3); // Complete
ga.orderByDesc('sys_updated_on');

ga.groupBy(''); // add some groupby, then only it will work properly

ga.setLimit(2); //Select three completed sprints during query.

ga.addAggregate('SUM', 'actual_points'); //Calculate the 'Completed Points' column.

ga.addAggregate('COUNT', 'actual_points');

ga.query();

while(ga.next()){

    var count = ga.getAggregate('COUNT','actual_points');

    var sum  = (ga.getAggregate('SUM','actual_points' )); // <-- Here, I'm expecting the average of the three sprints' completed points.

var avg = sum/count;

gs.info('avg: ' + avg);

}

Please mark my answer as accepted if you find the solution here and hit helpful.

Thank you!