Avg Time in status

Questions & Answers
1

I transformed one formula into another to calculate the average time in status for closed tasks only. I also specified the statuses, as I have more of them in the Workflow, but I need it only for these. However, it doesn’t produce any results. What did I write incorrectly? I ask for help, thank you

Original(EazyBi)

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

My Formul

CASE WHEN [Measures].[Issues closed] > 0 THEN
  AVG(
    Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Measures].[Issues closed] > 0
    ),
    Sum(
      {
        [Status].[Analysis],
        [Status].[Development],
        [Status].[Code Review],
        [Status].[Testing],
        [Status].[Stage]
      },
      [Measures].[Days in transition status]
    ) / 
    Sum(
      {
        [Status].[Analysis],
        [Status].[Development],
        [Status].[Code Review],
        [Status].[Testing],
        [Status].[Stage]
      },
      [Measures].[Transitions from status]
    )
  )
END
2

I tried this method, it also shows nothing.

Transition status ( Unresolved statuses)

Aggregate({
[Transition Status].[Analysis],
[Transition Status].[Development],
[Transition Status].[Code Review],
[Transition Status].[Testing],
[Transition Status].[Stage]
})

Measures

CASE WHEN
[Measures].[Issues closed] > 0
THEN
([Measures].[Workdays in transition status], 
[Transition Status].[Unresolved statuses]) /
[Measures].[Issues closed]
END
3

Hi @JiraDeveloperMD

There are two ways how to approach this.

The faster and simpler way:
In the Import options, define a custom Issue cycle that will include all these statuses. After the import a predefined measure will be precalculated that will return Average cycle days/workdays for the group of these statuses.

The more complex and possibly slower way:
Your original formula was really close to the necessary, except it used the Status dimension members which hold current status value for issues. Instead, switch this to the Transition Status (as you’ve done in the later formulas) and you should get the expected result:

CASE WHEN [Measures].[Issues closed] > 0 THEN
  Avg(
    Filter(
      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      [Measures].[Issues closed] > 0
    ),
    Aggregate({
      [Transition Status].[Analysis],
      [Transition Status].[Development],
      [Transition Status].[Code Review],
      [Transition Status].[Testing],
      [Transition Status].[Stage]
      },
      [Measures].[Days in transition status]
    )
  )
END

Note that this calculation will be more resource-demanding and may result in slower reports, so the Issue cycles are recommended instead.

Let me know if this works as expected!
​Best regards,
​Nauris