How to get average of all scores of all assessments for tasks

[akshayp]

im trying following code in background script, so whats i want is to get the average of all score of all assessments related to one task

Currently i have hardcoded query, so for that task i have two assessments 

so what i want is score of 1st added to score of 2nd divided by 2 in this manner i want that

But currently im getting score of only latest assessment pasting code below please have look at it

And it as urgent for me so anyone who can edit the script accordingly help me out ASAP

var arr_cat = [];
var count = 0;

var gi = new GlideRecord('asmt_assessment_instance');
gi.addEncodedQuery('task_id.sys_class_name=x_ubte_ethics_an_0_ethics_compliance_risk_assessment_campaign^state=complete');
gi.addEncodedQuery('task_id=a98bae491b54e590c1cb4197dc4bcb7c');
gi.query();
while (gi.next()) {
var rec1 = new GlideRecord('asmt_assessment_instance_question');
rec1.addEncodedQuery('instance.sys_id= ' + gi.sys_id);
rec1.orderBy('category');
rec1.query();
while (rec1.next()) {

if (arr_cat.indexOf('' + rec1.category) === -1) {
arr_cat.push('' + rec1.category);
count = count + 1;
}
}

var total_sum = 0;

for (var i = 0; i < count; i++) {
var tempmult = 1;
var rec2 = new GlideRecord('asmt_assessment_instance_question');
rec2.addEncodedQuery('instance.sys_id= ' + gi.sys_id);
rec2.addEncodedQuery('category=' + arr_cat[i]);
rec2.query();
while (rec2.next()) {
tempmult = parseInt(rec2.value) * parseInt(tempmult);
}

total_sum = total_sum + tempmult;
}

}
gs.print(total_sum);

[Taha Habib · Accepted solution]

Hello Akshayp,

Can you try the following script as a template to achieve your requirement. Here I am doing a glideRecord to Assessment Instances and then while I am in the loop I am counting the no of Assessment record. After that I am doing a GlideRecord to the Assessment questions in each of them and then summing the values. And dividing the total sum of value outside of all loop by the no of assessments.

var valuecount=0;
var nocountofAssesments=0;
var gi = new GlideRecord('asmt_assessment_instance');
gi.addEncodedQuery('number=AINST0000501^ORnumber=AINST0000502');
gi.query();
while (gi.next()) {
nocountofAssesments++;
var rec1 = new GlideRecord('asmt_assessment_instance_question');
rec1.addQuery('instance', gi.sys_id);
rec1.query();
while (rec1.next()) {

   
valuecount+=rec1.value;


}

}
 var finalsum= valuecount/nocountofAssesments;
gs.info(finalsum);

This is the ss of the assessments I am using in default PDI:

I hope this Information helps you, if you find this helpful please mark this as helpful and correct. Thank you.

[akshayp]

ya that worked for me, thanks so much!!