Querying To Get Two Values from Same Table and Divide and Get Percentage

[Community Alums]

Hi There,

Greetings!!!

I have a requirement to Get two values from same table and divide and get percentage in a Service Portal:

I am trying to calculate total number of courses assigned to divide by completed courses.

In below code firstly, I am fetching completed courses from the table and then total number of courses, storing it in data.complcount and data.totalcount respectively and in data.perc calculating the percentage after that I want to pass the percentage to HTML. 

Please let me know how this can be achieved.

Server script :

data.complcount=parseInt();

data.totalcount=parseInt();

data.perc=parseInt((data.complcount/data.totalcount)*100);

var user1 = gs.getUserID();

var ComplCourse=new GlideRecord("x_infte_diversity_course_task_for_registered_user");

ComplCourse.addEncodedQuery('course_selected=535e228e478bfd1088119ac5536d43b7^assignment_status=Completed');

ComplCourse.addQuery('assigned_to.sys_id', user1);

ComplCourse.query();

var ccount = ComplCourse.getRowCount();

gs.info('Completed Count is :' +ccount);

  data.complcount=ccount;

var Course=new GlideRecord("x_infte_diversity_course_task_for_registered_user");

Course.addEncodedQuery('course_selected=535e228e478bfd1088119ac5536d43b7');

Course.addQuery('assigned_to.sys_id', user1);

//Course.addQuery('selected_user.sys_id', user1);

Course.orderBy('week_number');

Course.query();

var count = Course.getRowCount();

data.totalcount=count;

gs.info('Count is :' +count);

HTML :

Completed Percentage : {{data.perc}}

Thanks,

Tejas

[Community Alums]

Thanks @Saurabh Gupta  , it's working 

[Aniket Chavan]

Hello @Community Alums ,

Please refer the below revised script.

var user1 = gs.getUserID();

// Fetch completed courses
var complCourse = new GlideRecord("x_infte_diversity_course_task_for_registered_user");
complCourse.addEncodedQuery('course_selected=535e228e478bfd1088119ac5536d43b7^assignment_status=Completed');
complCourse.addQuery('assigned_to.sys_id', user1);
complCourse.query();
var complCount = complCourse.getRowCount();
gs.info('Completed Count is: ' + complCount);

// Fetch total courses
var course = new GlideRecord("x_infte_diversity_course_task_for_registered_user");
course.addEncodedQuery('course_selected=535e228e478bfd1088119ac5536d43b7');
course.addQuery('assigned_to.sys_id', user1);
course.orderBy('week_number');
course.query();
var totalCount = course.getRowCount();
gs.info('Count is: ' + totalCount);

// Check for potential division by zero
if (totalCount === 0) {
    gs.info('Total count is zero. Cannot calculate percentage.');
    data.perc = 0; // or set it to any default value you prefer
} else {
    // Calculate percentage
    var percentage = (complCount / totalCount) * 100;

    // Set data variables
    data.complcount = complCount;
    data.totalcount = totalCount;
    data.perc = parseInt(percentage);
}

// Pass the percentage to HTML

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

Thanks,

Aniket

[RikV]

What are your gs.info's returning for the ccount and count? Are they returning actual numbers?

I suspect one of them is 0 or "undefined".

Just for testing purpose you could use another way to check if you actually even get records back by doing:

var ccount=0
while ComplCourse.next() {
ccount++;
}
gs.info("Completed count is:" + ccount);

Its also worth a try to edit your variable declaration with || 0 at the end to make sure it returns a value, and not "undefined" for whatever reason.

// for complCount variable:
var complCount = complCourse.getRowCount() || 0;

for totalCount variable:
// for complCount variable:
var totalCount = course.getRowCount() || 0;

Let me know what your results are with above debugging.