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

[Aniket Chavan]

Hello @Community Alums ,

Please refer the below modified code and let me know how it works for you.

server 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);

// 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

HTML :

Completed Percentage : {{data.perc}}

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

[Community Alums]

Hi Aniket,

Thanks for your reply.

I tried your code, getting "Completed Count is :NaN". I have put "gs.info('Completed Count is :' +ccount);" in code.

Thanks,

Tejas

[Amit Verma]

Hi @Community Alums 

NaN represents a division by 0 is tried. From this, it is evident that total count is coming out to be 0. Can you please tell what is the output you got from gs.info('Completed Count is :' +ccount); ?

Thanks & Regards

Amit Verma

[Saurabh Gupta · Accepted solution]

var user1 = gs.getUserID();

// Fetch completed courses
var complCount =0;
var complCourse = new GlideAggregate("x_infte_diversity_course_task_for_registered_user");
complCourse.addEncodedQuery('course_selected=535e228e478bfd1088119ac5536d43b7^assignment_status=Completed^assigned_to='+user1);
complCourse.addAggregate("COUNT");
complCourse.query();
if(complCourse.next())
complCount = parseInt(complCourse.getAggregate("COUNT"));

// Fetch total courses
var totalCount = 0;
var course = new GlideAggregate("x_infte_diversity_course_task_for_registered_user");
course.addEncodedQuery('course_selected=535e228e478bfd1088119ac5536d43b7^assigned_to='+user1);
course.addAggregate("COUNT");
course.query();
if(course.next())
totalCount = parseInt(course.getAggregate("COUNT"));


// Calculate percentage
var percentage=0;
if(totalCount)
percentage =parseFloat (complCount / totalCount) * 100;


// Set data variables
data.complcount = complCount;
data.totalcount = totalCount;
data.perc = percentage;

// Pass the percentage to HTML

HTML :

Completed Percentage : {{data.perc}}

C	If the provided solution meets your needs, kindly consider marking it as helpful and accepting it as the solution. This helps others who may have similar questions.