Time between to actions

Hello,

Our development-teams works intesively with Scrum. To further improve the ongoing work, thery would like to have some reports. I got no idea how to create them, Therefore i described two of them below.

1) Time since first to Status ‘In Progres’ to last transition to 'Closed’
Similar to question Time interval in Status we would like to have a report that shows our for every team per sprint the the average time the issues needed from Status ‘in Progres’ to Status ‘Closed’.

I was more looking after something like How to calculate Days in transition status including the time in current status, but i do not know how to get to these time-informations.

2) Time since first set the story-point or an estimation-time to Status 'Done’
Similar to point 1, but the time should be calculate from the time the story-points or the estimation-time is recorded. Is this possible?

Thanks for your help.
Regards,
Roman

Hi Roman,

Time since first to Status ‘In Progress’ to last transition to 'Closed’
For the first calculation, you can use historical measure Transition to status first date to set a date when issue moved to status In Progress. You should use the measure on Issue level only to get correct results. I also would suggest using this measure in a tuple with Time and Sprint default members to get this date for the first time issue moved to this status no matter when and in which Sprint the issue was during this status transition.

You can use default imported property Issue closed date. We create this date based on specified closing statuses in import settings. eazyBI calculates the last time when issue transits to any of closed statuses.

Here is an example formula for this calculation:

NonZero(AVG(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    -- filter by some date property, typically for closed issues when cycle is completed
    not IsEmpty([Issue].CurrentHierarchyMember.get('Closed at'))
    AND
    DateInPeriod(
     [Issue].CurrentHierarchyMember.get('Closed at'),
      [Time].CurrentHierarchyMember
    )
    AND
    ([Measures].[Issues created],
    [Time].CurrentHierarchy.DefaultMember) > 0
    )
    ,
-- date difference between moving in progress to closing
   DateDiffDays(
      ([Measures].[Transition to status first date],
      [Transition Status].[In Progress],
      [Time].CurrentHierarchy.DefaultMember,
      [Sprint].Defaultmember),
      [Issue].CurrentHierarchyMember.get('Closed at')
   )
 )
)

Time since first set the story-point or an estimation-time to Status 'Done’
eazyBI does not import date when estimates for an issue is set. You can create a JavaScript calculated custom field and find this date based on issue history. You can import the date as property and then use in the similar calculation as above.
However, I would suggest importing already calculated time in days between the date when an estimate is set for an issue and issue resolution date (typically set when an issue moves to Done status).

Here is an example custom field definition with JavaScript code for calculating days from issue estimation date untill resolution date for resolved issues:

[jira.customfield_timefromestimate]
name = "Time from estimate"
data_type = "decimal"
measure = true
javascript_code = ''' 
var estimatedate = null; 
// for resolved issues only
if (issue.fields.resolutiondate){
	label:
	if (issue.changelog && issue.changelog.histories && issue.changelog.histories.length > 0) {
	  var histories = issue.changelog.histories; 
	  for (var i = 0; i < histories.length; i++) { 
	    var history = histories[i]; 
	    if (history.items && history.items.length > 0) {
	      for (var n = 0; n < history.items.length; n++) {
	        var item = history.items[n]; 
	        if ( item.field == 'Story Points' || item.field == 'timeestimate' ) {
	        	if ( item.fromString == null && item.toString && item.to != 0 ) {
	          estimatedate = history.created;
	          break	label;
	          }
	} } } } }
	if(estimatedate) {
		issue.fields.customfield_timefromestimate = (Date.parse(issue.fields.resolutiondate) - Date.parse(estimatedate)) / 1000 / 60 / 60 / 24;; 
	}
}
'''

Add the custom field definition to eazyBI advanced settings or ask JIRA administrator to do this for you, as only JIRA administrators, has access to eazyBI advanced settings. eazyBI advanced settings are global for Jira instance.
Then select the new custom field Time from estimate for import as measure and property for each account you would need this calculation and run an import.
eazyBI will create several measures including Time from estimate resolved and hidden measure Issues with Time from estimate resolved. You can use those measures to calculate an average time in issues from estimation till resolution:

CASE WHEN
  [Measures].[Time from estimate resolved] > 0
THEN
  [Measures].[Time from estimate resolved]
  /
  [Measures].[Issues with Time from estimate resolved]
END

Both formulas will work for the last/current Sprint of an issue.

Daina / support@eazybi.com

Hi Daina
I’m very sorry, i did not see your answer. I’m going to check your answer this week.
Regards, Roman

I used the calculated measure but im getting only minutes instead of days :frowning:

