EazyBI - Time in Status

[Ariel Rosa]

Hello guys,

I have been looking for a config in EazyBI for a while now without success.

I need a way to calculate the average time issues stay in a specific status, like:

Bar 1 = Number of issues in 'To Do' Status
Bar 2 = Average time issues stay in 'To Do' Status

 The 'Average Days in Transition' that comes as Default with EazyBI is not quite that, it calculates the average time is takes issues to get to that transition, if i'm not wrong.

Thanks for any insights on this issue. 

[Lauma Cīrule · Accepted solution]

Hi Ariel!

The 'Average days in transition status' is the measure you are looking for. It is showing average days that issue stayed in status that is selected in Transition status dimension.

It is calculated when issue is transitioned from this status (when Transitions from status measure is recorded) and will be shown on Time dimension on date when transition from this status happened. If you are not using the Time dimension then the Average of 'all time' will be displayed. 

Please see example report I created with To Do and In Progress statuses selected on rows. If you click on any of the bars and select 'Drill through issue' you would see which issues are included in the calculation and can go to JIRA to double check the time they they spent in status.

Please let me know if you have any additional questions!
Lauma / support@eazybi.com 

[Lauma Cīrule]

The links in above answer are outdated. Please see the description of Average days in transition status here. 

There is also a new example for a report with Days in current status. If you do not see values in this report, please check the last import date in analyze tab of the account - it might be that the latest values (e.g. updated in last 8 days) have not been imported.

Lauma / support@eazybi.com

[Peter Andréasson]

Hi Lauma,

How could you combine Ariels example above as Bar 2 as bars and Bar 1 as line?
I can never get it to work together.

//Peter

[Lauma Cīrule]

Hi Peter,

To change the style of each measure, you can click on the label color box. See example here https://docs.eazybi.com/display/EAZYBI/Create+charts#Createcharts-Customizemeasuredisplayoptions and let me know if you have any other question about this!

Lauma / support@eazybi.com

[Krzysztof]

Hello Lauma,

There is a problem with the 'Average days in transition status' when there is more than one transition in an issue to a status. When you make transitions within one issue (e.g.):

Registered -> InProgress (1 day here) -> Suspended -> InProgress (3 more days here) -> Closed

the total number of days in InProgress status is 4 but only 2 on average. I would say the result of 4 days is more natural when it comes to calculate the average days in transition status for this issue. And it is getting even worse when your jira configuration allows this kind of transition:

InProgress -> InProgress

which is not so unusual. Problem accumulates in larger sample of issues taken to the average calculation.

Is there an easy way to calculate the average days in transition status but not from the status change perspective but from total time in transition status within the issue perspective? Dividing not by number of status transitions but the number of issues should be considered somehow to the average calculation.

[Zane eazyBI Support]

That's true, measure Average days in transition status is calculated Time in transition status divided by transitions count from that status. If an issue has been in status "In Progress" for two times, then average time in status "In Progress" is half of a total time spent in transition status "In Progress" for that issue.

You may create a calculated measure in Measures to calculate the average time based on issue count instead transition count.
A formula for the calculated measure Average days in transition status by issue count would look like this:

CASE WHEN [Measures].[Transitions from status issues count] > 0 THEN
  [Measures].[Days in transition status] /
  [Measures].[Transitions from status issues count]
END

Zane / support@eazyBI.com

[Krzysztof]

Thx Zane,

Works perfect.

[James Weaver]

Hi Zane, 

I've used this measure and seems to be calculating correctly, however the figures EazyBi is using to actually make the calculation appear to be flawed.

For example, I have 3 issues that moved out of "Triage" status in one week:

1 = 0.01 days

2 = 0.96 days

3 = 59.99 days

The average days in triage status for issues that week is therefore approx. 20.3 days, this is correct however issue 1 in the above example has been moved out of Triage status twice, once after 25 days and once after 10 seconds.

