Need help calculating workdays in current status

[Vinay Vira]

I am trying to create a Time in Status report in EazyBI using the JIRA source data.

Lets say I have the following scenario:

JIRA issue ABC-123 with following workflow status: Ready, In Prog, Waiting, Closed.

When the issue was created it went into Ready and stayed there for 2 days. It was then moved to In Prog and statyed there for 5 days. Now the issue is in Waiting status and as of today its been 10 days. 

Lets say I imported the data today the eazyBI report is updated with the most current data. 

On the report I can see the 2 days in the Ready column and 5 days in the In Prog column. But I am not seeing the 10 days in the current status "Waiting". As of now the waiting column shows blank. 

I have realized that when I move the issue out of waiting, to the Done status. Then do the import of data again in EazyBI. The report now shows 15 days for waiting. and blank for "Done" status.

This tells me that the report is only able to calculate the time when the issue is in and out of the status. It does not show the days in current status. I would have like to see the report show that my issue is in the current status of waiting with 15 days (as of the last day when the data was imported)

Any suggestions on how I can show the "Workdays in Current status"?

Thanks 

[Emre Toptancı _OBSS_]

Hello @Vinay Vira,

I suggest you use our marketplace app Time in Status for this which can give you these numbers out-of-the-box without the need to write any calculation code. You can use the REST API provided by our app to pull calculated data into easyBI.

Time in Status mainly allows you to see how much time each issue spent on each status and on each assignee. 

       

The app has Consolidated Columns feature. This feature allows you to combine the duration for multiple statuses into a single column and exclude unwanted ones. It is the most flexible way to get any measurement you might want. Measurements like Issue Age, Cycle Time, Lead Time, Resolution Time etc.

The app has Custom Calendars feature which allows you to define custom work calendars (with each having its own working days and working hours) and get your reports based on those calendars. This makes it very easy to report times as business days.

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well. It supports both Company Managed and Team Managed projects.

Time in Status reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts.

And the app has a REST API so you can get the reports from Jira UI or via REST.

  

Using Time in Status you can:

• See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
• Calculate averages and sums of those durations grouped by issue fields you select. (For example, see average InProgress time per project and per issue type.)
• Export your data as XLS, XLSX, or CSV.
• Access data via REST API. (for integrations)
• Visualize data with various chart types.
• See Time in Status reports on Jira Dashboard gadgets

https://marketplace.atlassian.com/apps/1211756/

EmreT

[Neha Panchal]

Hi @Daina Tupule eazyBI ,

Can you please tell me how to calculate- only Workdays in transition status till now?

Within this same report “Days in backlog”

Thanks a lot in advance!

Neha 

[Daina Tupule eazyBI]

The formula in our demo account has changed since the last time I posted the previous answer. You can use a similar formula to Days in transition status till now and replace measure Days in transition status with Workdays in transition status, and function DateDiffDays with DateDiffWorkdays

Here is a suggested code. 

-- days in transition status when issue was in this status in previous times
IIF(
-- if report uses Status dimension instead of Transition status it should work as well:
[Status].CurrentHierarchyMember.Level.Name = "Status" and Not [Transition Status].CurrentHierarchyMember.Level.name = "Transition Status",
([Measures].[WorkDays in transition status],
[Transition Status].[Transition status].GetMemberByKey(
[Status].CurrentHierarchyMember.Key
)), 
[Measures].[WorkDays in transition status])
+
-- days since last transition to this status
NonZero(SUM(Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- for unresovled issues only
IsEmpty([Issue].CurrentHierarchyMember.Get("Resolved at"))
AND
IIF([Transition status].CurrentHierarchyMember.Level.Name = "Transition Status",
[Transition status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
AND
IIF([Status].CurrentHierarchyMember.Level.Name = "Status",
[Status].CurrentHierarchyMember.Name = [Measures].[Issue status], 1)
),
CASE WHEN
[Measures].[Issues history] > 0
THEN
DateDiffWorkDays(
[Measures].[Issue status updated date],
Now()
)
END
))

 
Please note. We support a new option to define Cycles based on statuses. Could you check if this could help for some total cycle calculation? Here is a report example for days in cycle (Average age in progress) till now as well in our demo account: 

Daina / support@eazybi.com

[Neha Panchal]

It helps!

Thank you!

[Neil Zaccari]

I tried using this code and got huge numbers. I tried decomposing it, stating first with Workdays in transition status. In all instances, this number is > 1,500,000

When I add in this calc, the sql times out

[Daina Tupule eazyBI]

You are correct. Measure “Workdays in transition status” counts days for any previous status only. It does not add days any issue spent in current status.

Please check out the measure “Days in transition status till now” in our demo report “Days in backlog”. The measure adds days since the last status transition for current status to any previously counted days in the same transition status. The measure works with Transition status dimension in a report.

Here is the same measure for workdays:

-- days in transition status when issue was in this status in previous times
Cache([Measures].[Workdays in transition status]
+
-- days since last transition to this status
NonZero(SUM(Filter(
 Descendants([Issue].CurrentMember, [Issue].[Issue]),
 IIF([Transition status].CurrentMember is [Transition status].[Transition status],
 [Transition status].CurrentMember.Name = [Measures].[Issue status], 1)
 AND
 [Measures].[Issues history] > 0),
 DateDiffWorkDays(
 [Measures].[Issue status updated date],
 Now()
 )
)))