I have a Jira Project that requires tickets to be completed by due dates. We consider a breach of due date to be a breached SLA. I’ve tried a few different ways to show this with what I have available in our cubes, but was curious if anyone else had any ideas.
What I’m looking for:
I would like to be able to show tickets that are resolved later than their due date. A cumulative total and percentage of issues.
Any help would be greatly appreciated as I’m fairly new to this role. Thanks!
Hi @jbran00,
Welcome to the eazyBI community!
There are several ways of finding the relevant issues.
One of the options is to iterate through the Issue dimension and check each issue to see if it has met or breached the SLA. The main drawback for this approach is that this calculation might get slow on larger instances with a high number of issues.
The expression to find the number of issues breaching SLA might be as follows.
Sum(
Filter(
--set of issues
DescendantsSet(
[Issue].CurrentHierarchyMember,
[Issue].[Issue]),
--conditions for filter
--conditon 1 - check relevance against the report context
[Measures].[Issues resolved]>0
AND
--condition 2 - resolved after due date
DateCompare(
--dropping the resolution date time part before comparison so the issue resolved in afternoon is still within due day
DateWithoutTime(
[Issue].CurrentHierarchyMember.Get('Resolved at')),
[Issue].CurrentHierarchyMember.Get('Due date'))>0
),
--numeric value for sum
[Measures].[Issues resolved]
)
An alternative option is to create a JavaScript-calculated custom field dimension that finds the issue SLA category upon data import.
You might define a new field in the source data tab as described here - New calculated fields.
The Javascript code might then be as follows.
if( issue.fields.resolutiondate &&
issue.fields.duedate ) {
var res = new Date(issue.fields.resolutiondate).setHours(0,0,0,0);
var due = Date.parse(issue.fields.duedate);
if(due <= res ){return "Met";}
else{return "Breached";}
}
else
if(issue.fields.duedate) {return "Pending";}
else {return "Undefined";}
This would allow to import the new dimension with the following values:
You might then use the following construction to see the percentage of SLA specifically met issues.
([Measures].[Issues resolved],
[SLA status].[Met])
/
[Measures].[Issues resolved]
Regards,
Oskars. / support@eazyBI.com
PS. JS code updated with necessary corrections.
Thank you Oskars!!
I am running into an issue with res = res.setHours(0,0,0,0);. It keeps throwing an error for setHours. Am I missing something?
Hey Oskars!!
Just wanted to let you know that I figured this out!
function epoch (date) {
return Date.parse(date)
}
if (issue.fields.resolutiondate && issue.fields.duedate) {
var res = Date.parse(issue.fields.resolutiondate);
var due = Date.parse(issue.fields.duedate);
if (!isNaN(res) && !isNaN(due)) {
res = new Date(res).setHours(0, 0, 0, 0);
if (due > res) {
return "Met";
} else {
return "Breached";
}
}
} else if (issue.fields.duedate) {
return "Pending";
} else {
return "Undefined";
}
Hi @jbran00,
Great to see you got through it!
Usually, the due date is set as “until the end of day”.
However, the resolution date (time) comes together with the time component. The setHours(0,0,0,0) part removes the time component and leaves the pure date only.
You might consider whether the issue resolved ON the due day is considered “met”.
If it is considered breached (over the specific timestamp) - you might as well remove the .setHours() part.
If it is considered met, you might update the comparison condition to the following.
if (due >= res)
That will make the issue resolved on the due date to be considered met.
Regards,
Oskars / support@eazyBI.com