eazyBI has a formatting option to show values as days hours minutes or minutes. However, eazyBI does not apply any additional calculation when you are switching to this formatting.

You would like to make sure you have the data in minutes, to use this formatting. eazyBI can calculate hours and days based on minutes. However, if you have values in days (calculated with DateDiffDays or with JavaScript example above) then you would like to transfer it (multiply) to get minutes (value * 60 * 24).

Daina / support@eazybi.com

hi Daina,

I’m trying to reuse your solution but it obviously won’t work for my setup. We’re using 3 statuses to indicate closed state: Awaiting for deployment, Closed and Done and this is reflected in EazyBI import setup. When I’m using your solution I’m getting average calculated to one of these 3 statuses, which is also usefull, however I don’t understand how it varies from the built-in metric Average Resolution Workdays. Could you please help me to calculate time to specifically “Closed” or “Done” state and only for issues which are in Closed or Done state?

Default measure Average resolution workdays works for the time between Issue creation date till issue resolution date. If you would like to calculate any other period of issue lifecycle, you would like to use some custom calculation with MDX or JavaScript custom field.

In the example JavaScript shared within this post, I counted time from some value that was added to a custom field till the resolution date.

It seems you have a different case there. We have quite a community posts to address cycles by particular statuses.

Two posts on JavaScript calculation examples on statuses:

Two posts using MDX calculations:

Daina / support@eazybi.com

Hi Daina,

thank you for your reply. Seems like this post


does what I need, but I a little bit lost in syntax. How can I modify the formula in order to:

  1. Measure the last transition to 1 of 2 statuses: Closed or Done
  2. Taking into consideration that in EazyBI setup status Awaiting Deployment is also treated as Closed state, but transition to this status only in this case is a part of development cycle
  3. And of course apply only to the issue in Closed or Done status

Hi @daina.tupule ,
i have a similar situation where i wanted to calculate the time between 2 status eg “In progress " to " Done”
with your calculation i could achieve that but it also shows issues which where in done and moved to "in progress " later and then to Done . So the result is in negative
Can you help with calculation how can i eliminate cases giving negative result .

I would suggest using the Issue property Closed date for this calculation instead of any historical calculations to retrieve the last status to Done or Closed. It will work as expected in combination with a filter by issue status in Done or Closed.

Anytime you will Issue property (measure from a section Issue properties starting with the sigular name Issue) instead of historical measure (Transition to/from status first/last date) you will improve a performance of an issue level calculation.

Here is a suggested improvement for the formula based on the mentioned examples using Closed date instead of historical status date:

NonZero(
  Avg(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      DateInPeriod(
          [Measures].[Issue closed date],
          [Time].CurrentHierarchyMember
      )
      AND
       [Measures].[Issue status] MATCHES "Closed|Done"
    ),
    DateDiffworkdays(
      (
        [Measures].[Transition to status first date],
        [Transition Status].[Open],
        [Time].CurrentHierarchy.DefaultMember
      ),
         [Measures].[Issue closed date]
    )
  )
)

Daina / support@eazybi.com

Please check if you can use issue property, for example, Issue closed date or Issue resolution date as a filter and the last date representing a move to status Done. If this is the case, you can use the example I just shared above using Closed date (switch to any other issue property, if needed here).

If you do not have an issue property to represent the last date to some status you can consider precalculating it with JavaScript calculated custom fields. You can check out this community topic to get the date of some status change. The code calculates the first date to some status. You can use it as an inspiration and modify the code to get the last date:

If this does not work for you, you can use historical date measures. I would suggest using Issue properties as only filters and move filters by measures to AVG numeric expression part. However, it might impact the performance of the report significantly. I would suggest using Issue properties as only filters to improve a prformance as much as possible and move filters by measures to AVG numeric expression part:

NonZero(
  Avg(
    Filter(
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
       [Measures].[Issue status] MATCHES "Closed|Done"
    ),
    Case WHEN
      DateInPeriod(
        (
          [Measures].[Transition to status last date],
          [Transition Status].[Done],
          [Time].CurrentHierarchy.DefaultMember
        ),
          [Time].CurrentHierarchyMember
      )
    THEN
    DateDiffworkdays(
      (
        [Measures].[Transition to status first date],
        [Transition Status].[In Progress],
        [Time].CurrentHierarchy.DefaultMember
      ),
      (
        [Measures].[Transition to status last date],
        [Transition Status].[Done],
        [Time].CurrentHierarchy.DefaultMember
      )
    )
    END
  )
)

Daina / support@eazybi.com