Rolling up "Start Date" from child to a parent

Questions & Answers
1

I’m trying to create a Gantt chart report that will show the start and due date of an issue type named “Milestone” which a higher level than “Epic”, it should show the first “Start date” of child issue and the last “Due date” of child issues. if there is no data in the child issue it should be empty.
I was able to do that in the “Due Date” but not the 'Start Date", it will be very helpful to get some help.
here are the logic that I used

-- annotations.group = Prediction by epic
Cache(
CASE WHEN
  -- this case will work for Epic Link dimesion and individual epic selection
  [Parent Link].CurrentHierarchyMember.Level.Name = "Milestone"
  AND
  [Parent Link].CurrentHierarchyMember.Name <> "(no Parent)"
THEN
  CASE WHEN
    not IsEmpty([Parent Link].CurrentHierarchyMember.Get("Created at"))
  THEN
  -- for resolved issues grab resolution date as report end date
    [Parent Link].CurrentHierarchyMember.GetDate("Created at")
  ELSE
  -- get the last due date from issues in epic:
    TimestampToDate(Max(
      Descendants([Issue Type].[Milestone].GetMemberByKey([Parent Link].CurrentHierarchyMember.Key),
      DateToTimestamp([Measures].[Issue Start date] ))))
  END
WHEN
 -- if Issue hierarchy on epic level: 
  [Issue].CurrentHierarchyMember.Level.Name = "Milestone" 
THEN
   CASE WHEN
   not IsEmpty([Issue].CurrentHierarchyMember.Get("Created at"))
  THEN
  -- for resolved issues grab resolution date as report end date
    [Issue].CurrentHierarchyMember.GetDate("Created at")
  ELSE
 -- access all children of the epic and get the latest due date
  TimestampToDate(Max(
    Descendants([Issue].CurrentHierarchyMember),
    DateToTimestamp([Issue].CurrentHierarchyMember.GetDate('start date'))))
  END
ELSE
  -- for issues show due date
  [Issue].CurrentHierarchyMember.GetDate('start date')
END
)

and here is the report result

Screen Shot 2022-09-08 at 4.07.05 PM
Screen Shot 2022-09-08 at 4.07.05 PM1342×505 91.7 KB

2

Hi @Tamer,

You implemented a good way to retrieve the latest date using Max() and date transformation to timestamps and back. To retrieve these values from a specific level of the Issue dimension, I recommend using the Descendants() function. Please see the formula below where the earliest “Target start” date is retrieved from the Advanced Roadmaps hierarchy “Epics”:

TimestampToDate(
  Min(
    Filter(
      Descendants([Issue.Advanced Roadmaps].CurrentMember,[Issue.Advanced Roadmaps].[Epic]),
      Not IsEmpty([Issue].CurrentHierarchyMember.Get('Target start'))
    ),
    DateToTimestamp([Issue].CurrentHierarchyMember.Get('Target start'))
  )
)

You can use a similar formula with Max() and the other date to retrieve the latest end date. Adjust the formula to consider the Issue dimension hierarchy you are using in the report rows. It will work for several levels above the Epics if required. See the picture below for reference:

Screenshot 2022-09-20 at 16.17.04
Screenshot 2022-09-20 at 16.17.042226×1418 390 KB

Best,
Roberts // support@eazybi.com

3

Hi @roberts.cacus ,

Thank for this it works.

Thanks,
Tamer

1 Like
4

Hi @roberts.cacus ,

a quick question as of now, the roll up works on the level that I define but not on the Childs and so on,. for example I do have “Domain”>>“Module”>>“Milestone”>>“Epic” and so on, If I applied the code for epic it will role up to the epic, and if I applied it to the Domain it will role up to that level but not the Childs.

Screen Shot 2022-09-27 at 8.03.38 PM
Screen Shot 2022-09-27 at 8.03.38 PM1220×924 101 KB

5

Hi @Tamer,

The provided formula will roll up but not down. That is because it descends into a specified level. In the example, into the “Epic” level. The Epic children are levels below the Epic.
If you want to roll the value of the parent Epic down to their Stories, add a CASE statement and get the date value of the Epic with the Ancestor function, no matter how low the issue is in the hierarchy. See an example for “Target start” below:

CASE WHEN IsEmpty(([Measures].[Issues created],[Issue Type].[Epic]))
THEN
Ancestor([Issue.Advanced Roadmaps].CurrentMember,[Issue.Advanced Roadmaps].[Epic]).Get('Target start')
ELSE
TimestampToDate(
  Min(
    Filter(
      Descendants([Issue.Advanced Roadmaps].CurrentMember,[Issue.Advanced Roadmaps].[Epic]),
      Not IsEmpty([Issue].CurrentHierarchyMember.Get('Target start'))
    ),
    DateToTimestamp([Issue].CurrentHierarchyMember.Get('Target start'))
  )
)
END

See the eazyBI documentation page for details on the proposed MDX functions - MDX function reference.

Best,

1 Like