Avg duration of a set of status

Questions & Answers
1

Hello,

I hope someone can help me with the issues below, I spend nearly 2 days and still do not get it right :frowning:

Taken the workflow
Status1 => Status2 => Status3 => Status4

I want to build up a simple line chart, which gets me the average duration of Status2 and Status3 for all tickets, which transitions from Status3 for a specific month. The code below seemed promising, but when the month of the transition from Status2 to Status3 differs from the one of Status3 to Status4, it simply does not get counted… Anyone with an idea what I am doing wrong here?

Avg(
  Filter(
   [Time].CurrentHierarchyMember,

-- Get all issues, which transitions from "Status3" to "Status4" in the current month
DateInPeriod(
    ([Measures].[Transition to status last date], [Transition].[Status3 => Status4]),
    [Time].CurrentHierarchyMember
  )),

  -- Get the duration of the status "Status2" & "Status3"
  Aggregate(
    {[Transition Status].[Status2],
    [Transition Status].[Status3]}, 
    [Measures].[Days in transition status]
   )
  )

Thanks a lot,
David

2

Hi @davis,

To get the average number of days the tickets spent in the two statuses, try iterate through the Issue dimension “Issue” level members instead if the Time dimension current member. For that purpose, you can use the Descendants() function.

One other thing - you could create a tuple with the measure “Days in transition status” and the Time dimension default member. This way, you will get the number of days from months before the current one. The formula could look similar to the one below:

Avg(
  Filter(
    Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
    DateInPeriod(
      ([Measures].[Transition to status last date],[Transition].[Status3 => Status4]),
      [Time].CurrentHierarchyMember
    )
  ),
  Aggregate(
    {[Transition Status].[Status2],
    [Transition Status].[Status3]},
    ([Measures].[Days in transition status],[Time].CurrentHierarchy.DefaultMember)
  )
)

You can read more about the Descendants() function on the eazyBI documentation page - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/descendants.

Best,
Roberts // support@eazybi.com