Create ETA based on Cycle Times

Questions & Answers
, , ,
1

Hi all, I want to automatically calculate estimated dates for delivering tickets based on cycle dates from the previous months
So I have created the following report

image
image508×726 31.1 KB

The estimated delivery date that I am calculating uses the following calculation:

CASE WHEN 
  [Measures].[Issue status]="Raised" OR [Measures].[Issue status]="Reopened" OR [Measures].[Issue status]="PENDING" OR [Measures].[Issue status]="Blocked"THEN 
  DateAddDays("today",Int([Measures].[Days in transition status]))
    ELSE
    CASE WHEN [Measures].[Issue status]="In Progress"  OR [Measures].[Issue status]="On Hold"  THEN 
    DateAddDays("today",13 - Int([Measures].[Days in transition status]))
      ELSE
      CASE WHEN 
        [Measures].[Issue status]="Waiting Dev Review" or [Measures].[Issue status]="Dev Reviewing" or [Measures].[Issue status]="Waiting Code Fix" or [Measures].[Issue status]="In Code Fix" or [Measures].[Issue status]="Waiting Dev Review 2" or [Measures].[Issue status]="In Dev Review 2" or[Measures].[Issue status]="Waiting Code Fix 2" or [Measures].[Issue status]="In Code Fix 2"      
        THEN 
        DateAddDays("today",7-Int([Measures].[Days in transition status]))
      ELSE
      CASE WHEN 
        [Measures].[Issue status]="Generating Bundle" OR 
        [Measures].[Issue status]="Waiting Bundle"  
      THEN 
        DateAddDays("today",3-Int([Measures].[Days in transition status]))
      ELSE 
      CASE WHEN 
      [Measures].[Issue status]="Waiting Dev QA Test" OR
      [Measures].[Issue status]="In Test" OR
      [Measures].[Issue status]="Waiting Bug Fix" OR
      [Measures].[Issue status]="In Bug Fix" OR
      [Measures].[Issue status]="In Reopened Fix" 
      THEN 
      DateAddDays("today",2-Int([Measures].[Days in transition status]))
      ELSE 
DateAddDays("today",0)
      END
    END
  END
END
END

Now, instead of using in this calculation the constant values, for example, in this case:

> CASE WHEN 
>         [Measures].[Issue status]="Generating Bundle" OR 
>         [Measures].[Issue status]="Waiting Bundle"  
>       THEN 
>         DateAddDays("today",**3**-Int([Measures].[Days in transition])

The constant from this case is 3 because I have a table with all the cycle times, so I manually get the int as the picture shows, which is the Average Bundle Cycle Time from the last month.
Now, instead of having to input this number automatically, I would like to replace the constant using a calculation , I would have something like this

> CASE WHEN 
>         [Measures].[Issue status]="Generating Bundle" OR 
>         [Measures].[Issue status]="Waiting Bundle"  
>       THEN 
>         DateAddDays("today",int([Measures].[AVG Bundle Cycle Time month ago]-Int([Measures].[Days in transition])

The problem is that I cannot do it this, because the row for this table are tickets that are not resolved, and all my cycle calculations uses resolved (on Bundle Cycle Time , for instance, is

Aggregate
(
{

    [Measures].[Waiting Bundle days of resolved issues],
    [Measures].[Generating Bundle days of resolved issues],
    [Measures].[Waiting Dev QA test days of resolved issues],
    [Measures].[In Test days of resolved issues],
    [Measures].[Waiting Bug fix days of resolved issues],
    [Measures].[In bug fix days of resolved issues]  
  }
)

/[Measures].[Issues resolved]

Can you help me with this?

2

Hi @Cesar_Daldosso,

The approach of taking the average days in transition status in the previous month would only work if you have a statistically significant number of issues passing through each of these transition statuses in that time period.

The issue that passed through “In progress” two months ago and was resolved in the previous month, would not return the time spent in “In Progress” when the inquiry is built for the previous month.

In order to retain the “weight” of each transition status - the expression should consider each transition status separately.

The total cycle time, considering the average time spent in each status in the previous month, might be as follows.

Cache(
Sum(
--list of statuses to sum the time spent within them
{[Transition status].[Raised],
 [Transition status].[Reopened],
 [Transition status].[PENDING],
 [Transition status].[Blocked],
 [Transition status].[In Progress],
 [Transition status].[On Hold],
 [Transition status].[Waiting Dev Review],
 [Transition status].[Dev Reviewing],
 [Transition status].[Waiting Code Fix],
 [Transition status].[In Code Fix],
 [Transition status].[Waiting Dev Review 2],
 [Transition status].[In Dev Review 2],
 [Transition status].[Waiting Code Fix 2],
 [Transition status].[In Code Fix 2],
 [Transition status].[Generating Bundle],
 [Transition status].[Waiting Bundle],
 [Transition status].[Waiting Dev QA Test],
 [Transition status].[In Test],
 [Transition status].[Waiting Bug Fix],
 [Transition status].[In Bug Fix],
 [Transition status].[In Reopened Fix]
},
CASE WHEN
--any issue exited statuse last month
 ([Measures].[Transitions from status],
  [Issue].CurrentHierarchy.DefaultMember,
  [Time].[Month].CurrentDateMember.PrevMember)>0
THEN
--average days in transition status
 ([Measures].[Days in transition status],
  [Issue].CurrentHierarchy.DefaultMember,
  [Time].[Month].CurrentDateMember.PrevMember)
  /
 ([Measures].[Transitions from status issues count],
  [Issue].CurrentHierarchy.DefaultMember,
  [Time].[Month].CurrentDateMember.PrevMember)
END
)
)

However, that is only reasonable when all issues definitely pass through all statuses.

A much more precise option is to filter out the issues that left the specific processing cycle in the previous month and then calculate the average time they have spent in that cycle anytime.
The drawback of that option is that it requires iteration through all issues and, at the same time, the retrieval of changelog measures for each of these issues, and that might be very slow on larger datasets.

Regards,
Oskars / support@eazyBI.com