Average time to specific resolution

Questions & Answers
,
1

I have a fairly complex custom member, trying to calculate the average number of days from creation (or Estimated Start Date, if it exists) until a ticket is moved to the Released resolution. I am currently including only tickets with the REQUEST label, but I plan to change that to be only those with fixVersion not empty.

At the moment, the calculation is much lower than I would expect, so I know it’s going amiss somewhere.

Any help is appreciated!

Custom Member 
-- created following an example ([Average days till resolution or period end])
NonZero(Avg(
Filter(
  Descendants([Issue].CurrentMember,[Issue].[Issue]),
    DateBeforePeriodEnd(
            [Issue].CurrentMember.get('Created at'),
            [Time].CurrentHierarchyMember)
   AND( NOT DateBeforePeriodEnd(
            [Issue].CurrentMember.get('Resolved at'),
            [Time].CurrentHierarchyMember)
   or 
   DateInPeriod(
            [Issue].CurrentMember.get('Resolved at'),
            [Time].CurrentHierarchyMember))
   AND ([Issue].CurrentMember,[Label].[REQUEST]) > 0
),
Cache(
  -- age of issues resolved during given period
  CASE
    WHEN DateInPeriod(
            [Issue].CurrentMember.get('Resolved at'),
            [Time].CurrentHierarchyMember)
      AND ( [Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember ) > 0 
      AND [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Released'
    THEN
      CASE
        WHEN NOT isEmpty([Measures].[Estimated Start Date])
          AND [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Released'
        THEN
          DateDiffDays([Issue].CurrentMember.get('Estimated Start Date'),
          [Issue].CurrentMember.get('Resolved at'))
        WHEN isEmpty([Measures].[Estimated Start Date])
          AND [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Released'
        THEN
          DateDiffDays([Issue].CurrentMember.get('Created at'),
          [Issue].CurrentMember.get('Resolved at'))
      END
    -- age of open issues for all periods when issues were open
    WHEN DateBeforePeriodEnd(
              [Issue].CurrentMember.get('Created at'),
              [Time].CurrentHierarchyMember)
      AND NOT DateBeforePeriodEnd(
              [Issue].CurrentMember.get('Resolved at'),
              [Time].CurrentHierarchyMember)
      AND     ( [Measures].[Issues created], [Time].CurrentHierarchy.DefaultMember ) > 0
    THEN
      -- not current time period, age till end of period is calculated
      CASE
        WHEN DateCompare([Time].CurrentHierarchyMember.NextStartDate, Now()) < 0
        THEN
          CASE
            WHEN NOT isEmpty([Measures].[Estimated Start Date])
            THEN
              DateDiffDays(
                [Issue].CurrentMember.get('Estimated Start Date'),
                [Time].CurrentHierarchyMember.NextStartDate
              )
            WHEN isEmpty([Measures].[Estimated Start Date])
            THEN
              DateDiffDays(
                [Issue].CurrentMember.get('Created at'),
                [Time].CurrentHierarchyMember.NextStartDate
              )
          END
      ELSE
        -- current time period, age till now is calculated
        CASE
          WHEN NOT isEmpty([Measures].[Estimated Start Date])
          THEN
            DateDiffDays(
              [Issue].CurrentMember.get('Estimated Start Date'),
              Now()
            )
          WHEN isEmpty([Measures].[Estimated Start Date])
          THEN
            DateDiffDays([Issue].CurrentMember.get('Created at'),
              Now()
            )
        END
      END
    END)
))
2

It seems you are using our example Average age till resolution report from our demo account and measure Average days till resolution or period end for inspiration on this measure.

The measure in our demo account is complex enough. Your formula has extra complexity. I would suggest to minimize it with some tweaks, and then it would be easier to identify/follow the algorithm.

The start date could vary depending on your data. You would like to define a new calculated measure Start date with the formula:

Cache(CASE
WHEN NOT isEmpty([Measures].[Issue Estimated Start Date])
THEN
[Issue].CurrentHierarchyMember.getDate('Estimated Start Date')
ELSE
[Issue].CurrentHierarchyMember.GetDate('Created at')
END)

You also would like to have a date when issue moved to Resolution name Released. I would suggest creating a new calculated measure End date for this.
You are using Resolution date for issues with this resolution. Do your Jira workflow updates resolution date of issue when you moved to Release resolution?
In this case, End date could be with this formula:

Cache(
CASE WHEN [Resolution].[Resolution].getMemberNameByKey([Issue].CurrentHierarchyMember.get('Resolution ID')) = 'Released'
THEN [Issue].CurrentHierarchyMember.GetDate('Resolved at')
END)

You could use any other formula to represent the End date if I did not get the correct it here. You can use the calculated JavaScript custom field as well to calculate this end date if the rules are more complex to apply within MDX or raise performance issues.

Then you can use the initial formula of Average days till resolution or period end and address the new calculated measures instead of measures Issues created date and Issue resolution date.

In this case, you do not need to add a filter by Resolution. It is already included in the End date formula. End date will be empty even if there is a resolution date for the issue if the issue is not yet in resolution Released.

I also would suggest adding a filter by Label to already existingtuples with measure issues created.

NonZero(Avg(
Filter(
  Descendants([Issue].CurrentMember,[Issue].[Issue]),
  -- filter by issue properties to get only open or resolved issues in period
    DateBeforePeriodEnd(
            [Measures].[Start date],
            [Time].CurrentHierarchyMember)
   AND( NOT DateBeforePeriodEnd(
            [Measures].[End date],
            [Time].CurrentHierarchyMember)
   or 
   DateInPeriod(
            [Measures].[End date],
            [Time].CurrentHierarchyMember))
),
Cache(
  -- age for resolved issues on period when issue was resolved
  CASE WHEN DateInPeriod(
    [Measures].[End date],
    [Time].CurrentHierarchyMember) AND
    ( [Measures].[Issues created], 
      [Label].[REQUEST],
      [Time].CurrentHierarchy.DefaultMember ) > 0 
  THEN
    DateDiffDays([Measures].[Start date],
      [Measures].[End date])
  -- age for open issues for all periods when isses where open
  WHEN DateBeforePeriodEnd(
    [Measures].[Start date],
    [Time].CurrentHierarchyMember) AND
    NOT DateBeforePeriodEnd(
    [Measures].[End date],
    [Time].CurrentHierarchyMember) AND
    ( [Measures].[Issues created],
      [Label].[REQUEST],
      [Time].CurrentHierarchy.DefaultMember ) > 0
  THEN
    -- not current time period, age till end of period is calculated
    CASE WHEN DateCompare(
        [Time].CurrentHierarchyMember.NextStartDate, Now()) < 0
    THEN
      DateDiffDays(
        [Measures].[Start date],
        [Time].CurrentHierarchyMember.NextStartDate
      )
    ELSE
    -- current time period, age till now is calculated
      DateDiffDays([Measures].[Start date],
        Now()
      )
    END
  END)
))

I hope I got the idea and pattern of the formula correctly as it is too far complex to get correct reverse engineering there. Therefore simplifying the formula would be the first step. The simpler formula allows easier reading and spotting mistakes in logic.

Daina / support@eazybi.com