Average days in transition status over time

Questions & Answers
1

Hello,

I would be grateful for any help concerning the following problem.

I created a report that shows the average days spent in a given transition status.
I would like to build a second report which shows the evolution of this KPI over time.
Assuming the considered period is between 10 weeks ago and now, then I would like the KPI, in week 1, to take into account all the issues that transitioned from the given transition status until week 1. For the week 2 indicator, it should take into account all the issues that transitioned from the given transition status until week 2. And so forth …

I tried adding the time dimension as a row, but the result is not quite what I expected. The values are shown only for certain weeks, so I guess it is not a cumulative measure.

Thank you very much for your help,

T.

1 Like
2

Hi @tomahawk,

I understand that you are looking for the average days in transition status over accumulated transitions in 10 week period you have selected for the report. Cumulative of average days in transition status won’t work in this use case, as it would simply add up average values over selected periods. The logic for KPI would be to divide the accumulated amount of time spent in transition statuses by accumulated transitions during the same period.
You might want to define a new calculated measure (in Measures) using a formula like this:

CASE
WHEN -- when Total row in the report
  CurrentTuple(VisibleRowsSet()).Item(0).Name = '$total_aggregate' THEN
  [Measures].[Average days in transition status].Value
WHEN --for each period where has been any transitions
  NOT IsEmpty([Measures].[Transitions from status]) 
THEN
  -- accumulated value of days in transition status
  Sum(
    Head(VisibleRowsSet(), Rank(CurrentTuple(VisibleRowsSet()), VisibleRowsSet())),
    [Measures].[Days in transition status] )
  /
  -- accumulated value of transitions
  Sum(
    Head(VisibleRowsSet(), Rank(CurrentTuple(VisibleRowsSet()), VisibleRowsSet())),
    [Measures].[Transitions from status] )
END

Average%20days%20in%20transition%20status%20(cumulative)
Average days in transition status (cumulative).jpg2282×1044 266 KB

Note that, the provided calculation works starting from eazyBI version 5.0 where is support for used function VisibibleRowSet() and CurrentTuple().

More on calculated measures and used functions:

Best,
Zane / support@eazyBI.com