Average In Progress vs Wait Time + Avg Time per Transition Status

Hi All,

I’m trying to get the the average In Progress vs Wait Time based on the transition statuses I’ve got. I’ve got “Ready for XYZ” statuses that are used as queues/wait time and I basically want to get the average days between them and the actual in progress statuses.

I first got the total workdays per Transition status (+ current open time) with the following formula:

Calc measure:Workdays in transition status + open
NonZero(
Cache(
– workdays in transition status when issue was in this status previously
[Measures].[Workdays in transition status] +
– workdays since last transition to this status
IIF([Measures].[Issues history] > 0,
DateDiffDays(
( [Measures].[Transition to status last date],
[Time].CurrentHierarchy.DefaultMember ),
Now()
), 0
)
)
)

Then I get the Average by using:

Calc measure: Avg Workdays in transition status
CASE WHEN [Measures].[Transitions from status] > 0 THEN
[Measures].[Workdays in transition status + open] /
[Measures].[Transitions from status]
END

This gets me the average workdays in each column, although it doesn’t give me zeros when certain cards skip columns. It only gets the time they actually sat there. So there might be a better way of getting the average workdays each card spent in each column. Any thoughts?

Then in a different chart, I’m grouping the columns in the Transition Status dimension:
Aggregate({
[Transition status].[In Development],
[Transition status].[In Review],
[Transition status].[In Test],
[Transition status].[Deployment]
})

However, if I use the calc measure “Workdays in transition status + open” it gives me the total days, if I use the calc measure “Avg Workdays in transition status”, it doesn’t give me the totals in the aggregation:

||Workdays in transition status + open|Avg Wkdays in Status Total|
|Value Adding Time|2058.058444|BLANK?|
|Waiting Time|628.1096989|BLANK?|

Calc measure: Avg Wkdays in Status Total
NonZero(
Sum(
Filter(
Descendants([Transition Status].CurrentHierarchyMember,[Transition Status].[Transition Status]),
Not IsEmpty([Measures].[Avg Workdays in transition status])
),
[Measures].[Avg Workdays in transition status]
)
)

Thanks,

Hi @ifritsch

First, if you are looking to display zeros for the average calculation, you can put the formula you are using inside the CoalesceEmpty() function. It will add a specified value for calculation results that return empty. You can read more about it on the documentation page - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members/mdx-function-reference/coalesceempty.

One other thing I noticed - in the “Workdays in transition status + open” formula you are using DateDiffDays() function. It calculates the difference between to days and displays the result in days. If you are looking for workdays, try the DateDiffWorkDays() function - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-members/mdx-function-reference/datediffworkdays.

Please share the definition of the second report and a screenshot of the report. I wasn’t able to replicate the empty values. https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-Exportandimportreportdefinitions.

Also, you can have a look at our Demo account. There is one report in particular that could interest you - https://eazybi.com/accounts/1000/cubes/Issues/reports/61947-issue-days-in-backlog.

Kind regards,
Roberts // eazyBI support