Want to exclude calculation for Issue went from "Review to complete status"

Questions & Answers
1

Want to exclude calculation for Issue went from “Review to complete status”
remaining is fine
if the issue moves from “review to complete” & then back to in progress then complete then count should be from in progress to complete (new inprogress)

Account : SQ Reports
Report : Resolved tickets avg resolution days

Query is
CASE WHEN
[Measures].[Issues closed]>0
THEN
NonZero(
AVG(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Issues closed]>0
),
DateDiffdays(
IIF(
(
[Measures].[Transitions to],
[Transition].[Reopening],
[Time].CurrentHierarchy.DefaultMember,
[Status].DefaultMember
)>0
,
(
[Measures].[Transition to status last date],
[Transition Status].[Open statuses],
–[Time].CurrentHierarchy.DefaultMember,
[Status].DefaultMember
),
[Measures].[Issue created date]
)
,
[Measures].[Issue closed date]
)
)
)
END

2

Hi @Ramesh_Mashetty1,

For the calculation, you might want to consider two scenarios:

  • An issue went through Review status without reopening. In this case, you might want to decrease closing time by time in transition status Review. Use a tuple of transition status “Review” and measure “Days in transition status” to get time in review status, if any.
  • An issue was reopened. In this case, you might want to count the time since the last time issue entered in status “Open” or “In Progress”.

To incorporate that logic, you might do the following:

  1. In dimension Transition Status, define a new calculated member that groups Open statuses “Open” and “In Progress”. I will name it “Open statuses without Review” to refer to further calculations.

  2. Update the formula for the calculated measure. You might want to change the order of functions Iif() and DateDiffDays() to sort those two use cases.

     CASE WHEN
 [Measures].[Issues closed]>0
 THEN
 NonZero(
   AVG(
     --filter set of closed issues in selected period
     Filter(
       Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
       --you may add here some more criteria for issues to count closing status
       [Measures].[Issues closed]>0
     ),
     IIF(
       --check if issue is reopened
       ( [Measures].[Transitions to],
         [Transition].[Reopening],
         [Time].CurrentHierarchy.DefaultMember,
         [Status].DefaultMember ) > 0
       ,
       --for reopened issue, time since last entered status Open or In Progress
       DateDiffdays(
         ( [Measures].[Transition to status last date],
           [Transition Status].[Open statuses without Review],
           [Time].CurrentHierarchy.DefaultMember,
           [Status].DefaultMember),
         [Measures].[Issue closed date] )
       ,
       --if not reopened, then total closing time miuss time in Review
       [Measures].[Average closing days]
       -
       ( [Measures].[Days in transition status],
         [Transition Status].[Review],
         [Time].CurrentHierarchy.DefaultMember )
     )
   )
 )
 END

Best,
Zane / support@eazyBI.com

1 Like