Time in status till first transition only

Questions & Answers
, , ,
1

Hello,
since my management found out how powerful easyBI is, I got asked to create a report for measuring the time between combinations of various statuses. Report should be only for resolved issues and I’m trying to use Average resolution days since cycle start or I was thinkng about creating calculated dimension using Transition from status fist date but I don’t know how to put it together :frowning:

1st report should answer the question, what is the average time of all issues which stayed in “L2 assigned” status (this is the first status after Create) until it transitioned for the first time to status “L2 in progress” (it’s not wanted to count also other time sums issue entered the status and moved repetitively).

2nd report should be a breakdown, what is the average time of all issues which stayed in “L2 in progress” until the issue was transitioned for the first time between statuses:

  • “L2 in progress” → “Handover to solution”
  • “L2 in progress” → “Need more info”
  • “L2 in progress” → “Set in test”
    Again, only first cycle - the time when issue entered the L2 status for the first time, waited there “n” hours/days and then moved to other specific status. Ignore other times issue entered this status after that. If we can put there also number of issues that went this way (from avg is calculated) it would be awesome!

3rd report should answer, what is the average time for all resolved issues between “L2 assigned” → “Waiting for evaluation”, also only first cycle.

Could someone please help me to put this measure together or point me into the right direction?
Many thanks!

2

Hi @Gabriela_Molitorisz ,

With the requirement to take into account only the first transition, it is necessary to iterate through all issues and determine the first transition date and the duration.

The first and the third requirement seem close enough, so try to adopt the same approach for both of them. For example, below, you can see a formula that will retrieve the average number of days issues spent in the status “To Do” before being transitioned to “In Progress” the first time.

Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- resolved issues only
    Not IsEmpty([Measures].[Issue resolution date])
    AND
    -- first transition in current period
    DateInPeriod(
      ([Measures].[Transition from status first date],
      [Transition].[To Do => In Progress],  -- <==== change transition here
      [Time].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember
    )
    AND
    -- numeric measure to tie the dimensions together
    ([Measures].[Transitions from status],
    [Transition].[To Do => In Progress],  -- <==== change transition here
    [Time].CurrentHierarchy.DefaultMember) > 0
  ),
  -- the difference in days
  DateDiffDays(
      ([Measures].[Transition to status first date],
      [Transition Status].[To Do],  -- <==== change status here
      [Time].CurrentHierarchy.DefaultMember),
      ([Measures].[Transition from status first date],
      [Transition].[To Do => In Progress],  -- <==== change transition here
      [Time].CurrentHierarchy.DefaultMember)
  )
)

Try to change the relevant values to fit your use case.

For the second report, I recommend using the Transition dimension with the three transitions you are interested in rows. Then you can use a calculated measure similar to the one below:

Avg(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    -- resolved issues only
    Not IsEmpty([Measures].[Issue resolution date])
    AND
    -- first transition in current period
    DateInPeriod(
      ([Measures].[Transition from status first date],
      [Time].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember
    )
    AND
    -- numeric measure to tie the dimensions together
    ([Measures].[Transitions from status],
    [Time].CurrentHierarchy.DefaultMember) > 0
  ),
  -- the difference in days
  DateDiffDays(
      ([Measures].[Transition to status first date],
      [Transition Status].[In Progress],  -- <==== change status here
      [Transition].DefaultMember,
      [Time].CurrentHierarchy.DefaultMember),
      ([Measures].[Transition from status first date],
      [Time].CurrentHierarchy.DefaultMember)
  )
)

It will look for issues transitioned out of status “In Progress” and update the formula to “L2 in Progress”.

To get the number of issues, replace the Avg() function with Sum() and move the numeric measure in place of the DateDiffDays() function, which is not necessary for this calculation. To retrieve the number of issues instead of transitions, change the measure to “Transitions from issues count”. See the suggested formula below:

Sum(
  Filter(
    Descendants([Issue].CurrentMember,[Issue].[Issue]),
    Not IsEmpty([Measures].[Issue resolution date])
    AND
    DateInPeriod(
      ([Measures].[Transition from status first date],
      [Time].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember
    )

  ),
  ([Measures].[Transitions from status issues count],
  [Time].CurrentHierarchy.DefaultMember)
)

The second report could look similar to the one below:

Screenshot 2021-08-17 at 16.39.29
Screenshot 2021-08-17 at 16.39.291344×998 80.5 KB

See the eazyBI documentation page for more details on the functions used in the calculations - MDX Function reference - eazyBI for Jira.

Best,
Roberts // support@eazybi.com

1 Like
3

Hello Roberts,
sorry for the delayed response. Definitely pointed me in the right direction, thank you a lot, I will play with this around.

Many thanks, your support is much appreciated!

1 Like