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 Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,516 views 15 20
Read article

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