Total workdays for all the stories or sub tasks

Hi,

I have a lot of “Story”, some without sub tasks, some with sub tasks.

  • If I have no sub task, I wish to count the number of days “in progress”
  • If I have sub task, I with to count the number of days “in progress” for the subtasks, but NOT the stories

SO, I wrote this code, but I got some error message

CASE

WHEN [Measures].[Issue type] = “Story” AND [Measures].[Sub-tasks created] = 0 THEN

([Measures].[Workdays in transition status], [Transition Status].[In Progress])

WHEN [Measures].[Issue type] = “Story” AND [Measures].[Sub-tasks created] > 0 THEN

Sum(
Filter(
DescendantsSet([Issue].CurrentHierarchyMember, [Issue].CurrentHierarchy.Levels(‘Sub-task’)),
([Measures].[Workdays in transition status], [Transition Status].[In Progress])
)
)

END

So please, any help is welcome. I can’t fix and make it works. Any idea ?

Thanks

Hi @cdemez

You are on the right track!
As you create an issue list, I would use the issue property “Issue sub-task keys” to detect if the story has or does not have any subtasks.
Also, I would use this same property in the sum calculation, for retrieving the set of subtasks.

CASE WHEN 
 [Measures].[Issue type] = "Story" 
THEN
 case when
 IsEmpty([Measures].[Issue sub-task keys])
 then
 Val(([Measures].[Workdays in transition status], [Transition Status].[In Progress]))
 when Not IsEmpty([Measures].[Issue sub-task keys])
 then
 Sum(
 [Issue].[Issue].GetMembersByKeys([Measures].[Issue sub-task keys]),
 ([Measures].[Workdays in transition status], 
  [Transition Status].[In Progress])
 )
 end
END

How does this calculation work for you?

Best,
Ilze, support@eazybi.com