Count epics with children issues in different states

[jbuchhol]

I want to create a report to show the state of epics historically where:

• “In Dev” is any In Progress epic that has 1 or more children stories in the Open or In Progress state
• “In QA” is any In Progress epic that has 0 children stories in the Open or In Progress state and 1 or more in the Ready for QA state
• “Ready to Ship” is any In Progress epic that has 0 children stories in the Open, In Progress, or Ready for QA state and 1 or more in the Release Ready state

Here is a mock up of how I imagine it might look - the numbers should be a count of Epics in states as described above:

Question: is there a way to count the children stories in those states historically? Something along the lines of this perhaps?

Aggregate(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
[Measures].[Issue status] =“Open” or
[Measures].[Issue status] =“In Progress”
)
)

I think this would give me the story count though, not the epic count. I’m working on this any making slow progress - any suggestions would be greatly appreciated!

Thanks

[ilze.krauze]

Hi jbuchhol,
Welcome to the eazyBI community.

This will be a longer answer and is currently based on the assumption that you want to filter Epics that currently are “In Progress” and that you would want the “Drill through issue” to open a list of epics.

There are several steps:
(1) Create dimension members that create aggregates for later calculations. Please double-check if all status names are correct.
(1.1) To exclude epics from child calculations create [Issue Type].[Except Epics]

Aggregate( Except( [Issue Type].[Issue Type].Members, {[Issue Type].[Epic]} ) )

1.2) To group In Dev statuses create [Transition Status].[In Dev]:

Aggregate( 
{[Transition Status].[Open] 
,[Transition Status].[In Progress]} 
)

1.3) To group all child statuses that are in Dev and ready for QA create [Transition Status].[Not Ready to ship]:

Aggregate(
 {[Transition Status].[Open] 
,[Transition Status].[In Progress] 
,[Transition Status].[Ready for QA]} 
)

(2) Define a new report specific calculated measure “Epics In Dev” that uses newly created aggregated dimension members [Transition Status].[In Dev] and [Issue Type].[Except Epic] to count epics with at least one child in dev. If you choose different names in step 1, then the names should be adjusted in this formula as well:

Count(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Issue type] = "Epic"
    AND
    [Measures].[Issue status]= "In Progress"
    AND
-- at least 1 child "In Dev":
    ([Measures].[Issues history]
    ,[Issue.Epic].[Epic].GetMemberByKey([Issue].CurrentHierarchyMember.key)
    ,[Transition Status].[In Dev]
    ,[Issue Type].[Except Epic]
    ,[Issue].DefaultMember)>0
  )
)

(3) Formula for “Epics in QA”:

Count(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    [Measures].[Issue type] = "Epic"
    AND
    [Measures].[Issue status]= "In Progress"
    AND
   -- no child "In Dev":
    IsEmpty(NonZero(([Measures].[Issues history]
    ,[Issue.Epic].[Epic].GetMemberByKey([Issue].CurrentHierarchyMember.key)
    ,[Transition Status].[In Dev]
    ,[Issue Type].[Except Epic]
    ,[Issue].DefaultMember)))
    AND
    -- at least 1 child in Ready for QA:
    ([Measures].[Issues history]
    ,[Issue.Epic].[Epic].GetMemberByKey([Issue].CurrentHierarchyMember.key)
    ,[Transition Status].[Ready for QA]
    ,[Issue Type].[Except Epic]
    ,[Issue].DefaultMember)>0
  )
)

(4) Formula for “Epics Ready to Ship”:
Use the same formula as for “Epics in QA”, but in IsEmpty condition replace [Transition Status].[In Dev] with [Transition Status].[Not Ready to ship] (created in step 1.3)

Note that “Issues history” field will look at issues at the end of the Time period, so these formulas will show epic counts at the end of each month.

Let me know if that works for you or if you have more questions.

Kindly,
Ilze

[jbuchhol]

This is awesome and I think it might be the solution. Whether it is or not - I greatly appreciate the effort put in and can say that I learned quite a bit just tinkering with it to adjust for some unexpected issues (additional states needed, ignoring another issuetype beyond just Epics). So thank you for that!

The reason I say that I think this is the solution is that I believe the only discrepancies remaining are due to having two projects in my data set. I have PROJECT and PROJECTSW in my data set. If the Epic has has two stories, one story in PROJECT in the “Ready for QA” state and one story in PROJECTSW in the “In Progress” state then that epic gets two rows in my report and it shows up as both “In Dev” and “In QA”.

I have asked our administrators to implement the change listed here: Additional Issue hierarchies

However, our change process can be slow. A simple yes or no question: would it be worth opening a new question about this (might there be a way to combine the two rows described above without changing the data import)?

[ilze.krauze]

Hi jbuchhol,
If Project dimension is added in the report, then, in your described scenario, epic will be counted in both projects. You could try creating aggregated project member, if you want to see those two projects’ data together, but that depends on your desired report layot. Would you mind sharing a screenshot here?

Kindly,
Ilze

[Shivaprasad_Hattarak]

Hi @ilze.krauze,

I want a count of epics which don’t have the same status as its child status.

My epic will move based on child status. (all child statuses are the same)

Ex: All children of EPIC-1 are in DONE status and EPIC-1 is in TO DO status, I want the count of epics which don’t have the same status as its child status.

Regards,
Shivaprasad