How to calculate Median score of the records using PA Indicator?

[Rohit8]

Hello,

Is there a way to calculate the Median score for a particular set of records. The Analytics Hub in Madrid does show the Median there but I am trying to find a way to display it as as a score widget on the dashboard.

Thank you.

[Rohit8]

We were not able to achieve it with PA options. So we created a manual indicator which is populated by a scheduled daily job (that calculates the median for the set of records).

This is the Script Include for calculating Median, we are calculating the median of all records and also for different Business Units (Breakdown elements), so you can edit it per your requirements.

var SLO_MedianElapsedTime_GBUs = Class.create();
SLO_MedianElapsedTime_GBUs.prototype = {
	initialize: function() {
	},
    medianCalculation: function(filter,breakdownGbu,elementGbu,indicatorSLO) {
        var filterGbu = filter;
        var med = new GlideRecord('task_sla');
        med.addEncodedQuery(filterGbu);
        med.orderBy('duration');
        med.query();

        var result;

        var numbers = [];
        while (med.next()) {
            numbers.push(new GlideDateTime(med.duration).getNumericValue());
        }
        median(numbers);

        function median(numbers) {
            // median of [3, 5, 4, 4, 1, 1, 2, 3] = 3
            var median = 0,
                numsLen = numbers.length;
            if (
                numsLen % 2 === 0 // is even
            ) {
                // average of two middle numbers
                median = (numbers[numsLen / 2 - 1] + numbers[numsLen / 2]) / 2;
            } else { // is odd
                // middle number only
                median = numbers[(numsLen - 1) / 2];
            }
            result = median / 86400000;
        }

		var indicator = indicatorSLO;
        var breakdown = breakdownGbu;
        var element = elementGbu;
        var value = result;

        var date = new GlideDate();
        date.setDayOfMonthUTC(1);
        var start = parseInt(date.toString().replace('-', ''));

        var score = new GlideRecord('pa_scores');
        score.addQuery('indicator', indicator);
        score.addQuery('breakdown', breakdown);
        score.addQuery('element', element);
        score.addQuery('start_at', start);
        score.query();
        if (score.next()) {
            score.setWorkflow(false);
            if (isNaN(value)) {
                value = 0;
            }
            score.value = value;
            score.update();

        } else {
            score.initialize();
            score.indicator = indicator;
            score.breakdown = breakdown;
            score.element = element;
            score.start_at = date.getByFormat("dd-MMM-yy");
            if (isNaN(value)) {
                value = 0;
            }
            score.value = value;
            score.insert();

        }
    },

    type: 'SLO_MedianElapsedTime_GBUs'
};

We calculate the median for last 3 months running SUM of records, and here's the excerpt from Scheduled job, that uses it:

var collmonths = new GlideDateTime();
collmonths.addMonths(-2);
collmonths.setDayOfMonthUTC(1);
var start = collmonths.getDate();
var breakdownGbu;
var elementGbu;
var indicator = 'e12d09051b5e549a49cc4bcb88';

var all = new SLO_MedianElapsedTime_GBUs();
var filterall = "sla=180893e5db9b7fc4974517619b7^stage=completed^end_time>=javascript:gs.dateGenerate('" + start + "','00:00:00')";

all.medianCalculation(filterall, breakdownGbu, elementGbu, indicator);

var filtercorp = "sla=180893e5db9b7fc497451719b7^stage=completed^end_time>=javascript:gs.dateGenerate('" + start + "','00:00:00')^task.ref_incident.caller_id.u_bechtel_default_job.u_itfm_job_number.u_gbu=ab0a69496fb48f002ea3bcee431^NQsla=180893e5db9b7fc4974517619b7^stage=completed^end_time>=javascript:gs.dateGenerate('" + start + "','00:00:00')^task.ref_incident.caller_id.u_default_job.u_itfm_job_number.u_gbuISEMPTY^task.ref_incident.caller_id.manager.u_default_job.u_itfm_job_number.u_gbu=ab0a69496fb48f002ea3bee431";

breakdownGbu = '5b280732dbdbdfc0914cf3331d9';
elementGbu = 'ab0a69496fb48f002ea3bc775b31';

all.medianCalculation(filtercorp, breakdownGbu, elementGbu, indicator);

This worked for us, hope it helps you.

[Swadesh Saraf2]

Thanks Rohit, this will definitely help me. I have a few doubts while implementing this though:

breakdownGbu - sys_id of the breakdown created

elementGbu - This one I am not sure. If this is one value in the business unit table, how can we use this for breakdown? I am very new to PA and not very much familiar with the terminologies in it.

indicatorSLO - sys_id of the manual indicator created

Can you please confirm on the above?

I have a better script for calculating the median, i will look for it and post it soon.

[Rohit8]

Yes, indicatorSLO is the manual indicator here, breakdownGBU is the sys_id of the breakdown (Example - Incident Priority), elementGBU is the sys_id of the breakdown elements (Examples - Priority 1, Priority 2, Priority 3), the ones that are mapped in the related breakdown source.

In the scheduled job, we had to map the breakdown (it will remain the same) and the elements (each) one by one.

so this one below gives us the aggregate Median score of all BUs.

var all = new SLO_MedianElapsedTime_GBUs();
var filterall = "sla=180893e5db9b7fc4974489619b7^stage=completed^end_time>=javascript:gs.dateGenerate('" + start + "','00:00:00')";

while this one is to get the Median score of a BU named Corporate. So in the filtercorp variable with encoded query, we have to edit the query so that it only looks for the ones from Corporate GBU (u_gbu = ab069496f.......)

all.medianCalculation(filterall, breakdownGbu, elementGbu, indicator);

var filtercorp = "sla=180893e5db9b7fc4974519b7^stage=completed^end_time>=javascript:gs.dateGenerate('" + start + "','00:00:00')^task.ref_incident.caller_id.u_default_job.u_itfm_job_number.u_gbu=ab0a69496fb48f002ea3e431^NQsla=180893e5db9b7fc4974517b7^stage=completed^end_time>=javascript:gs.dateGenerate('" + start + "','00:00:00')^task.ref_incident.caller_id.u_default_job.u_itfm_job_number.u_gbuISEMPTY^task.ref_incident.caller_id.manager.u_default_job.u_itfm_job_number.u_gbu=ab0a69496fb48f002ea3b1";

breakdownGbu = '5b280732dbdbdfc0914cf39d9';
elementGbu = 'ab0a69496fb48f002ea31';

all.medianCalculation(filtercorp, breakdownGbu, elementGbu, indicator);

I have not pasted the rest of this script , but it basically contains this same part reiterated with "elementGBU" and "u.gbu" containing values for each BU one by one.

I'd love the find out if there is a better and less complicated way to find the median. Thanks.

[Eddie5]

Hi Rohit,

I read your answer on calculating median value, however i am very new to servicenow and wanted to calculate median value for incident onhold duration, can you please take sometime to explain how to script it? also can you please help using a screenshots of how these reports are setup?

[Duncan Pedersen]

Just an update here, we tried the above code for manually inserting pa_score records in Quebec, and we had to set the date using the following function. Otherwise we got an invalid date error. 

score.start_at = SNC.PAScore.normalizeStartAt(indicator, start);