Need help calculating workdays in current status

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 

2 answers

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()
)
)))

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

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,760 views 11 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot