Posted by:.

19 replies on “How to calculate “Lead Time” or “Real Average Resolution Days” with eazyBI and JIRA (Part II)”

  1. Pingback: How to calculate “Lead Time” or “Real Average Resolution Days” with eazyBI and JIRA – www.MrAddon.com ®: Jira & Confluence Administration Support Blog & Ethereum Dev Blog – [www.MrAddon.blog] ®

  3. I am a little confused by this article. Should I be creating a new calculated measure or changing the one from the first article you said to create? There are no real clear directions as to what I should do here.
    Thanks for your help!

    Like

    Reply

  4. I am confused by this article compared to the first one. Do I need to update the original Calculated Measure you said to create in the first article or am I creating a new one?

    Like

    Reply

    1. Hello Mike,

      You must create the “Unresolved” Calculated member in Transition-Status dimension.
      And ONLY apply the one (or the two) Calculated Measure (In the Measures dimension) proposed by eazyBI

      Regards

      Like

      Reply

  5. Hello,

    Thanks for writing this blog. I followed the instructions given in this blog to create a Time spent in each status report for all the resolved issues within a given time period.

    I get a warning saying
    “Do not use the other dimension Transition status when defining a calculated member in Transition Status . Create complex calculations in Measures .”
    while creating the calculated member in the Transition status dimension with the given formula as shown below.
    [Transition Status].[Unresolved Status] =
    Aggregate({
    [Transition status].[New],
    [Transition status].[In Squad Review],
    [Transition status].[Ready for grooming],
    [Transition status].[Grooming],
    [Transition status].[Groomed],
    [Transition status].[Ready for Development],
    [Transition status].[In development],
    [Transition status].[Ready for Peer Review],
    [Transition status].[In Peer Review],
    [Transition status].[Ready for testing],
    [Transition status].[In Testing],
    [Transition status].[Ready for Acceptance],
    [Transition status].[In Acceptance],
    [Transition status].[Resolved],
    [Transition status].[Merged]
    })

    Is it okay to ignore this warning ?

    Then I created a calculated measure “Average Flow Time” as given below (following the steps given in this blog post)

    [Measures].[Average Cycle Time] =
    Sum(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentMember.get(“Resolved at”), [Time].CurrentHierarchyMember) AND
    [Measures].[Issues resolved] > 0),
    ([Measures].[Average workdays in transition status],
    [Transition status].[Unresolved status],
    [Time].CurrentHierarchy.DefaultMember)
    ) / [Measures].[Issues resolved]

    But after I activate these user defined measure “Average Cycle Time” and member “Unresolved Status” I get an empty table for Average Cycle Time and Issues resolved. Though there are values in the average workdays in transition status.

    Could you please help debug the issue here and let me know what I am missing. Appreciate your help and response.

    My requirement is to get two reports.
    (1) Average Time spent in individual “in progress” statuses by only the resolved tickets in a given time period.
    (2) Total Cycle Time = Average of all the Time spent in individual “in progress” statuses.

    Could you help here please?

    Like

    Reply

    1. Hello Karthiga

      Yes please ignore the Warning in the calculated measure in the dimension “Transition Status”
      You can try to add this Calculated Measure in “Measures”

      Sum(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod([Issue].CurrentMember.get("Resolved at"), [Time].CurrentHierarchyMember) AND
[Measures].[Issues resolved] > 0),
([Measures].[Days in transition status],
[Transition status].[Unresolved status],
[Time].CurrentHierarchy.DefaultMember)
) / [Measures].[Issues resolved]

      Note the change from “Average workdays in transition” to “Days in transition status”.
      With this measure you can check if the measure match with the value/time in the ticket (you will see that match).

      If you show in rows “Issue” and in columns “Issue Resolution date” you will see all fine BUT
      The problem is that the issues are counted in the IssueCreation date (and not in the resolution date), but you can activate in “Measures” dimension “Issues Resolved”

      Regards

      Like

      Reply

      1. Hello,

        Appreciate your quick response, I tried to follow your instructions, but still get an empty table as a result for the cycle time and the issues resolved 😦

        1. I have selected the calculated member Unresolved status on the rows in the Transition Status
        2. I updated the calculated measure Cycle time with what you said.
        3. Selected All Issues in rows and Issues resolved in columns
        4. Didn’t select the Time dimension.

        Not sure what I am missing, any thoughts on next steps ?

        Thank you.

        Like

  6. Hello, the problem is just the point 1 (1. I have selected the calculated member Unresolved status on the rows in the Transition Status) this is not needed. Try with:
    Measures must be in columns, Time (and or Issue) in rows
    Regards

    Like

    Reply

  7. Hello,

    Thank you for the inputs. It worked now. I modified the calculated measure’s formula to use Workdays in transition status instead Days in transition status.

    Sum(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentMember.get(“Resolved at”), [Time].CurrentHierarchyMember) AND
    [Measures].[Issues resolved] > 0),
    ([Measures].[Workdays in transition status],
    [Transition status].[All Transition Statuses],
    [Time].CurrentHierarchy.DefaultMember)
    ) / [Measures].[Issues resolved]

    Thanks again, appreciate your quick response and help 🙂

    Liked by 1 person

    Reply

  8. Hello,
    One more question: I am trying to plot a “Time Spent in each status” chart.
    The below calculated measure does not consider just the resolved issues in a given time period instead takes into account all of the issues that transitioned out of the statuses in a given time period.

    [Measures].[Average days in transition status] =
    CASE WHEN [Measures].[Transitions from status] > 0 THEN
    [Measures].[Days in transition status] /
    [Measures].[Transitions from status]
    END

    Could you help me formulate the logic to use this in conjunction with the below measure to iterate through the list of resolved issues only?

    (Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentMember.get(“Resolved at”), [Time].CurrentHierarchyMember) AND
    [Measures].[Issues resolved] > 0),
    ([Measures].[Workdays in transition status],
    [Transition status].[All Transition Statuses],
    [Time].CurrentHierarchy.DefaultMember)
    )

    Thanks in advance.

    Like

    Reply

  10. Hello Again,

    A go-back on the issue that I mentioned in the above thread, I modified the calculated measure’s formula to use Unresolved Status instead of All Transition Statuses.

    Sum(
    Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod([Issue].CurrentMember.get(“Resolved”), [Time].CurrentHierarchyMember) AND
    [Measures].[Issues resolved] > 0),
    ([Measures].[Workdays in transition status],
    [Transition status].[Unresolved status],
    [Time].CurrentHierarchy.DefaultMember)
    ) / [Measures].[Issues resolved]

    I get an empty column for the Cycle Time 😦
    Any thoughts on what might be the issue.

    I have Issues, Time and Transition Status (with All Transition Statuses member) selected on the Rows.

    Thanks very much for your help.

    Like

    Reply

  13. Hi! Awesome article, thank. I have 2 questions regarding it:
    1. I’m constantly getting 60-seconds timeout when trying to execute the query from article. Any ideas on optimization?
    2. I have 3 done statuses: Awaiting deployment, Closed, Done. Formula will do the correct calculation when I need to calculate average lead time to any of these 3 statuses. How can I modify the formula, so I could calculate average lead time only to statuses Done and Closed? In this case [Measures].[Issues Resolved] in denominator won’t be calculated correctly

    Liked by 1 person

    Reply

    1. Hello Valerii,
      1.- Please add this to the settings of eazyBI:
      [mondrian.rolap]
      queryTimeout = 120

      2.- Try to add a new calculated member in TransitionStatus to have a second measure of [Transition status].[Unresolved status] and clone the other calculated member of Measures to use the new “Unresolved status” measure.
      Regards!

      Like

      Reply

Leave a comment