Create a report that shows issues In Status and Average Time in that Status split by month

Questions & Answers
1

Can you help me build a report that shows the number of issues in a specific status and the average of days in that status but counting the days just inside that particular period?

Something like this:

In March, we had 15 issues In Progress and the average day in status was 2 days.

I just want to count the days that issues were In Progress in March and calculate the average of days to that issues.

Thanks

2

Hi @tozemorais

The default measures “Days in transition status” and “Average days in transition status” would calculate the duration in historical status. Still, they would show the full result against the day (period) when status is left - it won’t split the results by periods.

For example, if issue ABC-10 enters the status “In Progress” on March 10, 2025, and leaves it on April 5, eazyBI would return the “Days in transition status” = 26 against April 5, 2025 (April 2025—if your report shows a month view).
What I hear from your requirement is that you want to create a measure showing result 21 at the end of Mar 2025 in the report for status In Progress instead? Is that right?

In that case, you need to consider introducing a global calculated field with JavaScript that counts the duration in status. Then the field is imported and mapped to month-level periods from the Time dimension, so you can split this duration by months in the report.

Downside: This approach would not let you switch to Day, Week or any view from the Time dimension. It will be mapped for months because of how JavaScript code is defined.

Try adding this defintion to your eazyBI advanced settings:

[jira.customfield_d_inst_by_month]
name="Days in status by months"
data_type="decimal"
measure=true
multiple_dimensions=["Time","Transition Status"]
javascript_code='''
var datefrom = issue.fields.created;
var daysinstatus = 0;
var result = new Array;
issue.changelog.histories.forEach(function(history){
  history.items.forEach(function(historyItem){
    if (historyItem.field == "status") {
      statusto = historyItem.toString;
      statusfrom = historyItem.fromString;
      dateto = history.created;
      df = new Date(Date.parse(datefrom));
      dt = new Date(Date.parse(dateto));
      if (df.getMonth() == dt.getMonth() && df.getFullYear()==dt.getFullYear()) {
        daysinstatus=dt-df;
        result.push(dateto.substr(0,10)+","+statusfrom+","+daysinstatus);
      } else {
        while (df<dt) {
          first_day_next_month=new Date(df.getFullYear(), df.getMonth() + 1, 1);
          last_day_of_month = new Date(df.getFullYear(), df.getMonth() + 1, 0);
          daysinstatus = dt>first_day_next_month ? first_day_next_month-df : dt-df;
          result.push(strftime("%Y-%m-%d",(last_day_of_month>dt ? dt : last_day_of_month))
                         +","+statusfrom+","+daysinstatus);
          df=first_day_next_month;
        }
      } 
      datefrom = history.created;
    }
  });
  });
if(result) {
    return result.join("\n");
}
'''

If you want this code just in one eazyBI account or if you don’t have access to advanced settings because of your user role in eazyBI, you can define it as an account-specific custom field. In that case don’t forget to add additional advanced settings:

measure=true
multiple_dimensions=["Time","Transition Status"]

Then import this custom field with the measure checkbox selected.

That should calculate the time in status for each status and split by months.

Use this measure together with “Time” and “Transition Status” dimensions to split time by historical status and month level members.

Finaly, to calculate the average days in status, divide the imported measure by “Issues history”

CASE WHEN
[Measures].[Issues history]>0
THEN
[Measures].[Days in status by months]
/ [Measures].[Issues history]
END

Martins / eazyBI