Average time to status with excluded resolutions

Questions & Answers
, ,
1

Hi!
I’m trying to calculate the average time for certain bug tickets to move to status ‘planned’
(tickets start from status ‘open’, and travel trough bunch of other statuses until they go to ‘planned’)

We want the report to include tickets that have a certain label, and whose resolution is not ‘cannot reproduce, duplicate, won’t fix, obsolete or future update’.

I wrote a custom formula to measures, to measure the average time. Then I defined calculated members for certain labels and resolutions. Labels work well. Unfortunately the results don’t filter correctly when it comes to excluding the above resolutions.

Could you please help me out on what’s wrong?

Thanks in advance!

The formula used in measures:


NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    DateInPeriod(
      ([Measures].[Transition to status last date],
      [Transition Status].[Planned],
      [Time].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember )
    ),
  Datediffdays(
    ([Measures].[Transition to status first date],
    [Transition Status].[Open],
    [Time].CurrentHierarchy.DefaultMember),
    ([Measures].[Transition to status last date],
    [Transition Status].[Planned],
    [Time].CurrentHierarchy.DefaultMember)
  )
))

The calculated member formula used for resolutions:

Aggregate(
  Except(
  [Resolution].[Resolution].Members,
    {[Resolution].[Cannot Reproduce],
      [Resolution].[Won't Fix],
      [Resolution].[Duplicate],
      [Resolution].[Future Update],
      [Resolution].[Obsolete]}
  )
)

image
image1876×813 55 KB

2

Hi Martha,
​
​Welcome to the eazyBI community.
​
​The change history is being imported for the Resolution dimension. Therefore, the page selection only affects the moment when the transitions from the “Open” and to the “Planned” took place.
If the specific resolution was applied later, that does not affect the used measures.
​
​You need to use some “absolute” measure to check against the current resolution.
The updated expression might then be as follows.
​

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
   DateInPeriod(
      ([Measures].[Transition to status last date],
      [Transition Status].[Planned],
      [Time].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember )
AND
--check for issue relevance to context
  ([Measures].[Issues created],
   [Time].CurrentHierarchy.DefaultMember)>0
    ),
  Datediffdays(
   ([Measures].[Transition to status first date],
    [Transition Status].[Open],
    [Time].CurrentHierarchy.DefaultMember),
    ([Measures].[Transition to status last date],
    [Transition Status].[Planned],
    [Time].CurrentHierarchy.DefaultMember)
  )
))

​However, you might further improve the expression by executing the transition measures only on issues that are checked for relevance to issue type, label, priority, and resolution.
​
Since the relation to resolution is already checked, you might reset the Resolution dimension when checking for transitions.
​The optimized expression might then be as follows.

NonZero(Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
--check for issue relevance to context
  ([Measures].[Issues created],
   [Time].CurrentHierarchy.DefaultMember)>0
    ),
--numeric part for AVG, executed on reduced dataset
  CASE WHEN
--issue relevant for period
   DateInPeriod(
      ([Measures].[Transition to status last date],
      [Transition Status].[Planned],
--reset resolution to avoid excess aggregation
      [Resolution].CurrentHierarchy.DefaultMember,
      [Time].CurrentHierarchy.DefaultMember),
      [Time].CurrentHierarchyMember )
  THEN
  Datediffdays(
   ([Measures].[Transition to status first date],
    [Transition Status].[Open],
--to avoid excess aggregation
    [Resolution].CurrentHierarchy.DefaultMember,
    [Time].CurrentHierarchy.DefaultMember),
    ([Measures].[Transition to status last date],
   [Transition Status].[Planned],
--to avoid excess aggregation
    [Resolution].CurrentHierarchy.DefaultMember, 
    [Time].CurrentHierarchy.DefaultMember)
  )
  END
))

Regards,
​Oskars / ​support@eazyBI.com
​

3

Hi Oskars,
Thank you very much for your help! Your formula seem to work, now I don’t see those unwanted resolutions.

Have another question about this please, as I understand this formula is currently calculating average days the tickets spend until the reach the planned status.

Would it be possible to articulate this in the form of average workdays?