Avg amount of time in status with respect to time in months

Questions & Answers
1

Hi team ,

I would like to have the avg time taken every month ( past 12 month ) the amount of time taken to any status before moving to another .

the team before helped in getting the time for every issue but if we wan to have avg time per month how can we get .

Regards
Aakanksha

2

Hi @agupta!

For such a report you would use the Average days in Transition status measure together with Transition status and Time dimensions (more about history measures in documentation).
The report would be similar to this https://eazybi.com/accounts/1000/cubes/Issues/reports/50046-unresolved-issues-by-statuses-over-time, but with the Average days in transition status measure instead of Issues history.

Lauma / support@eazybi.com

3

Hi Lauma

Thank you for the reply .
We that i have another condition the case is below

I have a case that I only want to have avg time for the tickets in “in progress ” state before they moved to a state for the first time
“2nd level “ and not all tickets move to 2nd level .

in this case i want to have average no of time the ticket was in in progress state before it moved to 2nd level in each month .

4

Hi @agupta,

Please see the suggestions from our internal discussion below:

You can use the measure “Average days in transition status” with the specific Transition dimension member. In your case, you can create a new calculated measure that forms a tuple. Use the measure “Average days in transition status” and the Transition dimension member “In Progress => 2nd level”. See the formula below:

([Measures].[Average days in transitions status], [Transition].[In Progress => 2nd level])

See a picture of a sample report below:
Screen-Shot-2020-06-30-at-15-45-47
The "AVG days “In Progress => In Review” displays the average number of days issues spent in the status “In Progress” before being transitioned to the status “In Review”. The number of days is displayed in the period the transition happened.

Best,
Roberts // support@eazybi.com

5

Hello @roberts.cacus

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 from status],

 [Transition Status].[In Progress])>0

AND

-- filter by issue transition date from status In progress

DateInPeriod(

  ([Measures].[Transition from status first date],

   [Transition Status].[In Progress],

   [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].[In Progress],

   [Time].CurrentHierarchy.DefaultMember),

  ([Measures].[Transition to status first date],

   [Transition Status].[2nd level],

   [Time].CurrentHierarchy.DefaultMember)

)

)

the above mdx somehow provided the result but this is showing me result in negative . Can you tell how i can just elemenate the negative or the query could only give result where difference is in positive .

Regards
Aakanksha Gupta

6

Hi @agupta,

The number may be negative because some issues transitioned to the status “2nd level” first and only then to “In Progress”. To overcome this, you can enclose the DateDiffDays() calculation inside the Abs() function. That way, the returned number will always be positive.

Best,