Calculating average time in transition for only the first time this transition is done

Hi,

I am trying to build a KPI which shows the time needed to start working on an issue, time between : Open and Ongoing
for that I use the calculated measure “Average workdays in transition status” however the issues can go back the status Open, and what I need is to calculate only the average time spent only for the first time this transition is done in a ticket.
Is it possible to do that ?

Regards,
Philippe

Hi @Philippe_Puy,

Welcome to eazyBI community :slight_smile:

In this case, the “average workdays” won’t fit your requirement as it would include all the transitions to particular status.

You could try creating a new calculated measure using Descendants and measures “Transition to status first date”

NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
  [Measures].[Transitions from status],
  [Transition Status].[Open]
)>0
AND
DateInPeriod(
  (
    [Measures].[Transition from status first date],
    [Transition Status].[Open],
    [Time].CurrentHierarchy.DefaultMember
  ),
  [Time].CurrentHierarchyMember
)
),
DateDiffworkdays(
  (
    [Measures].[Transition to status first date],
    [Transition Status].[Open],
    [Time].CurrentHierarchy.DefaultMember
  ),
    (
    [Measures].[Transition to status first date],
    [Transition Status].[Ongoing],
    [Time].CurrentHierarchy.DefaultMember
  )
)
)
)

Martins / eazyBI support

Hi Martins
Thank you for your help.
But right I don’t succeed to make it work, I obtain a negative result (-2.83), which is kinda awkward, I will try to look deeper into the code to understand what is going on.

Cheers,
Philippe

Hi @Philippe_Puy

I realized I used wrong measure in the DateDiffWorkdays part when issue reach “Ongoing” status for the first time.
The code is slightly updated now
Please try it now.

Martins / eazyBI

Hi @martins.vanags
I understand why I still get some negative results, the thing is we changed the workflow the 2019-07-01 and added the new status open.
So to have a correct report I need to measure only the issues created after the 2019-07-01, is it possible?

Cheers,
Philippe

Hi,

Try JQL filter in your import options.
https://docs.eazybi.com/eazybijira/data-import/jira-issues-import#JiraIssuesImport-JQLQueryFilter
Note it would affect all reports on your eazyBI account.

Martins / eazyBI

Well I’m not interested in filtering all the issues with the import options.
So I’ll figure out another way to get this information.
Thank you anyway.

Philippe

Hi @martins.vanags
i have a similar situation where i wanted to calculate the time between 2 status eg “In progress " to " 2nd level”
with your calculation i could achieve that but it also shows issues which where in 2nd level and moved to "in progress " later and then to 2nd level . So the result is in negative
Can you help with calculation how can i eliminate cases giving negative result .

@agupta

Try using “transition to status last date” in the formula:

NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
(
  [Measures].[Transitions from status],
  [Transition Status].[Open]
)>0
AND
DateInPeriod(
  (
    [Measures].[Transition from status first date],
    [Transition Status].[Open],
    [Time].CurrentHierarchy.DefaultMember
  ),
  [Time].CurrentHierarchyMember
)
),
DateDiffworkdays(
  (
    [Measures].[Transition to status first date],
    [Transition Status].[Open],
    [Time].CurrentHierarchy.DefaultMember
  ),
    (
    [Measures].[Transition to status last date],
    [Transition Status].[Ongoing],
    [Time].CurrentHierarchy.DefaultMember
  )
)
)
)

Martins /eazyBI team

Hello @martins.vanags

Thank you . That worked for me and eliminated the above situation . But it arise to another situation that
the issue went to 2nd level than to in progress and than never went to 2nd level .
In this case also its negative. As i would need only the time spend in in progress before it first move to 2nd level .

is there a way we can filter out the negative cases .

Regards
Aakanksha

Hi,

Try this code to eliminate specific cases:

NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),

DateInPeriod(
  (
    [Measures].[Transition from status first date],
    [Transition Status].[In Progress],
    [Time].CurrentHierarchy.DefaultMember
  ),
  [Time].CurrentHierarchyMember
)
AND
(
  [Measures].[Transitions from status],
  [Transition Status].[In Progress]
)>0
),
CASE WHEN
Datecompare(
(
    [Measures].[Transition to status last date],
    [Transition Status].[In Progress],
    [Time].CurrentHierarchy.DefaultMember
  ),
(
    [Measures].[Transition to status last date],
    [Transition Status].[2nd level],
    [Time].CurrentHierarchy.DefaultMember
  )
  )<0

THEN
DateDiffworkdays(
  (
    [Measures].[Transition to status first date],
    [Transition Status].[In Progress],
    [Time].CurrentHierarchy.DefaultMember
  ),
    (
    [Measures].[Transition to status last date],
    [Transition Status].[2nd level],
    [Time].CurrentHierarchy.DefaultMember
  )
)
ELSE
(
    [Measures].[Days in transition status],
    [Transition Status].[In Progress],
    [Time].CurrentHierarchy.DefaultMember
  )
END
)
)

Martins / eazyBI support

Hello @martins.vanags
I tried this solution but the else part will give me a result of cases
where the issue never went to 2nd level thus adding extra cases
is there a possibility that we have only the condition is true it will give the result before else and in the else it just dont consider any case .
like a if condition without else .

Regards
Aakanksha

Hi,

Yes, you can have just “case when … then … end” without “else” condition

Martins / eazyBI team

Thanks @martins.vanags It worked for me .