Out of SLA based on comment updated date

Questions & Answers
1

I am trying to look for tickets that are in a particular status for more than 2 days without any new comment.
For example- Ticket in “Under investigation” status for 2 continuous days without any latest comment tells there has been no progress in the ticket. How can I write a measure for this ?

2

Hi @sunray2003 ,
First, you need to import a new JavaScript field to get the “Latest comment date”, add this code in eazyBI advanced settings and import the new field:

[jira.customfield_lastcommentdate]
name = "Latest comment date"
data_type = "datetime"
json_fields = ["comment"]
javascript_code = '''
var comments = issue.fields.comment.comments;
if (comments.length > 0) {
  var comment = comments[comments.length - 1];
  issue.fields.customfield_lastcommentdate = comment.created;
}
'''

See more here: JavaScript calculated custom fields

After you have imported the new field, you can create this type of report in eazyBI:

  1. Add “Issue” dimension at issue level in Rows;
  2. Add measures “Issues created”, “Latest comment date”, “Issue status updated date”;
  3. Add “Status” dimension in Pages and filter the status “Under investigation”
  4. Then create a new custom measure in “Measure” dimension using this formula that will return if the issue need attention:
CASE WHEN
--compare if latest comment date was before status update date
(DateCompare(
  [Measures].[Issue status updated date],
  [Measures].[Issue Latest comment date]
  )>0
OR 
--check if the latest comment date is empty
IsEmpty([Measures].[Issue Latest comment date]))
--compare status update date with now, if it is greater than 2
AND
DateDiffDays(
  [Measures].[Issue status updated date],
  now()
  )>2
THEN
'need attention'
END

Or you can create a measure that counts those issues using this formula:

Sum(
  Filter(
    DescendantsSet(
      [Issue].CurrentHierarchyMember, [Issue].CurrentHierarchy.Levels("Issue")
    ),
    --compare if latest comment date was before status update date
    (DateCompare(
      [Measures].[Issue status updated date],
      [Measures].[Issue Latest comment date]
      )>0
    OR 
    --check if the latest comment date is empty
    IsEmpty([Measures].[Issue Latest comment date]))
    --compare status update date with now, if it is greater than 2
    AND
    DateDiffDays(
      [Measures].[Issue status updated date],
      now()
      )>2
  ),
  [Measures].[Issues created]
)

In the report:

Latest_comment_-_eazyBI
Latest_comment_-_eazyBI1586×1446 172 KB

best,
Gerda // support@eazybi.com