Calculate Lead time for an issue type when it left either of statuses

Questions & Answers
1

Hi Eazybi Team,

I am currently using the following query to calculate lead time for an issue type:

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

What i noticed is that it disregards any Jira ticket where the ticket never left the “in progress” status. There is no restriction in the workflow to say all issues have to go straight into in progress. Is there a way to adapt the above formula so it calculates the lead time from first date an issue type has left an array of statuses? i.e. if an issue type went from either Backlog or In refinement or ready for development or in progress to QA complete.

2

Hi @Dilan,

here

DateDiffWorkDays(
([Measures].[Transition from status first date],
[Transition Status].[in progress],
[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember)
)

you measure the difference between [Transition Status].[in progress] and [Transition Status].[QA Complete], that’s why you don’t get the others. Change [Transition Status].[in progress] to whatever status yo want to measure.

Best
Sven

3

Hi @sven

thanks for the response.

so the issue is really that an issue doesnt have to go to in progress so if it doesnt then it doesnt get calculated… Theres no workflow constraint to say that issue needs to go to in progress before QA complete. So i was thinking whether we could look at saying calculate the leadtime from when a issue type has left either of XYZ Statuses from first date to QA Complete.

Hope that makes sense.

4

Hi @Dilan,

sure you can do that, you just have to know from which status you want to calculate. The formula you are using is using the date of leaving “In progress”. I guess it is a bit tricky to find all issues, that have never been in status “In progress”, but it should be possible via usage of tupels. For those you use the status before and for all others the formula above and then add both values up.

Does this make sense? :upside_down_face:

Best
Sven

5

Thanks @sven

Reading up on how to use tuples but not really clear able to share any examples?

6

The above formula uses two tuples:

([Measures].[Transition from status first date],
[Transition Status].[in progress],
[Time].CurrentHierarchy.DefaultMember)

and

([Measures].[Transition to status last date],
[Transition Status].[QA Complete],
[Time].CurrentHierarchy.DefaultMember)

You would change the first one to something working. :smiley: Maybe one of the eazyBI-guys has an idea. I don’t at the moment. Sorry.

There’s also a pretty good example here:

Tuple (eazybi.com)

7

Thanks @sven

Any Eazy bi staff able to help if its possible to configure my report / measure to calculate lead time from when a issue has left any range of statuses from first date to last date it went to QA Complete

8

Hi @Dilan,

I suggest first creating an aggregated calculated member “Starting status” within the Transition Status dimension to group all the starting statuses.

Aggregate(
{[Transition Status].[Backlog],
 [Transition Status].[In refinement],
 [Transition Status].[ready for development],
 [Transition Status].[in progress]
})

Please double-check the names of statuses on your Jira instance.

You might then use this calculated member to find the first timestamp from any of these statuses.

TimeStampToDate(
 MIN(
   {PreviousPeriods([Time].CurrentHierarchyMember),
    [Time].CurrentHierarchyMember},
   DateToTimeStamp(
    ([Measures].[Transition from status first date],
     [Transition Status].[4 Statuses]))
 )
)

Then the whole expression might look as follows.

CASE WHEN
 ([Measures].[Transitions to status],
  [Transition Status].[QA Complete]) > 0
THEN
 AVG(
  Filter(
   Descendants([Issue].CurrentMember, [Issue].[Issue]),
--condition for filter
   DateInPeriod(
     ([Measures].[Transition to status last date],
      [Transition Status].[QA Complete],
      [Time].CurrentHierarchy.DefaultMember),
     [Time].CurrentHierarchyMember)
   ),
--actual measure for AVG
DateDiffWorkDays(
 TimeStampToDate(
  MIN(
   {PreviousPeriods([Time].CurrentHierarchyMember),
    [Time].CurrentHierarchyMember},
   DateToTimeStamp(
    ([Measures].[Transition from status first date],
     [Transition Status].[4 Statuses]))
  )),
  ([Measures].[Transition to status last date],
   [Transition Status].[QA Complete],
   [Time].CurrentHierarchy.DefaultMember)
 )
)
END

Regards,
Oskars / support@eazyBI.com