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 
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
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 .
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
I have used a similar formula to grab first in and first out between two statuses.
Is there a way to filter a date range in that calculated measue? For instance I only want to see for that measure > Aug 2020.
I have 3 of these measures on one report, so it would be useful to only show one measure for a certain time period, and the other two for 2 other time periods…
NonZero(Median(
–filter all issues that were completed in a selected period
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateInPeriod(
–date of first transition to status Done
([Measures].[Transition to status first date],
[Transition Status].[Completed],
[Time].CurrentHierarchy.DefaultMember),
–selected period on rows or pages
[Time].CurrentHierarchyMember )
),
–for each issue count days between two transitions
DatediffWorkdays(
–date and time of first transition to status In Progress
([Measures].[Transition to status last date],
[Transition Status].[PDR In Progress],
[Time].CurrentHierarchy.DefaultMember),
–date and time of first transition to status Done
([Measures].[Transition to status last date],
[Transition Status].[Completed],
[Time].CurrentHierarchy.DefaultMember)
)
Hi @Jon_C
Could you elaborate on this?
Would you limit the filtering part for the Aug 2020 or the DateDiffWorkdays calculation should start from Aug 2020?
Martins / eazyBI support
Hey @martins.vanags . Thanks for following up. I was hoping to only show results for the above measure for the time period aug 20 to now. I have 2 other measures that are the same but one would be from time period march 19 to march 20, april to july
20, and aug 20 forward. I wonder if I can limit the results shown to just those time periods as the definition of done changed a few times over that time period, and would like to show on one report.Hi @Jon_C,
If you use the “Time” dimension in report rows you could explore dynamic date filters to limit periods in your report. See more details on our documentation site:
Martins / eazyBI team
Ya those are cool, but as soon as you apply them it limits the measures displayed. I was hoping to filter the results that appear in the rows before theyre in the report. So for measure 1 above, just march to march shows, measure 2 should only show april to july and the third one is missing but it should show only aug 20 forward. Does this make sense or no?
Hi,
Yes, it does make sense.
You could try limiting the single calculation using CASE WHEN validation.
CASE WHEN
DateBeforePeriodEnd(
'Mar 1 2020',
[Time].CurrenthierarchyMember
)
THEN
<formula>
END
Similarly you could use DateAfterPeriodEnd formula to limit the time to oposite side or use both using AND expression to limit the calculation for a specific period ot Time.
Martins / eazyBI team
