How to compare Date to find response time?

Hi, Team…
I really need help… :sob::sob::sob:

In SLA perspective, my team want to count ticket which respose time is within 30 minutes(1/48 day).
There are two criteria: “First Comment time” and the “First StatusUpdate time from New to In Progress”.

That means,
I would like to set Response Time measure by comparing it and pick less taking time among the two criteria.

To do this,
I try to make “First Comment Time” custom field by using Advanced settings.

[jira.customfield_firstcommentdate]
name = "First comment date"
data_type = "datetime"
javascript_code = '''
var comments = issue.fields.comment.comments;
if (comments.length > 0) {
var comment = comments[0];
issue.fields.customfield_firstcommentdate = comment.created;
}
'''

Here is my 2 questions.

Q1) Can I count “First StatusUpdate time from New to In Progress”?
How can I do?

Q2) If Q1 is possible, how can I compare two criteria - First Comment time / First StatusUpdate time from New to In Progress.

I just make Respose Time Measure only with First Comment time, but I don’t know how to modify…

Please give me helplful advice.

[Measure][~30min Response Time (Urgent)]

 NonZero(Count(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod([Issue].CurrentHierarchyMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
        ([Measures].[Issues created],
         [Time].CurrentHierarchy.DefaultMember) > 0 AND
      DateDiffDays(
        [Issue].CurrentHierarchyMember.get('Created at'),
       [Issue].CurrentHierarchyMember.get('First comment date')) <= (1/48) AND
      [Measures].[Issue type] = [Issue Type].[Urgent Request ].Name
    )
  ))

Hi @sujikim

In general, the first status update date for a particular transaction can be found from measure “Transition to status first date” and a particular transition.

For your calculation, you may want to use function Min() to find the first date from the two dates - first comment and this first status update date.
To use this function, you need to use your two dates as timestamps (it is integer value).

Do the following:

  1. Create a measure that calculates first transition date and converts it to timestamp (lets name this measure “Transition to In Progress timestamp”, we need it later):
DateToTimestamp(
 ([Measures].[Transition to status first date],
 [Transition].[new => In Progress],
 [Time].CurrentHierarchy.DefaultMember)
)
  1. Create a new measure to convert the first comment date to timestamp (name it “First comment timestamp”)
DateToTimestamp(
 [Issue].CurrentHierarchyMember.get('First comment date')
)
  1. Now, modify the general calculation:
  • use Sum() instead of Count() (it is faster);
  • add a condition by issue type before measures. Notice! It seems, you have a empty space after the issue type name, could it be so? Check it and use correctly in the calculation.
  • use DateDiffMinutes() function instead of DateDiffDays();
  • use this Min() function to find the minimal date from the two, convert the result back to date, and then use it in the calculation of the duration.
Sum(
    Filter(
      Descendants([Issue].CurrentMember, [Issue].[Issue]),
      DateInPeriod([Issue].CurrentHierarchyMember.get('Created at'),
        [Time].CurrentHierarchyMember) AND
      [Measures].[Issue type] = "Urgent Request " AND
      DateDiffMinutes(
        [Issue].CurrentHierarchyMember.Get('Created at'),
        TimestampToDate(Min(
            {[Measures].[First comment timestamp],
             [Measures].[Transition to In Progress timestamp]},
            [Measures].CurrentMember))
      )<=30
    ),
  ([Measures].[Issues created],
   [Time].CurrentHierarchy.DefaultMember))

Hope, it gives you the needed result!

Best,
Ilze , support@eazybi.com