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? 

[TerryC03]

This is not working either. I'm running into my initial issue. It prints the values, but does not calculate the average of the values. 

[Aniket Chavan]

Hello @TerryC03 ,

Please give a try to the modified code below and see how it works for you.

(function executeRule(current, previous /*null when async*/) {
    var ga = new GlideAggregate('sn_safe_sprint');
    ga.addQuery('state', 3); // Complete
    ga.orderBy('sprint_number'); // Order by an appropriate field representing sprint order
    ga.setLimit(3); // Select the first three completed sprints during the query
    ga.addAggregate('AVG', 'actual_points'); // Calculate the 'Completed Points' column
    ga.query();

    if (ga.next()) {
        gs.info('Average Completed Points: ' + ga.getAggregate('AVG', 'actual_points'));
    }

})(current, previous);

Spoiler

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks & Regards,

Aniket.

[Deborah Brown L]

Hi @TerryC03 ,

This below script will definitely work. Try it

You should add SetGroup(false) and SetOrder(false) in the Query.

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

ga.setGroup(false); // this is compulsory

 ga.setOrder(false);// this is compulsory

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

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

ga.query();

while(ga.next()){

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

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

}

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

Thank you!

[Tai Vu]

Hi @TerryC03 

You should be facing the below error.

Skip invalid GroupBy condition: ORDERBYsys_update_on

Since you're calculating Average, it requires a group of records to be able to calculate it (groupBy).

Your script could look like below. The script will order (A - Z) on the actual_points field (aggregate field).

(function executeRule(current, previous /*null when async*/) {
    var ga = new GlideAggregate('sn_safe_sprint');
    ga.addQuery('state', 3); 
    ga.orderBy('state'); //group by state as your query by state
    ga.setAggregateWindow(0,2); //limit 2
    ga.addAggregate('AVG', 'actual_points');
    ga.query();
    while(ga.next()){
        gs.info(ga.getAggregate('AVG', 'actual_points'));
    }

})(current, previous);

If your case is required orderByDesc on updated time like below, you can consider to build a function to collect the record's sys_id with orderByDesc first. Then use the sys_id in result for the query of the GlideAggregate.

ga.orderByDesc('sys_updated_on');

Cheers,

Tai Vu