How to find Cumulative hours spent between two dates by Transition?

Questions & Answers
#1

Hi ,
Good Day !!

I am working on the requirement of finding reopened (Transition : Closed => Reopened) tickets of projects.

and I want to calculate hours spent between Reopened date and date the ticket Closed again.

I am trying with following code and getting timeout error.
Sum(
– get set of dates between Reopen and closed dates
Filter(
[Time].[Day].Members,
DateBetween(
[Time].CurrentHierarchyMember.StartDate,
[Measures].[Issue Reopened Date],
[Measures].[Date Issue Closed Again])
),
– sum up spent hours on those dates
– [Measures].[Hours spent]
[Measures].[Hours spent]

)

Could anyone please provide me a good solution to calculate hours spent between two dates.

#2

Hello,

Is there any update on my query ?

#3

Hi @Ganesh,

I am sorry this question didn’t get any attention for so long.
This is a quite heavy calculation as it iterates through every day for each issue and that is the reason it times out.
For this case, we would recommend calculating the time between these transition dates using JavaScript calculated custom field or Jira misc custom field in Jira. Unfortunately, we don’t have any examples for this specific use case.

Kind regards,
Robert / eazyBI support

#4

Basically you may want to create a Javascript that would parse the history of each issue, locates first date when issue has been reopened (stores the datetime the history item has been created) and then determines the current status of the issue. This JS will be able to measure how much time issue has been marinated between two statuses.

#5

Hi!
I made something which is similar.

Elapsed time (spent hours) since going Live (date of PROD)
It worked!

Sum (Filter (
Descendants ([Time] .CurrentHierarchyMember, [Time]. [Day]),
DateBetween([Time].CurrentMember.StartDate,Format([Measures].[Profields date of PROD].Value, ‘mmm dd yyyy’),‘today’)
), [Measures]. [Hours spent])