Roll up an average of duration difference

Questions & Answers
,
1

Hi,

We are using the formula below to calculate the difference in duration between a certain schedule and its baseline. The dates are custom fields from a plugin we use (BigPicture Gantt).

DateDiffDays([Measures].[Issue Start date],
[Measures].[Issue End date])-DateDiffDays(
[Measures].[Issue Baseline start date],
[Measures].[Issue Baseline end date])

The formula works fine but it doesn’t ‘roll up’ from current member to the parent (say task, to Epic, to Project). Interestingly enough, the same type of formula used on Effort (as in differences in Original Estimate versus Remaining Effort and Time Logged), rolls up by itself.

How can I then calculate an average for a parent which includes all its children ? The goal is to have a bar showing project x average delay y days, then you drill down and each Epic has an average delay, then you drill down and each Task has an average delay etc

2

Hi,

The reason why the formula does not roll-up to higher levels is that it is based on the Issue properties. The properties are fields that work only with a specific level of the dimension members, in your case, the Issue dimension, Issue level. Indeed, what might be the “Issue End date” for the whole Project? The numeric fields that are imported as measures can do the “roll-up” by summing the values up which is what happens with the estimates and hours spent.

Perhaps, the short video on our documentation site could explain that better:
https://docs.eazybi.com/eazybijira/getting-started/measures-and-dimensions

The solution for this case is to create your own aggregation as the sum from the Issue set.
The formula might differ depending on what issues you wish to see in the aggregate. A solution to create such a measure would be the following:

sum(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[Issues created]>0),

  --your calculation at issue level comes here
  DateDiffDays([Measures].[Issue Start date],
  [Measures].[Issue End date])-DateDiffDays(
  [Measures].[Issue Baseline start date],
  [Measures].[Issue Baseline end date])
)

Kindly,
Janis, eazyBI support

3

Thanks Janis I believe that worked. We’ll collect more data to be sure.

4

Also how can I track changes to the values of that Issue End date (custom field) for example ? I would like to have a trend chart that shows the values as they change over time. I read this but I’m unclear on how to create a table and find the Custom Field ID for that field specifically:
https://docs.eazybi.com/eazybijira/data-import/jira-issues-import/import-issue-change-history

5

Hi,

date custom field changes in the simplest way could be imported without the need to use the advanced settings. It is possible to check the custom field value changes in the data import options screen:

image
image.png1810×566 44.3 KB

Perhaps, the simplest version would not cover more complicated use-cases; it would provide the list of changed values as described here:
https://docs.eazybi.com/eazybijira/data-import/custom-fields#CustomFields-Customdatefieldoldvalues

You should contact the support for further guidance if the default value change import is not sufficient for your trend chart.

Kindly,
Janis, eazyBI support

6

I have a similar request, but I cannot get it to work. I too am using two customer date/time fields and trying to do a datediffminutes. Unfortunately nothing’s returning.

Ultimately I’m trying to create an uptime report by month for our “Problem” cases based on the difference between the outage start and outage end times we log in the case. I feel like I’m missing something basic here. Any help?

sum(
  Filter(Descendants([Issue].CurrentMember,[Issue].[Issue]),
  [Measures].[Issues created]>0),
  
DateDiffMinutes(
[Measures].[Issue Outage End],
[Measures].[Issue Outage Start]

))