Resolution Time deducting time spent in status

[CarlyBooth]

Hi,

I am using the below calculation to calculate total resolution time minus the time spent in the transition status Draft and Live.

NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember
)
and
[Measures].[Issues resolved]>0
),
DateDiffDays(
[Issue].CurrentMember.get(‘Created at’),
[Issue].CurrentMember.get(‘Resolved at’)
)
-
(
[Measures].[Days in transition status],
[Transition Status].[Draft]
)
-
([Measures].[Days in transition status],
[Transition Status].[Live])
)
)

This works most of the time however there are some tickets that have been transitioned from draft back to draft as per the below example and for some reason this time isn’t being deducted. I tried add on to the deduction the specific transition ‘Draft to draft’ but this caused problems and led to minus figures in the results.

Any help would be apreciated!

Many Thanks
Carly

[CarlyBooth]

During some more testing of this isolated issue I have found that if I remove the time dimension then I get the correct time of 54 days. There seems to be some sort of issue with the Time dimension picking up the time spent in ‘draft to draft’ transition.

Please see below transcript of the transitions from Jira for reference. When using Time dimension the 228 days in draft to draft is not being deducted but the 26d when it moved to Job open is being deducted:

I have also showed the below breakdown for reference:

I really need the Time dimension as I am trying to show if the time taken to process issues over a period of time is decreasing. Ie show all issues that were resolved during December and the total processing time it took for those December issues to be resolved minus these two statuses and how that compared to the issues resolved in January as per below example.

Any help would be greatly appreciated.

Many Thanks

Carly

[roberts.cacus]

Hi @CarlyBooth ,

You are correct. The Time dimension does affect the values displayed by measures. Just like the measure “Issues resolved” will display the number of issues resolved in each month, the measure “Days in transition status” will display the number of days an issue spent in particular status in the period it was transitioned out of the status.

In your case, it could be that some issues were transitioned out of the two statuses in a previous Time dimension period to the one it was resolved in. Thus, the “Days in transition status” values will be empty for the two Transition Status members.

I suggest resetting the context of the Time dimension in the two tuples that retrieve the number of days issues spent in particular statuses. See the suggested formula below:

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentMember.get(‘Resolved at’),
      [Time].CurrentHierarchyMember
    )
    and
    [Measures].[Issues resolved]>0
  ),
  DateDiffDays(
    [Issue].CurrentMember.get(‘Created at’),
    [Issue].CurrentMember.get(‘Resolved at’)
  )
  -
  (
    [Measures].[Days in transition status],
    [Transition Status].[Draft],
    [Time].CurrentHierarchy.DefaultMember
  )
  -
  ([Measures].[Days in transition status],
  [Transition Status].[Live],
  [Time].CurrentHierarchy.DefaultMember)
))

This way, the number of days the issues spent in particular statuses will be retrieved, disregarding the current Time dimension member.

See more details on tuples and resetting context here - Calculated measures - eazyBI for Jira.

Best,
Roberts // support@eazybi.com

[CarlyBooth]

Hi @roberts.cacus ,

Thanks very much. This works but is very slow and often times out. Is there any way to speed this up or could this be converted into a custom field for import?

Many Thanks
Carly

[roberts.cacus]

Hi @CarlyBooth ,

You could achieve a slight increase in performance with the formula below:

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Issue].CurrentHierarchyMember.get('Resolved at'),
      [Time].CurrentHierarchyMember
    )
  ),
  [Measures].[Total resolution days]
  -
  (
    [Measures].[Days in transition status],
    [Transition Status].[Draft],
    [Time].CurrentHierarchy.DefaultMember
  )
  -
  (
    [Measures].[Days in transition status],
    [Transition Status].[Live],
    [Time].CurrentHierarchy.DefaultMember
  )
))

It replaces the DateDiffDays() function retrieving the total resolution days with the hidden measure “Total resolution days”.

You are right. The calculation could be offloaded during the import with a JavaScript calculated custom field. Please have a look at some examples in other community posts. One example suggesting importing this as an interval dimension and a measure is found here - Issue Resolution histogram excluding time blocked - #2 by vanessa.equip. Try to adapt it to fit your use case.

Best,
Roberts // support@eazybi.com