Average workdays in transition status, by user and another custom issue property (~issue category)

Hi!

I would like to know if it’s possible to build a report that shows "average workdays ‘assignee’ users spend on issues under specific status XYZ, grouped by an imported jira custom property ABC which may have up to 4 different values (let’s say issue of Category 1, 2, 3 or 4). I basically need to know how much time some group of users spend working on our team tickets, for each user, based on a specific category of ticket selected (custom jira field). For us, the status is what determines what group of users/team is currently working on the ticket, so we want to use the status as the ‘trigger’ to start/stop the clock and then derive the average time on that status, for each assignee.

For example, if I assign a jira issue to user ‘John’, set the ‘ABC’ jira custom attribute to drop-down value ‘Category 2’, and update at the same time the issue status to let’s say “Review In Progress”, then the clock should start. If ‘John’ then re-assign 2 working day later to another member of his team let’s say ‘Paula’, but that the status remains on their team (aka ‘Product Review’), the clock should stop on ‘John’, but it should now start to count against the new user ‘Paula’. Finally, once ‘Paula’ answers the questions etc, and update the issue status to let’s say “Review Completed” after 4 working days, then the clock should stop on her.

The week after, exact same situation happens (still issue of category 2), but this time, John takes 4 working days to re-assign to Paula, and Paula takes 6 days to answer.

The week after, exact same situation happens, but this time, the ticket is on a different category 3, John takes 1 day to re-assign to Paula, and Paula takes 7 days to answer and update status to Review Completed, to stop the clock (…from status “Review In Progress”).

Also, it would be nice to be able to filter, by time (e.g. last month, last 12 months / current year, etc)

REPORT/TABLE EXPECTATION:

[ Project = …xyz…] [Time = Current Month]
Assignee | Issue Custom Category | Average Workdays in transition status


John | Category 2 | 3 …(2d from 1st issue +4d from 2nd issue / 2 issues of cat 2 that John was assigned)
John | Category 3 | 1 …(1 / 1)
Paula | Category 2 | 5 …(4+6 / 2)
Paula | Category 3 | 7 …(7 / 1)

Is this sort of grouping/report feasible, EazyBI team? Hopefully this is clear enough…:slight_smile:
thanks!

Hi @frank
Welcome to the eazyBI community!

Indeed, you have a tricky question to solve.
By default, there is no default measure that would retrieve this information. Measure “Average workdays in transition status” count all days in the selected status to the assignee who was the issue assignee during the transition from the status.

You may want to think about the calculated JavaScript custom field. While I do not have an example that would solve your problem, there is an example of how to assess how many days the issue was assigned to each assignee (Days for assignee) and it could be a base for further modifications of the JavaScript code: JavaScript calculated custom fields

Best,
Ilze, support@eazybi.com