Average days in transition status filtered by first sprint issue was in


I am very new to eazyBI and struggling to get it to do what I need. We are working in Scrum and I would like to see this data: on average how many days an issue spent sitting in a specific transition status + display this information only for issues that were taken into specific sprint. Please see details below.

I currently have Transition status in “Rows” and Measure “Days in transition status” in “Columns”. Which means that I have pretty big numbers in the cells, because these are sums from issues from the beginning of the Jira project (as I understand). I can click on the data cells and drill through issues to see from how many issues this number was gotten. I checked by opening some issues, that this data is the one I need for my calculation.

So firstly, I would like to divide the “Days in transition status” number by how many issues were displayed when I clicked on “Drill through Issue” to get the average.

Secondly, I would like to use only specific issues for this calculation. My idea was to be able to select the sprint (or multiple sprints), which was the first sprint the issue has been taken into (because we have leftovers often enough). I think I can’t simply use Sprint or Time dimensions in “Pages”, because they seem to cut off the days for which the issue was in the transition status already before the selected sprint or time period started (making the overall average go down).

Hope this makes sense.

Best regards,

Sveika, Krista, un laipni aicināta eazyBI kopienas lapā!

Instead of Days in transition status, you may want to use measure Average days in transition status from predefined measures. Drilling through issues, you would see the average in this status for each issue (usually total days; average if there had been several transitions from the same status for the issue). Import issue change history - eazyBI for Jira

About the first sprint. It would be a quite complex calculation that most probably would time out the report. At the issue-level, you may retrieve the issue first sprint quite easily, but applying it as a filter to all calculations is the hardest part. (See, for instance, community thread Report Issues with the first sprint only)

Instead, consider creating a new JavaScript calculated custom field, that, for each issue, retrieves the issue first sprint, and then import it as a new dimension. Then you would just apply this dimension in Pages as a filter, and all results in the report would come only from the issues with the selected first sprint.
Read here: JavaScript calculated custom fields - eazyBI for Jira

Ilze / support@eazybi.com

Sveika, Ilze, paldies!

Yes, I am aware of the “Average days in transition status” measure, but it does not fulfill what I have in mind. If I compare this number with what I get by dividing “Days in transition status” with the number of issues that contribute to this overall number, the result from this predefined measure is much smaller. I understand its because, as you mentioned, it takes into account that the same transition happened more than 1 time. I want to ignore that and for the calculation use only the overall time spent in the status and do not average it over the times the transition happened (because in my project the issue could go back and forth between statuses due to various reasons and its not what I am currently interested in).

About the second question - I understand importing a completely new dimension would be quite complex and would require help from our company’s Jira admin?


Sveika vēlreiz,

You may want to create a custom calculated measure for Average: use the same approach as for predefined average, only, instead of “Transitions from status”, use “Transition from status issues count”. In such a way, you would divide the total days by issues (regardless of how many transitions have happened), not transitions:

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

Also, take into account the Time period used in the report: only transitions and days from transitions that happened during the selected Time period are included in the calculation. If you want it otherwise, then either time dimension should be applied to the report differently or this measure modified.

About sprints: yes, you can use eazyBI advanced settings to define JavaScript calculated custom field only if you are Jira system administrators or eazyBI admin. Probably, you can ask your Jira admin to set you as eazyBI admin to that by yourself?


Paldies Ilze,

yes, this seems correct, I would not have come up with that on my own. The number still does not match completely with my “manual calculation” - it can differ by up to 20% but I think that is a separate question.

I have currently not selected a specific time period (either I don’t use Time dimension or I put it in Pages with “All times”) to see the data with no additional filter. Our project is not that old/ big so that I can do this. The most logical way to apply the time period, in my opinion, was to do this filtering by the first sprint a issue was taken into. But as I understand from you reply it requires importing completely new custom dimension which I am afraid will be complicated with my limited knowledge.

Maybe there is another parameter that does not require importing custom dimension but could achieve similar result. For example, maybe there is an option to use the Time or Sprint dimension in Pages but create a new calculated member that display issues for whom the first transition to “Development” was done in specific time period/ sprint?