AVG value being calculated is incorrect

[divvi_vamsi]

I am trying to run a glideAggregate for Average value on syslog_transaction table for response_time for last few minutes. But i observe the value being returned is the MAX value instead of AVG of all the records within that time range.

Not sure what is being missed. Below is the script:

var count = new GlideAggregate('syslog_transaction');

count.addQuery('sys_created_on','>=', gs.minutesAgo(2));

count.addAggregate('AVG', 'response_time');

count.query();

while(count.next()) {

  var avg= count.getAggregate('AVG', 'response_time');

}

gs.log('Response time Average is '+ avg);

Thanks
Divya

[divvi_vamsi]

Hi Shishir,

Do you get correct values with the above code. The idea seems to be good, but it is just giving sum of averages of types total but not the actual average.

The individual averages were also incorrect when viewed with actual values obtained through list calculations for last 15minsi on the list view

Thanks
Divya

[Shishir Srivast]

Hi Divya,

I think, i am getting the correct result, i just tried with last 1 minute data, please find the result below.

[divvi_vamsi]

the values for different types are correct individually but i am speaking about the summed up total. Calculating averages of average is not the right way to calculate the overall average and that is the reason for incorrect values for the final avg value.

I was thinking if total of response time can be summed up and row count is taken separately and the average value is calculated manually.

Thanks

Divya

[Shishir Srivast]

that' correct. it's wrong. let me think if we can achieve in other way.

[Shishir Srivast · Accepted solution]

Hi Divya,

Can you please try below code. I tried in Background Script and seems working. Basically I have applied below formula.

Total avg: [(avg of type A* Number count of Type A) + (avg of type B * Number count of Type B)] / (Number count of Type A + Number count of Type B)

var count = new GlideAggregate('syslog_transaction');

count.addQuery('sys_created_on','>=', 'javascript:gs.minutesAgo(1)');

count.addAggregate('AVG', 'response_time');

count.addAggregate('COUNT', 'response_time');

count.groupBy('type');

count.query();

var final_avg = 0;

var cnt = 0;

var avg = 0;

var total_val = 0;

var total_count = 0;

while(count.next()) {

total_count =+ parseInt(total_count) + parseInt(count.getAggregate('COUNT', 'response_time'));

avg = count.getAggregate('AVG', 'response_time');

cnt = count.getAggregate('COUNT', 'response_time');

total_val =+ parseFloat(total_val) + (parseFloat(avg) * parseInt(cnt));

gs.log(count.type + '       Response time Average is :         '+ avg);

gs.log(count.type + '       Response time count is :         '+ cnt);

}

gs.log('***************************************************');

gs.log('total val         :' + total_val);

gs.log('total count         :' + total_count);

final_avg =+ parseFloat(total_val) / parseInt(total_count);

gs.log('final_avg:     ' + final_avg);

Hope this helps.