Average Workdays between two statuses by Sprint

Questions & Answers
1

Hi,

I’m looking to measure Average Workdays between two Statuses (To Do and Final Review) by ticket, then aggregate an average by Sprint.

My current setup is:

Calculated Member on Transition Status
Aggregate({
[Transition Status].[To Do],
[Transition Status].[Work in Process],
[Transition Status].[In Review],
[Transition Status].[Peer Review],
[Transition Status].[Peer Review In Progress],
[Transition Status].[Ready for Release]
}),

And a calculated member
CASE WHEN [Measures].[Transitions from status] > 0 THEN
[Measures].[Workdays in transition status]
END

The breaks down time in status by status, but I can’t figure out how to average the totals by Sprint. Any ideas?

2

Hi Jonny,

You are on the right track with a calculated member in Transition status dimension: use it in a tuple with total workdays measure divided by issues count having a transition from one of those statuses (again, a tuple) to get the average.

The calculation could be the following (use correct calculated memebr name instead of …calculated member…):

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

Ilze
support@eazybi.com

1 Like
3

@ilze.leite This helps, thank you!

I still seem to have an issue with the data that’s produced. It seems that, for issues that moved from one Sprint to another, the days spent in transition status count toward the average of the Sprint, even though the ticket itself does not appear in the Sprint row grouping.

See

image
image.png945×192 3.96 KB

My report configuration:

Pages: Issue Type (Filtered by Story, primarily)
Rows:

  • Sprint
  • Issue

Columns:

  • Average Days ToDo to Final Review (the calculation you posted above). This references a Transition Status “Aggregated Days ToDo through Final Review”.

I have a couple Stories in particular that spanned several Sprints. When I break the data apart by transition status, it seems that a single Story has time in transition status counting toward several Sprints (Whichever Sprint it was in when the time in transition status was recorded?). For example: a single story might have Work In Progress toward Sprint 1, and Ready for testing time in status on Sprint 2.

Instead, I’d like to pull all of the time in all specified transition statuses into the results. Is there a way to pull the [Workdays in transition status] aggregated value into a single location after I add the Sprint row?