Hi
I updated to the latest eazybi for jira version and found that fetching linked issues is much better.
With some searching, I was able to calculate the average resolution date of linked issues.
However, I would like to add a condition.
Excluding dropped issues from the linked issues
Average resolution date is based on working days, excluding weekends and public holidays
The calculations below include status(drop) and weekends.
(39.78, 58.45, 54.07 )
-- annotations.group = 4 Issue links and hierarchies
Case
When
[Issue].CurrentMember.Level.Name = "Issue"
AND
[Dev].Currentmember is [Dev].Defaultmember
Then
-- retrieve Bugs information from Issue property Bugs
Case when
not isEmpty([Issue].CurrentHierarchyMember.Get('voclinkedissue'))
then
NonZero(AVG(
[Issue].[Issue].GetMembersByKeys(
[Issue].CurrentHierarchyMember.get('voclinkedissue')),
DefaultContext((
[Measures].[Total resolution days],
[Issue].CurrentMember,
[Time].CurrentHierarchyMember))
))
end
Else
-- total calculation for any issue, data on Bugs level
NonZero(AVG(
Filter(
Descendants([Dev].Currentmember, [Dev].[Dev]),
-- filter out Bugs with reference to Issue
([Measures].[Issues created],
[Time].CurrentHierarchy.Defaultmember) > 0),
-- search for Bug in Issue dimension, use in a tuple with measure and time, ignore anything else with DefaultContext
DefaultContext((
[Measures].[Total resolution days],
[Issue].[Issue].GetMemberByKey([Dev].Currentmember.Key),
[Time].CurrentHierarchyMember, -- allow selection by Time
[Dev].DefaultMember
))
))
End
hi,
I reflected the weekend work exclusion through search as [Measures].[Total resolution workdays].
I have additional questions.
This item is not searchable in Measures. Is it supported by default?
I want to change the Created date and Resolution date to custom date fields (start date and end date). I wonder how to modify it. I would also like to exclude weekends from this field.
I know you’re busy, but I’d appreciate it if you could check the comments and give me some advice.
Thanks for posting your question!
Glad to hear you find the import of issue links easier now. And well done for attempting to find the solution yourself as well! To answer your question about “Total resolution workdays” - it is a hidden measure in eazyBI. It means that it is available in the background, you can create a new measure and refer to it, but it is not available in the standard measures.
Here are a few things I can recommend. To check time spent between 2 dates, you can test the below code (at the Issue level) and then include it in your measure, if it returns the expected results.
However, you mentioned that you want to exclude some statuses as well. I recommend considering importing Issue Cycles (Issue cycles) where you define the statuses for which you want to calculate time spent in the particular statuses. Once you have imported the cycle (in measure below I have given an example of a cycle I named “Cycle days 1”) and define a new calculated measure with formula below. It will calculate the average workdays your linked issues spent in the statuses you define in the Issue Cycle
NonZero(AVG(
Filter(
Descendants([Dev].Currentmember, [Dev].[Dev]),
([Measures].[Cycle days 1 workdays]) > 0
),
[Measures].[Cycle days 1 workdays]
))
The average resolution date was reflected as the ‘Start date’ and ‘End date’ of the work linked to the content you recommended.
The situation I want to exclude is not a specific state or condition of the issues.
For example, South Korea has two long holiday in a year. And there are national holidays.
I would like to exclude these calendar-based holidays.
I would like to ask if there is any guidance on how to use it.
For the additional holiday exclusion, I recommend considering adding them in import options - check out this documentation point nr 4 Data from Jira
However, note that when you add these holidays in the particular account import options, all the reports in that account that use any measures containing “workdays” in the measure name will exclude the days you mark as holidays in the import options.