Hi,
I am using the below calculation to calculate total resolution time minus the time spent in the transition status Draft and Live.
NonZero(
Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember
)
and
[Measures].[Issues resolved]>0
),
DateDiffDays(
[Issue].CurrentMember.get(‘Created at’),
[Issue].CurrentMember.get(‘Resolved at’)
)
-
(
[Measures].[Days in transition status],
[Transition Status].[Draft]
)
-
([Measures].[Days in transition status],
[Transition Status].[Live])
)
)
This works most of the time however there are some tickets that have been transitioned from draft back to draft as per the below example and for some reason this time isn’t being deducted. I tried add on to the deduction the specific transition ‘Draft to draft’ but this caused problems and led to minus figures in the results.
Any help would be apreciated!
Many Thanks
Carly
During some more testing of this isolated issue I have found that if I remove the time dimension then I get the correct time of 54 days. There seems to be some sort of issue with the Time dimension picking up the time spent in ‘draft to draft’ transition.
Please see below transcript of the transitions from Jira for reference. When using Time dimension the 228 days in draft to draft is not being deducted but the 26d when it moved to Job open is being deducted:
I have also showed the below breakdown for reference:
I really need the Time dimension as I am trying to show if the time taken to process issues over a period of time is decreasing. Ie show all issues that were resolved during December and the total processing time it took for those December issues to be resolved minus these two statuses and how that compared to the issues resolved in January as per below example.
Any help would be greatly appreciated.
Many Thanks
Carly
Hi @CarlyBooth ,
You are correct. The Time dimension does affect the values displayed by measures. Just like the measure “Issues resolved” will display the number of issues resolved in each month, the measure “Days in transition status” will display the number of days an issue spent in particular status in the period it was transitioned out of the status.
In your case, it could be that some issues were transitioned out of the two statuses in a previous Time dimension period to the one it was resolved in. Thus, the “Days in transition status” values will be empty for the two Transition Status members.
I suggest resetting the context of the Time dimension in the two tuples that retrieve the number of days issues spent in particular statuses. See the suggested formula below:
NonZero(Avg(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember
)
and
[Measures].[Issues resolved]>0
),
DateDiffDays(
[Issue].CurrentMember.get(‘Created at’),
[Issue].CurrentMember.get(‘Resolved at’)
)
-
(
[Measures].[Days in transition status],
[Transition Status].[Draft],
[Time].CurrentHierarchy.DefaultMember
)
-
([Measures].[Days in transition status],
[Transition Status].[Live],
[Time].CurrentHierarchy.DefaultMember)
))
This way, the number of days the issues spent in particular statuses will be retrieved, disregarding the current Time dimension member.
See more details on tuples and resetting context here - Calculated measures - eazyBI for Jira.
Best,
Roberts // support@eazybi.com
Hi @roberts.cacus ,
Thanks very much. This works but is very slow and often times out. Is there any way to speed this up or could this be converted into a custom field for import?
Many Thanks
Carly
Hi @CarlyBooth ,
You could achieve a slight increase in performance with the formula below:
NonZero(Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.get('Resolved at'),
[Time].CurrentHierarchyMember
)
),
[Measures].[Total resolution days]
-
(
[Measures].[Days in transition status],
[Transition Status].[Draft],
[Time].CurrentHierarchy.DefaultMember
)
-
(
[Measures].[Days in transition status],
[Transition Status].[Live],
[Time].CurrentHierarchy.DefaultMember
)
))
It replaces the DateDiffDays() function retrieving the total resolution days with the hidden measure “Total resolution days”.
You are right. The calculation could be offloaded during the import with a JavaScript calculated custom field. Please have a look at some examples in other community posts. One example suggesting importing this as an interval dimension and a measure is found here - Issue Resolution histogram excluding time blocked - #2 by vanessa.equip. Try to adapt it to fit your use case.
Best,
Roberts // support@eazybi.com
Hi @roberts.cacus
Thank you for the above, there was an improvement 
Please could you let me know how would l add an additional criteria to the filter to only pick up issues that had a created date after 1st December 2020?
Many Thanks
Carly
@roberts.cacus
I have found the below solution which works well but please let me know if you feel there is a better alternative in terms of processing time.
NonZero(Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.get(‘Resolved at’),
[Time].CurrentHierarchyMember)
AND
(DateCompare(DateWithoutTime([Measures].[Issue created date]),DateParse(‘2020-01-01’))=1)
)
,
[Measures].[Total resolution workdays]
(
[Measures].[workdays in transition status],
[Transition Status].[Draft],
[Time].CurrentHierarchy.DefaultMember
)
(
[Measures].[workdays in transition status],
[Transition Status].[Live],
[Time].CurrentHierarchy.DefaultMember
)
(
[Measures].[workdays in transition status],
[Transition Status].[Campaign Outline Due],
[Time].CurrentHierarchy.DefaultMember
)
(
[Measures].[workdays in transition status],
[Transition Status].[Production Brief Due],
[Time].CurrentHierarchy.DefaultMember
)
))
Many thanks
Carly
1 Like
Hi @CarlyBooth ,
Good job! The DateCompare() function is a good option to satisfy your requirement. It is up to you to consider the two suggestions below, as they won’t affect the measure performance, rather improve the readability of the MDX formula.
I don’t think the formula requires the DateWithoutTime() and DateParse() functions. Also, you can try to sum the days issues spent in the particular statuses with Sum(). See the suggested formula below:
NonZero(Avg(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.get('Resolved at'),
[Time].CurrentHierarchyMember
)
AND
DateCompare(
[Measures].[Issue created date],
'2020-01-01'
)>0
),
[Measures].[Total resolution days]
-
Sum(
{[Transition Status].[Draft],
[Transition Status].[Live],
[Transition Status].[Campaign Outline Due],
[Transition Status].[Production Brief Due]
},
([Measures].[workdays in transition status],
[Time].CurrentHierarchy.DefaultMember)
)
))
Best,
Roberts // support@eazybi.com
Thank you, this is better
