Hello everyone,
I need to create a formula that calculates the resolution working days starting counting from status “In progress”, taking into account that it can go through the “In progress” state several times, so the calculation must start counting from last change to “In progress”.
Is it possible to do the same but starting to calculate from the last assignment of a ticket to a user ?
Thank you very much
Hello @JTejada
Check EazyBI Demo account (e.g. Issue days in selected status - Issues - Jira Demo - eazyBI), at least you may find something to start from.
Report definitions can be exported and replicated in your account, thus all the calculated members or measures will be available at your end.
Kind regards,
VasileS.
I am not sure if/how this is possible… Never needed to see a ticket’s updates on “assignee” field.
Kind regards,
VasileS.
I find this can help me
But I need average resolution workdays starting counting from last change in field “assignee” to resolution date
Can anyone help me ?
Hi @JTejada
@VasileS is right about using DateDiffdays() function with transition dates (and now).
You can use a similar approach also for the transition in the Assignee field (as well as in other fields where you import change history). For that, use the hidden change history dimension “Transition Field” with the needed field (Assignee) and measure “Transition to last timestamp” to get the timestamp of the last assignee change date. Read more about them in doc: Import issue change history
The calculation would be the following:
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember)
),
Now()
)
Remember to set the needed date formatting (decimal, I suppose). If you need to display values as a duration (like 3d 23h 05m), use DateDiffMinutes() function instead, as you need the result in minutes for converting it to duration.
Best,
Ilze, support@eazybi.com
Thank you very much @ilze.leite but i need calculate time from last change in field Assignee to Resolution date.
Can you help me ?
Hi,
you may use the same approach, only substitute Now() for the issue resolution date. When you use DateDiffDays function, there is need for any two dates, so use whichever is needed for them.
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember)
),
[Measures].[Issue resolution date]
)
It works in a typical use case assignee is not changed after the issue is resolved, as it takes the very last transition to assignee, independently of resolution date.
Best,
ilze
Thank you very much @ilze.leite it works !
Now I need to show the average time in days for each user without showing the data for each ticket (issue) and only count resolved tickets. If I remove the Issue field from the rows, the result is empty and I only want the average time for each user to appear.
Can you count the days without taking into account weekends or holidays?
Thank you so much for everything
Hi @ilze.leite
Is this formula correct for what I want?
Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
– for unresolved issues only
not isEmpty([Measures].[Issue resolution date])
),
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember)
),
[Measures].[Issue resolution date]
)
)
Hi, @ilze.leite
The problem is when a ticket is resolved at a given time and after this is changed the Assignee, in this case the value is negative and should not count in AVG.
How can I fix this ?
Hi @JTejada ,
Great, your average calculation formula seems to be correct.
Now about negative values: if you want to skip those issues with transition after the resolution date, then add a condition to check both dates and exclude such issues:
Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- for resolved issues only
not isEmpty([Measures].[Issue resolution date])
),
case WHEN
DateCompare(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember)),
[Measures].[Issue resolution date])<=0
then
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember)
),
[Measures].[Issue resolution date]
)
END
)
If you always want to find and use the last assignee transition before resolution date, then it would get too complicated for the calculation. Then the best approach would be inventing JavaScript calculated custom field that, during data import, goes through each issue changelog and retrieve days for the last assignee; it would be something similar to the already mentioned Days for assignee scripted custom field.
Best,
Ilze , support@eazybi.com
Thank you very much @ilze.leite,
Now I have a new problem.
I need show values by month (issues resolved by month). If I add a row with time i get the same value in all months.
What is the problem ?
Also, with this formula many times I get a timeout. Can the performance of the formula be improved in any way?
Thank you very much again
Hi
In the formula, the Time dimension is used in All times level to avoid limiting the time period when the assignee transitions have happened, therefore Time selection is not applied to the formula.
To filter issues by the issue resolution date, you may include the filter in the formula together with the issue property “Issue resolution date” - instead of checking its existence, check if it is within the particular time period. In such a way, you apply time only on the issue resolution date.
Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
-- for resolved issues within selected time period in rows
DateInPeriod(
[Measures].[Issue resolution date],
[Time].CurrentHierarchyMember)
),
case WHEN
DateCompare(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember)),
[Measures].[Issue resolution date])<=0
then
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assignee],
[Time].CurrentHierarchy.DefaultMember)
),
[Measures].[Issue resolution date]
)
END
)
Best,
Ilze, support@eazybi.com