Average time in a status

Questions & Answers
1

We have a report that shows us some basic monthly stats including average open days as --average days open of not closed issues
Case WHEN [Measures].[Open Issues (not Closed)] > 0
THEN
Avg(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get(‘Closed at’),
[Time].CurrentHierarchyMember)
),
CASE WHEN
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
CASE WHEN DateInPeriod(Now(), [Time].CurrentHierarchyMember)
THEN DateDiffDays([Issue].CurrentMember.get(‘Created at’),
Now())
ELSE DateDiffDays([Issue].CurrentMember.get(‘Created at’),
[Time].CurrentHierarchyMember.NextStartDate)
END
END
)
END

We would like to know for these issues what the average number of days the issue is not workable (Status = Blocked or Awaiting Information). My first pass has been to get an average for one of these so I can figure it out and I came up with:

Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
– filter with measure to pick issues based on dimension usage in the report and validation if issue has a transition from status In progress
([Measures].[Transitions to status],
[Transition Status].[Blocked])>0
AND
– filter by issue transition date from status In progress
DateInPeriod(
([Measures].[Transition to status first date],
[Transition Status].[Blocked],
[Time].CurrentHierarchy.DefaultMember),
[Time].CurrentHierarchyMember
)
),
– calculated time in days between first transition date to status In progress and first transition to status Done:
DateDiffDays(
([Measures].[Transition to status first date],
[Transition Status].[Blocked],
[Issue].CurrentHierarchy.DefaultMember),
([Measures].[Transition to status last date],
[Transition Status].[Blocked],
[Issue].CurrentHierarchy.DefaultMember)
)
)

The problem is that when this is used on a monthly report, it is reporting the average for the life of the issue, not just the average up until the reported month. I am also concerned that the denominator used for the average may be a different count for the 2 averages.

  1. by using Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]), are they using the same count for the average?
  2. How can I get the query to understand prior periods? This is complicated as the issue may still be in the status or not.

image

2

Hi,

The complete solution for this use case cannot be implemented by checking the transition dates. There can be several transitions between the “not workable” statuses, and we cannot correctly sort the transition order to exclude the time in the not workable statuses.

The way to go seems to be using the Days in transition status and aggregating the “workable” statuses for the average.

The solution is still quite complex and with a high risk of performance issues. The tricky part is to sort out the time in workable status for the previous period, including checking that the issue is in a workable status at the end of the period.

I tried the following code for that:

Avg(
Filter(Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateBeforePeriodEnd(
[Issue].CurrentMember.get('Created at'),
[Time].CurrentHierarchyMember) AND
NOT DateBeforePeriodEnd(
[Issue].CurrentMember.get('Closed at'),
[Time].CurrentHierarchyMember)
),

Sum(
  {PreviousPeriods([Time].CurrentHierarchyMember),[Time].CurrentHierarchyMember},
  ([Measures].[Days in transition status],
   [Transition Status].[In Progress])
)+
CASE WHEN
 ([Measures].[Issues history],[Transition Status].[In Progress])>0
THEN
 DateDiffDays(
   TimestampToDate(
     Max({PreviousPeriods([Time].CurrentHierarchyMember),[Time].CurrentHierarchyMember},
      ([Measures].[Transition to status last timestamp],
      [Transition Status].[In Progress])
    )
   ),
   CASE WHEN
     DateInPeriod(Now(),[Time].CurrentHierarchyMember)
   THEN
     Now()
   ELSE
     [Time].CurrentHierarchyMember.NextStartDate
   END
  )
END
)

I used the In Progress as a single status to count the days. You can create aggregated in the Transition status dimension if you need to aggregate more statuses as “workable”.

Kindly,
Janis, eazyBI support