EasyBI: Average Workdays in Transition Status, Transition status multiple times

[Ryan Peck]

I have an eazyBI report that is supposed to display the average number of workdays a specific developer's tickets are In Progress. For the majority of the jira tickets the number of days matches up with what I can see in the Jira ticket transition history, given the ticket only went into In Progress one time.

If a ticket went into In Progress status, then later in the lifecycle went back to In Progress then the eazyBI "Average workdays in transition status" only seems to be calculating based on the last time the issue was In Progress, and not based off both times the ticket was in the In Progress status. I checked this both by viewing the actual transition history on the ticket itself, but also by using the built in Jira "Time In Status Gadget" on the dashboard (I'd use that but I can't set it to only show a single developer)

Thus, my calculations are incorrect and any ticket that went to In Progress status more than once is showing a lower value than expected. Is there a way to get it to calculate off ALL the times the ticket was In Progress?

[Ryan Peck · Accepted solution]

Update: I was using the wrong field and I was able to answer my own question. What I needed was "Workdays in Transition Status". The "Average" one was doing an average of the however long it was In Progress each time that ticket went into that status. So any ticket that was only in In Progress once showed the right time, but any that were In Progress more than once were displaying the time divided by however many times it was in that status.

[Ayça Erdem_OBSS]

Hello @Ryan Peck 

You’ve already found the solution to your issue, but I wanted to share an alternative option that could also help address this challenge.

Thank you for sharing your question and update! It seems like you encountered a common challenge with calculating accurate time durations for statuses that a ticket has entered multiple times.

To address this, I would like to introduce Timepiece - Time in Status for Jira, an advanced reporting app developed by our team at OBSS, which offers highly flexible and accurate reporting for Jira. One of its key features is the ability to calculate total or average durations for statuses like "In Progress," even when a ticket has entered that status multiple times during its lifecycle.

Here’s how Timepiece can help solve your problem:

1. Total Time in Status: Timepiece provides the total amount of time a ticket spent in a specific status (e.g., "In Progress"), summing up all occurrences of that status, regardless of how many times the ticket transitioned into and out of it.
2. Average Time in Status: If you need an average duration, Timepiece can calculate the average time a ticket spent in a status across all its occurrences. For example, if a ticket entered "In Progress" three times, the app can divide the total time by three to give you an accurate average.
3. User-Specific Filters: Timepiece also allows you to filter the data by a specific developer or assignee, ensuring that your reports only show the tickets relevant to the person you're analyzing.
4. Historical and Detailed Reporting: Since Timepiece utilizes Jira’s issue history, you can analyze both historical and current data without needing to change your workflows or set up additional tracking in advance.

By using Assignee Duration per Status in Timepiece, you can precisely calculate the total or average time spent in "In Progress" for each ticket, ensuring that no data is missed, even for tickets that transitioned into "In Progress" multiple times.

Additionally, I have attached a screenshot below showing where you can access this report in the Timepiece app. This visual guide will help you quickly locate and configure the report to meet your needs.

With Timepiece,  you can generate combined reports across multiple projects, export them in formats like XLS or CSV, and even use them in tools like eazyBI for further analysis. 

Feel free to explore Timepiece - Time in Status for Jira for more advanced reporting capabilities, or schedule a live demo with our team. We would be happy to guide you through the app and answer any further questions.

Hope this helps!

[Ilze Krauze]

Hi Ryan Peck, 

To calculate average days in status per issue instead of transition, you can use the following formula, with the measure "Transitions from status issues count" as the denominator:

CASE WHEN [Measures].[Transitions from status] > 0 THEN
[Measures].[Days in transition status] /
[Measures].[Transitions from status issues count]
END

The measure "Transitions from status issues count" is located in the Distinct issues count measure group and counts the number of unique issues that transitioned from the selected transition status during the specified time period.

I hope this helps.

Ilze / eazyBI team