When calculating the average it seems to only be taking the most recent transition into account instead of the total time it has spent in Triage status regardless of transitions. Is there a way to include total time an issue has spent in a status in all transitions before calculating the average time all issues that week spent in a particular status?

Thanks

[Daina Tupule eazyBI]

I noticed several unanswered questions here.

1. from Neil. Days in a current status report shows an average calculation when you work with all issues grouped by selected dimensions on Rows. On an issue level it will show total days in status.

We made a new demo report Issue days in current status

2. from Hash. Time in status split by assignee, status, group. By default, eazyBI supports calculations for time in status. Default measures do not split this time by assignee. You can add a calculation during import to with calculated JavaScript custom field to split total resolution days of an issue by assignees. You can modify the code for any other scenario as well.

3. from Ramaiah. Total resolution days of issue, except time in statuses. You would like to use some custom calculations for this now. Here is an example calculation for total days in a set of statuses (cycle) for resolved issues. I have cycle defined by statuses In Progress and In Review:

IIF(
 [Time].CurrentHierarchyMember is [Time].CurrentHierarchy.DefaultMember,
 -- calculation for default time
 Aggregate({
 [Transition status].[In Progress],
 [Transition status].[In Review]},
 ([Measures].[Days in transition status],
 [Status.category].[Done],
 [Time].CurrentHierarchy.DefaultMember))
 /
 [Measures].[Issues resolved] ,
 -- per period calculation on issue level
 NonZero(AVG(
 Filter(
 Descendants([Issue].CurrentMember, [Issue].[Issue]),
 DateInPeriod(
 [Measures].[Issue resolution date],
 [Time].CurrentHierarchyMember
 )
 AND
 [Measures].[Issues resolved] > 0),
-- aggregattin list of cycle transtion statuses
 Aggregate({
 [Transition status].[In Progress],
 [Transition status].[In Review]},
 ([Measures].[Days in transition status],
 [Time].CurrentHierarchy.DefaultMember))
 )
 )
)

Daina / support@eazybi.com

[Ramaiah Pendli]

Hi Daina,

For each issue, can we get how many days in that status like it should show for all status.

Thanks

Ramaiah Pendli

[Ramaiah Pendli]

Hi Lauma,

I need to calculate the total workdays age of each and every issues which i need to exclude the last status - closed, In that total age of one issue, i need to exclude one status stayed age.

This is required for SLA calculation and which I need to exclude the pending on customer issue, which tickets are moving to "Pending on Customer" status. 

Thanks!

[Daina Tupule eazyBI]

We created a new report for tracking last status change.

Please see our new demo report "Days in current status" here:

https://eazybi.com/accounts/1000/cubes/Issues/reports/69885-days-in-the-current-status

[Neil Zaccari]

Hi there! I opened the report, but could not see any of the Colomes, Pages or Row configuration. What I really want is a used defined measure of the number of days in a status, NOT the Average. Is this possible?

[hash]

hello all,

Need report to measure Time In Status --to create reports on issues that show how much time each issue spent on each status, each assignee or each group. This report can be used to identify bottlenecks in  process, to measure performance or even for SLA targets.

Can we achieve through EazyBI??

Need helping answers!!

Thanks

[Lisa Reilly]

Hi @Lauma

I am looking for the same thing as Ariel I think

Say in the example below I have To Do - In Progress - In Review - Done

I've created a report - see diagram:

bi.png

The Average days in transition status shows how long the issue spent in the previous transition.

Our WF goes - Input Queue > In Progress > In Review  > Validation > Done.

DZ-1656 hasn't been In Review for 28.39d, that's how long it was In Progress, and it entered In Progress on 3rd July.

Is there a way to report on how long the issue is in the current status?

Thanks

[Lauma Cīrule]

