Time in Status problems

Questions & Answers
1

Hello Community,
we want to see the Average time in every status (e.g. “Open”, “In Progress”, “Reallocation”,…).
First we try to see the whole time, so we could see, if the values are right and then we can calculate the average.
We tried to see them throw this code:

NonZero(
Cache(
– days in transition status when issue was in this status previously
[Measures].[Days in transition status] +
– days since last transition to the next status
IIF([Measures].[Issues history] > 0,
DateDiffDays(
( [Measures].[Transition to status last date],
[Time].CurrentHierarchyMember.DefaultMember),
[Time].CurrentHierarchyMember.NextStartDate
), 0
)
)
)

The first Problem is, that first not all status will be shown (e.g. “Open” and “In Progress” will be shown, but not “Reallocation”). Most times just one status will be shown, the second last one (e.g. At the Current Status “Control” there will be shown the time from “Reallocation”).
The second Problem is, that the status, which are shown not are quite right ( it shows less time than it actually is).
The third Problem is, that some few status are shown 0, which is not right and don`t make sense.
The fourth Problem is, somtimes there are values <0 (e.g. -50).

So my Question is, how can i solve the problem?
Thank you

2

The measure might show you the correct values for individual issues. There could be some problems if you are using the measure with Time selection in the report.

Any historical value will show you data related to Time. Days in transition status will show you value in a period when an issue moves from status and will show you a total time an issue was in this status for this status change.

A tuple with measure Transitions to status last date with Time default member will show you the last status change for this issue ever. It might be after a selected period if you are using a Time in the report. Therefore, the formula might give you negative value when you are calculating the time in status until the period end with ([Time].CurrentHierarchyMember.NextStartDate).

[Time].CurrentHierarchyMember.NextStartDate will give you empty value and the last DateDiffDays will give you empty value if Time is not used in the report.

You can check out this demo report to count a time for an issue in the status until now (you would like to use the measure without time dimension).

Daina / support@eazybi.com