Issues closed over 48 hours since last assigned by transition author

Questions & Answers
1

I’m looking to create a report that displays the number of issues an individual closed in a period of time, and how many of those issues were closed 48 hours after being assigned (the assignee will usually be different than the person working on the ticket).

So far I’ve only been able to find the number of times an Author Transitioned issues to closed. Is there a way to display how many of those issues were closed over 48 hours since the last time the assignee field was changed?

This is all I have so far:

Transition Author Sample
Transition Author Sample942×545 24.3 KB

Any and all help would be appreciated. Thank you!

2

Hi @NotaryMaverick,

A warm welcome to the eazyBI community :partying_face: !

To consider two dates from an issue’s history, eazyBI must iterate through these issues with the Descendants() function. That can affect the report performance.

To calculate the difference between the last assignee change and the last transition to the closed status, you can use the DateDiffHours() function. Finally, the Sum() function could sum up the number of issues for each Transition Author member. Please see the suggested formula below:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateDiffHours(
      -- issue last assigned
      TimestampToDate((
        [Measures].[Transition to last timestamp],
        [Transition Field].[Assignee],
        [Time].CurrentHierarchy.DefaultMember,
        [Transition Author].CurrentHierarchy.DefaultMember
      ))
      ,
      -- issue closed by author
      ([Measures].[Transition to status last date],
      [Transition Status].[Done],
      [Time].CurrentHierarchy.DefaultMember)
    ) <= 48
  ),
  ([Measures].[Transitions to status issues count],
  [Transition Status].[Done])
)

Update the Transition Status dimension member to fit your use case. The formula part calculating the last date the assignee changed ignores the current Transition Author dimension member. Thus, it considers issues whose assignee was altered by any user. If the current Transition Author dimension member must be the one who changed the assignee, remove the [Transition Author].CurrentHierarchy.DefaultMember part from the formula.

Visit the eazyBI documentation page for more details about the mentioned functions - MDX function reference.

Best,
Roberts // support@eazybi.com

3

Thank you @roberts.cacus. I made a small change to the calculation to only show issues over 48 hours, but I also need it to take into consideration issues that were reopened. For example, I have a ticket that comes up even though it was closed about 2 hours after being reopened.

image
image1406×587 36.2 KB

Is there a way to sum up issues closed over 48 hours after being assigned, or 48 hours after being reopened (assuming the task was reopened)?

4

Hi @NotaryMaverick ,

Unfortunately, I can’t come up with anything better than another condition inside the Filter() function. The combined requirement formula could look similar to the one below:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    IIf(
      -- issue reopened?
      ([Measures].[Transitions to status],
      [Transition].[Done => In Progress],
      [Time].CurrentHierarchy.DefaultMember,
      [Transition Author].CurrentHierarchy.DefaultMember) > 0,
      -- YES, reopened
      DateDiffHours(
        -- issue last reopened
        (
          [Measures].[Transition to status last date],
          [Transition].[Done => In Progress],
          [Time].CurrentHierarchy.DefaultMember,
          [Transition Author].CurrentHierarchy.DefaultMember
        )
        ,
        -- issue closed by author
        ([Measures].[Transition to status last date],
        [Transition Status].[Done],
        [Time].CurrentHierarchy.DefaultMember)
      ),
      -- NO
      DateDiffHours(
        -- issue last assigned
        TimestampToDate((
          [Measures].[Transition to last timestamp],
          [Transition Field].[Assignee],
          [Time].CurrentHierarchy.DefaultMember,
          [Transition Author].CurrentHierarchy.DefaultMember
        ))
        ,
        -- issue closed by author
        ([Measures].[Transition to status last date],
        [Transition Status].[Done],
        [Time].CurrentHierarchy.DefaultMember)
      ) 
    ) > 48
  ),
  ([Measures].[Transitions to status issues count],
  [Transition Status].[Done])
)

The formula relies on the Transition dimension to determine the reopened issues. In my case, it is the transition from status “Done” to “In Progress”, indicated by the expression [Transition].[Done => In Progress].

I recommend first creating a separate calculated measure to find the re-opened issues and verifying the results.

Best,
Roberts // support@eazybi.com

1 Like
5

Thank you @roberts.cacus. I updated it to reflect work hours then tested it thoroughly and it seems to give me exactly what I was looking for. Thank you very much for your assistance!

1 Like