Hi Lisa! Unfortunately I do not see what Status dimension you are using. The Transition Status dimension should be used with Transition status measures to see historical values. For the current status you would use the Status dimension. Please see an example report here https://eazybi.com/accounts/1632/cubes/Issues#report/test%20days%20in%20current%20status that shows Issue current status and there is also a 'Days in Current Status' calculated measure that calculates difference from Status updated date until now. Please let me know if you have further questions regarding this! Lauma / support@eazybi.com

[Lisa Reilly]

Thanks Laura! I used one of the custom calculated measures you had added and will be using plenty more, thanks for sharing that report it's great!! :D

[Lisa Reilly]

Hello Laurma! I have been trying to do another report My WF is Input Queue > In Progress > In Review > Validation > Done. I want to get the average time it takes for issues to get from Input Queue to Validation (to go through part of the Workflow) Could you explain/do a sample report? It would be of great help I have a WF with about 20 steps in it and want to get the average time from Step 5 to Step 7, Step 11 to Step 15 and so on Thanks!!!

[Lauma Cīrule]

Hi Lisa, I am sorry I am getting back to you regarding this follow up question only now! You can aggregate several statuses to see average days in transition for those statuses. Please see an example report here https://eazybi.com/accounts/1632/cubes/Issues#report/test%20Average%20days%20in%20transition%20status. Here is also a short description about Aggregate(..) function https://docs.eazybi.com/display/EAZYBI/Aggregate. Kind regards, Lauma / support@eazybi.com

[Alex Benavides]

Thanks a lot for the "Days in Current Status" !!!

[Vinay Vira]

Hello,

I am looking for "Days in current status" could you please assist me on this. I am unable to see the example report link on the above comment.

Thanks
Vinay

[Shob]

Hello Lauma,

The example reports are empty.

Do you hava a better links?

[Lauma Cīrule]

Hi, @Shob !

As my colleague Daina wrote in the following reply, we have created a new report for tracking the last status change. Please see it in the eazyBI demo account here: https://eazybi.com/accounts/1000/cubes/Issues/reports/69885-days-in-the-current-status

Lauma / support@eazybi.com

[Shob]

I am looking for the "aggregate several statuses" example report. 

https://eazybi.com/accounts/1632/cubes/Issues#report/test%20Average%20days%20in%20transition%20status

Not working :)

Thanks

[Shob]

How can I make report like this but with time?

https://eazybi.com/accounts/1000/cubes/Issues/reports/50039-status-changes-overview

I want to see how much time it takes for issues to get from one status to another..

[Lauma Cīrule]

@Shob,

I see. Here is a similar report in the eazyBI demo account: https://eazybi.com/accounts/1000/cubes/Issues/reports/187032-cumulative-flow-diagram.

Instead of using the Transition status Categories, you can do a custom aggregate of Transition status dimension members:

Aggregate({
  [Transition Status].[Open],
  [Transition Status].[In Progress]
})

Additionally, here is an Average cycle time report: https://docs.eazybi.com/eazybijira/learn-more/learn-eazybi-through-sample-reports/samples-jira-issues/average-time-in-status-cycle-time. You can add Time dimension to it to see how much time issues spent in the statuses (based on when issues transitioned out of the status).

Lauma / support@eazybi.com

[Shob]

Thank you

If I took this report:

https://eazybi.com/accounts/1000/cubes/Issues/reports/50039-status-changes-overview

And switched the measure from "Transition from status" to the measure "Days in transition", its the same result as in the "Average cycle time report" you sent?

And do I need the  aggregate of Transition?

Remainder - I want to  know how much time it takes for issues to get from one status to another..

Thanks

[Lauma Cīrule]

@Shob

Yes, these reports are very similar. They show the total days (historically) of how much issues spent in the status they transitioned out of. The difference is if you wish to see only the status (Transition Status dimension in the Cycle time report) or what status issue transitioned (Transition dimension used in the Status changes overview).

Aggregate should be used if you wish to group some statuses instead of seeing individual statuses. This depends on your reporting needs.

Lauma / support@eazybi.com