how to check average of a field based on year

[sony8]

Hi,

I have a scenario as below.

We have a custom table,

we need to calculate average of ABC field based on year field.

 var gr = new GlideRecord('x_itsm.Rating');
        gr.addQuery('employee_number', current.employee_number);
        gr.query();
        if (gr.next()) {
// need to calculate  the rating of past 2 years
 
         Avg(gr.year-1 ,gr.year-2) //for field Rating we need to check average
           }

I am not understanding how to pull average of rating based on last 2 year using below script.

Reference table

employee_number	year	rating
123	2022	3
123	2021	4
123	2020	3
124	2022	2
124	2021	3
124	2020	3

target table

employee_number	Rating(Avg of past 2 years)
123	3
124	3

Please help me how to calculate avg of rating.

[Danish Bhairag2]

Hi @sony8 ,

Can u try the below code n check. Please use proper backend names of the variable.

// Assuming you are in the context of a script on the target table

var employeeNumber = current.employee_number;

var currentYear = current.year;

// Calculate the average rating for the past two years

var averageRating = calculateAverageRating(employeeNumber, currentYear - 1, currentYear - 2);

// Set the calculated average rating to the target field

current.Rating = averageRating;

// Function to calculate the average rating for the past two years

function calculateAverageRating(employeeNumber, year1, year2) {

    var gr = new GlideRecord('x_itsm.Rating');

    gr.addQuery('employee_number', employeeNumber);

    gr.addQuery('year', 'IN', [year1, year2]);

    gr.query();

    var totalRating = 0;

    var ratingCount = 0;

    while (gr.next()) {

        totalRating += gr.rating;

        ratingCount++;

    }

    // Avoid division by zero

    return ratingCount > 0 ? total

Rating / ratingCount : 0;

}

Thanks,

Danish

[sony8]

@Danish Bhairag2 

I have written after update business rule on my table.

The rating is not updating in my table.

(function executeRule(current, previous /*null when async*/ ) {
    // Add your code here
    var employeeNumber = current.employee_number;
    var currentYear = current.year;
    // Calculate the average rating for the past two years
    var averageRating = calculateAverageRating(employeeNumber, currentYear - 1, currentYear - 2);
    // Set the calculated average rating to the target field
    current.Rating = averageRating;
    // Function to calculate the average rating for the past two years
    function calculateAverageRating(employeeNumber, year1, year2) {
        var gr = new GlideRecord('my_reference_table');
        gr.addQuery('subject_employee_id', employeeNumber);
        gr.addQuery('year', 'IN', [year1, year2]);
        gr.query();
        var totalRating = 0;
        var ratingCount = 0;
        while (gr.next()) {
            totalRating += gr.rating;
            ratingCount++;
        }
// added alert also getting value as 0.0
        // Avoid division by zero
        return ratingCount > 0 ? totalRating / ratingCount: 0;
    }


})(current, previous);

[Danish Bhairag2]

Hi @sony8 ,

Can u put some logs n let me know the output? 

Like logs for year1 & Year2 employee number

 are those values proper as per ur reference table

Dont use alert as it is business rule server side it wont work

Thanks,

Danish