Lead time for issue types from status C to G

Hi All

Im new to eazybi and im struggling to build a report that shows the lead time it took for an issue type ( story, defects etc) at status g to get there in days or working days from status c

Workflow
Status a - to do
Status b - backlog
Status c- in refinement
Status d- ready for dev
Status e- ready for test
Status f- testing
Status g- QA complete

Please can i get assistance with my query?

Hi @Dilan ,
You can check out example reports in eazyBI demo dashboard Age Lead, and Cycle Time. It represents different cases.
You can read more about it here in this answer provided by Daina:

Also, check this answer from Martins on how to calculate average time based on status transition:

best,
Gerda // support@eazyBI.com

Hi Gerda, None of those seem to be working for me i get either a memory error or no data is shown.

CASE WHEN ([Transition Status].[Done],[Measures].[Transitions to status]) > 0
THEN
[Measures].[Workdays in transition status] / [Measures].[Transitions from status issues count]
END

This is the Measure i have used which is the closest im getting to what i want… however im finding that its not calculating number of work days from In progress to Done.

In Pages i filter by Done Status, Issue Type i filter to Feature
In Rows i have created a aggregate of all the Transitions from Status C to Status G

Hopefully you can help me to refine this futher?

Hi @gerda.zandersone - it looks like the below report measure query works, however 2 issues:

  1. im unable to get a average workdays view
  2. im unable to add time in Rows so i can see a trend over time

Any advice?

– days in transition status when issue was in this status in previous times
IIF(
– if report uses Status dimension instead of Transition status it should work as well:
[Status].CurrentHierarchyMember.Level.Name = “Status” and Not [Transition Status].CurrentHierarchyMember.Level.name = “Transition status”,
([Measures].[Days in transition status],
[Transition Status].[Transition status].GetMemberByKey(
[Status].CurrentHierarchyMember.Key
)),
[Measures].[Days in transition status])
+
– days since last transition to this status
NonZero(SUM(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
– for unresovled issues only
IsEmpty([Issue].CurrentHierarchyMember.Get(“Resolved at”))
AND
IIF([Transition status].CurrentHierarchyMember.Level.Name = “Transition status”,
[Transition status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
AND
IIF([Status].CurrentHierarchyMember.Level.Name = “Status”,
[Status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
),
CASE WHEN
[Measures].[Issues history] > 0
THEN
DateDiffDays(
[Measures].[Issue status updated date],
Now()
)
END
))

HI @gerda.zandersone

i managed to get the below suggestive query working :smiley: Avg days between two statuses - #4 by martins.vanags

isit possible to figure out the workdays as this query is only showing days?

Hi @Dilan
If you want to calculate Workdays instead of Days, you can replace the function DateDiffDays() to DateDiffWorkdays() in the formula.

Gerda

Awesome that worked thank you, another bit of help please :smiley:

I found tickets that are skipping transitions as the workflow currently allows it. This means where issues havent gone to in progress before moving to done, the formula im using isnt calculating the lead time because its missing that initial status of In progress.

Would it be possible to enhance the query so it can handle that scenario also?

I.e.
Scenario 1 Calculate work days for issue type where its transitioned to Done from in progress for the month it transitioned to Done

Scenario 2 Calculate work days for issue type where its transitioned to Done but skipped inprogress transition for the month it transitioned to done.

@eazyBI-Staff Hi All need some support i am using the following measure to get me Lead time for issue types between 2 statuses… However i have noticed if the issue has travelled inbetween those statuses multiple times. then the report is duplicating the entries for each month/date the status had moved from C to G. i.e.

Lets say in JAnuary it went from C to G and then in February it went from G to C back to G. Instead of reporting it in February with the lead time it is reporting the lead time when the status went from C to G in Jan and from then the status went from C in Jan to G in Feb.

Hope that makes sense.

Measure:
CASE WHEN
(
[Measures].[Transitions to status],
[Transition Status].[QA Complete]
) > 0
THEN
AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
(
[Measures].[Transitions to status],
[Transition Status].[QA Complete]
) > 0
),
DateDiffWorkDays(
(
[Measures].[Transition from status first date],
[Transition Status].[Backlog],
[Time].CurrentHierarchy.DefaultMember
),
(
[Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember
)
)
)
END