Percentage calculation

[NigelUnderwood]

This is a very simple usecase that we don't seem to be able to deliver which relates to percentages.

The example is: -

What percentage of tickets logged in a period are resolved before the first SLA control point is breached?

(total calls closed at CP1/Total Calls logged * 100)

Because this is a calculated field it is not held on the database so we cant report on it!

Are there any ideas or recommendations that we should consider?

[jfarrer]

We had a similar need and ended up creating a custom UI Page. Not the simplest solution but we've made several reports that way and it has worked well. It has allowed us to get very specific in how we format things.

[dave_m13]

Hi

I wonder whether you could share an example of what you have done.

Many Thanks

[jfarrer]

Here are a couple of screenshots, the top one is relatively simple and I believe it is close to what you need. The bottom one is significantly more complex and is mostly just an example of what can be done.

Here is the Jelly Script code for the top one. I glanced through it and it is pretty generic so there's only a couple places you would need to edit to make work for your instance. Mostly just with the goals and to include Priority 5 if you use it.

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<style>
table, table tr, table td, table th {
border-style: solid;
border-color: #444;
border-width: 1px;
border-collapse: collapse;
}

tr.group_row, tr.group_row td {
background-color: #EEE;
font-weight: bold;
text-align: left;
padding-top: 5px;
}

tr.group_totals_row, tr.group_totals_row td {
background-color: #EEE;
font-weight: bold;
}

table tr td {
text-align: right;
}

table th {
background-color: #ccc;
}

tr#total_row {
background-color: #CCC;
font-weight: bold;
}

th.thick_right, td.thick_right {
border-right-width: 5px;
}

tr.over_slo td, tr.over_solo td a, tr.over_solo td a:visited {
background-color: #FF0000;
color: #FFF !important;
}

a.over_slo, a.over_slo:visited {
color: #FFFFFF !important;
}

</style>
<g:evaluate>
var total_tickets = 0;
var total_out_of_slo = 0;
var slo_goal = [4, 24, 48, 120];
var goal_percentages = [90, 90, 80, 70];
</g:evaluate>
<h1>Tickets by SLO</h1>
<table>
<tr>
<th>Priority</th>
<th>Tickets</th>
<th>Out of SLO</th>
<th>Percent in SLO</th>
<th>Goal Percentage</th>
<th>Goal Hours</th>
</tr>
<j:set var="jvar_curr_priority" value="1"/>
<j:while test="${jvar_curr_priority != 5}">
<g:evaluate jelly="true">
var tickets_by_priority = new GlideRecord("incident");
tickets_by_priority.addActiveQuery();
tickets_by_priority.addQuery("priority", jelly.jvar_curr_priority);
tickets_by_priority.query();

var tickets = tickets_by_priority.getRowCount();
total_tickets = total_tickets += tickets;

var out_of_slo = 0;
while (tickets_by_priority.next()) {
if (gs.hoursAgo(slo_goal[jelly.jvar_curr_priority-1]) > tickets_by_priority.sys_created_on) {
out_of_slo++;
}
}

total_out_of_slo += out_of_slo;

// calculated fields
var percent_in_slo = parseInt((tickets - out_of_slo) / tickets * 100);
var goal_percentage = goal_percentages[jelly.jvar_curr_priority-1];
var goal_hours = slo_goal[jelly.jvar_curr_priority-1];
var over_slo = ""

if (goal_percentage > percent_in_slo) {
over_slo = "over_slo";
}
</g:evaluate>
<tr class="${over_slo}">
<th>${jvar_curr_priority}</th>
<td>
<a class="${over_slo}" href="/incident_list.do?sysparm_query=active%3Dtrue^priority%3D${jvar_curr_priority}$[AMP]sysparm_view=Support">
${tickets}
</a>
</td>
<td>
<a class="${over_slo}" href="/incident_list.do?sysparm_query=active%3Dtrue^priority%3D${jvar_curr_priority}^sys_created_on%3Cjavascript%3Ags.hoursAgo(${goal_hours.toString()})$[AMP]sysparm_view=Support">
${out_of_slo.toString()}
</a>
</td>
<td>${percent_in_slo.toString()}%</td>
<td>${goal_percentage.toString()}%</td>
<td>${goal_hours.toString()} hours</td>
</tr>
<j:set var="jvar_curr_priority" value="${jvar_curr_priority + 1}"/>
</j:while>
<tr>
<th>Total</th>
<th>
<a href="/incident_list.do?sysparm_query=active%3Dtrue$[AMP]sysparm_view=Support">
${total_tickets.toString()}
</a>
</th>
<th>
<a href="/incident_list.do?sysparm_query=active%3Dtrue^priority%3D1^sys_created_on%3Cjavascript%3Ags.hoursAgo(4)^NQactive%3Dtrue^priority%3D2^sys_created_on%3Cjavascript%3Ags.hoursAgo(24)^NQactive%3Dtrue^priority%3D3^sys_created_on%3Cjavascript%3Ags.hoursAgo(48)^NQactive%3Dtrue^priority%3D4^sys_created_on%3Cjavascript%3Ags.hoursAgo(120)$[AMP]sysparm_view=Support">
${total_out_of_slo.toString()}
</a>
</th>
<th>
${parseInt((total_tickets-total_out_of_slo)/total_tickets * 100).toString()}%</th>
<td></td>
<td></td>
</tr>
</table>
</j:jelly>

[dave_m13]

James

Thank you very much for posting this, I think this may be what we are looking for. I actually think the bottom is something we are trying to achieve as well, well the duration part anyway, though I think that we want specific days/hours/minutes etc..

Thanks
Dave