How to calculate Issue Aging from the last transition to Open (excluding Deferred time)

[B.Roy]

Hi Team

I am currently using the pre-defined Average age days predefined measure to compute defect aging. However, I have an issue:

For defects that were once moved to Deferred and then later brought back to In Progress/Open, the Average age days calculation still starts from the defect’s Created date.
This inflates the aging values because it does not account for the time the defect spent in Deferred.

I want the aging calculation to start from the last time the issue transitioned to an Open status rather than from the issue’s created timestamp.

In other words, if a defect is moved to Deferred and later reopened, its age needs to be recalculated from the point it was transitioned back to Open.

Can someone please help me with an MDX formula

Thanks in advance for your guidance!

Best

Roy

[janis.plume]

Hi,

The MDX solution to this use case looks like this:

Avg(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
    IsEmpty([Measures].[Issue resolution date])
  ),
  DateDiffDays(
    ([Measures].[Transition to status last date],
    [Transition Status].[Open statuses]),
    Now()
  )
)

The formulas requires that you have defined a calculated member in the Transition status dimension where all Open statuses are combined.

This formula can have performance issues. Those can be resolved with JavaScript calculated custom field.

Kindly,

Janis, eazyBI support

[B.Roy]

This solution is not working for me. The query is timing out. Can you elaborate on the java script to use.

[janis.plume]

Hi,

The following approach is for better efficiency.

1. Create a JavaScript-calculated custom field with the following settings:

2. Use the following JavaScript (note to update the open_status list:

// Find the timestamp of the last status change
// Search backwards from the most recent changelog entry
var open_statuses =["In Progress","Open"];

// For open issues only
if (open_statuses.indexOf(issue.fields.status.name)>=0) {
if (issue.changelog && issue.changelog.histories) {
  for (let i = issue.changelog.histories.length - 1; i >= 0; i--) {
    let history = issue.changelog.histories[i];    
    if (history.items) {
      for (let j = 0; j < history.items.length; j++) {
        let item = history.items[j];
        
        // Check if this is a status change
        if (item.field == "status" && open_statuses.indexOf(item.toString)>=0) {
          // Found the most recent status change, return immediately
          return new Date(history.created).getTime() / 1000;
        }
      }
    }
  }
}

// No status change found, return the issue creation date

  return new Date(issue.fields.created).getTime() / 1000;
}

3. After the import a set of timestamp measures will be created:

Create a custom formula for the average:

DateDiffDays(
  TimestampToDate(
    [Measures].[Open status last timestamp created] /
    ([Measures].[Issues created],[Status].[Open statuses])
  ),
  Now()
)

This formula expects that you created the respective aggregated member in the Status dimension.

The formula might need adjustments if you intend using the Time dimension in your report.

Kindly,

Janis, eazyBI support