Time that tickets stayed in a specific status during a specific period

Hello !
I am pretty new to eazyBI, now testing it for the first time, faced a problem and cannot find a solution.
I cannot display the time that tickets stayed in a specific status during a specific period.
Let me explain:
There is the first ticket, created on May 10 in the To Do status and stayed in this status until June 17, then moved to In Progress for 2 days and then to Testing.
There is a second ticket, created on April 1 in the To Do status and stayed in this status until June 26, and moved to Testing on July 9.

I want the monthly report to show me that in June the tickets were in total status
To Do - 43 days (17 + 26)
In Progress - 6 days (2 + 4)
Testing - 13 days

But if I display the report “Days in transition status”, I see incredible numbers, as if the system adds up the entire period that has accumulated in the current status before its change, and not from the beginning of the month.

Help please, is it possible to create such a report at all?

Hi,

The default measure of Days in transition status does not split the days by the period selected in the report. Instead, it shows the full number of days spent in the status for the period when the issue transitioned from the status.

It is possible to create a workaround with a Javascript calculated custom field.

I used the following advanced settings for the solution:

[jira.customfield_wd_inst_by_month]
name="Workdays 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)/1000/3600/24;
        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)/1000/3600/24) : ((dt-df)/1000/3600/24);
          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) {
    issue.fields.customfield_wd_inst_by_month = result.join("\n");
}
'''

This solution creates a new measure to show the split of the Time by months. The measure expects that the Monthly level of the Time hierarchy is used in the report:

Kindly,
Janis, eazyBI support

When I test this code I get this result


But when I added it to settings and made loading of this field as measures, I get emptiness

Why might this be happening?

There was an unfortunate mistype in the last line of the code. The field ID was incorrect.
I updated the code and it should be fixed now.

instead of how long, what if i want to know how many unique issues were staying in some specific status during specific period?

Hi,

The solution for this use case is possible with the custom formula, but it requires quite inefficient construction with high risk of reaching reasonable timeout:

NonZero(Count(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  ([Measures].[Transitions from status],
  [Transition Status].[In Progress])>0
  OR
  ([Measures].[Issues history],
  [Transition Status].[In Progress])>0
  )
))

Kindly,
Janis, eazyBI support