Time to first assignment (but accounting for issues assigned during creation)

Questions & Answers
1

I’ve found a number of quite useful tips re: how to capture the number of days elapsed between (created date) and (First Assignee Date), as “Days to First Assignment”. But the solutions I’ve found so far are built on the assumption that an issue is NOT assigned during its creation.

I currently have a [First Assignee Date] calculated member defined as:

– First Assignee date
TimestampToDate(
(
[Measures].[Transition to first timestamp],
[Transition field].[Assignee],
[Time].CurrentHierarchy.DefaultMember
)
)

How do I modify that logic so that, if Assignee is set during issue creation (i.e. to any value other than Unassigned), the ‘First assignee date’ is set to be the same as the ‘created at date’, otherwise it gets the value as calculated above?

This way, the team “gets credit” for immediately assigning a ticket (i.e., 0 days elapsed) rather than just ignoring the assign-during-creation set of data?

If I make this change, I realize that I might also want to change to using timestamps instead of dates, so that instead of being counted as “0 days”, I can count the number of hours for tickets that are created and assigned on the same date, but not upon creation. I.e., to be able to observe, for example that the average time to assign a P0 Bug is 3 hrs instead of “0 days”.

Thanks!

2

Hi @jtlande,
I tested your measure and it returns the timestamp also for the issues that have been assigned at the moment issue is created.
Here is my test example where the issue creation date is the same as the first assignee date:

Assignee_time
Assignee_time1920×1185 134 KB

If you want to use another formatting for your measure that returns currently “0 days” then you need to have your measure result in minutes. For example, use the DateDiffMinutes() function to calculate the duration in minutes between two dates.

Kind regards,
Gerda // support@eazyBI.com

3

Thanks @gerda.grantina . What I can’t figure out is why, using the same measure for First Assignee date, I’m getting no data back in that measure, even though you can see that the issue is assigned:

image
image1920×872 145 KB

4

Hi @jtlande ,
Could you please export and share the definition for your eazyBI report?
https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports#Createreports-Exportandimportreportdefinitions
You can send them to eazyBI support mail if you don’t want to post it here: support@eazyBI.com

I will try to help you once I see these details!

Kind regards,
Gerda

5

@gerda.grantina here’s the report definition:

{
“cube_name”: “Appen Jira Cloud”,
“cube_reports”: [ {
“name”: “Bugs: Production Bugs \u003e SLA (TFA) Performance over time, 2021”,
“folder_name”: “2021”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issue assignee]","[Measures].[Issue created date]","[Measures].[First Assignee date]","[Measures].[Average Days until assigned]","[Measures].[Average Hours until assigned]","[Measures].[Issues created]","[Measures].[Issues closed]"],“members”:null}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time].[2021].[Q3 2021].[Jul 2021]","[Time].[2021].[Q3 2021].[Aug 2021]"],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Time].[Month])”,“members”:null,“bookmarked_members”:null},{“name”:“Issue”,“selected_set”:["[Issue].[Issue].Members"],“members”:null,“bookmarked_members”:null}],“filter_by”:{“conditions”:[{“expression”:"[Time].CurrentHierarchyMember",“operator”:“between”,“value”:“1/1/2021 and today”,“value_type”:“date”}]},“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Issue Type”,“selected_set”:["[Issue Type].[All Issue Types]"],“members”:[{“depth”:0,“name”:“All Issue Types”,“full_name”:"[Issue Type].[All Issue Types]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Bug”,“full_name”:"[Issue Type].[Bug]",“parent_full_name”:"[Issue Type].[All Issue Types]"}],“bookmarked_members”:null,“current_page_members”:["[Issue Type].[Bug]"]},{“name”:“Priority”,“selected_set”:["[Priority].[All Priorities]"],“members”:[{“depth”:0,“name”:“All Priorities”,“full_name”:"[Priority].[All Priorities]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“P0”,“full_name”:"[Priority].[P0]",“parent_full_name”:"[Priority].[All Priorities]"},{“depth”:1,“name”:“P1”,“full_name”:"[Priority].[P1]",“parent_full_name”:"[Priority].[All Priorities]"},{“depth”:1,“name”:“P2”,“full_name”:"[Priority].[P2]",“parent_full_name”:"[Priority].[All Priorities]"}],“bookmarked_members”:null,“current_page_members”:["[Priority].[P0]","[Priority].[P1]","[Priority].[P2]"]},{“name”:“Resolution”,“selected_set”:["[Resolution].[All Resolutions]"],“members”:[{“depth”:0,“name”:“All Resolutions”,“full_name”:"[Resolution].[All Resolutions]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Done”,“full_name”:"[Resolution].[Done]",“parent_full_name”:"[Resolution].[All Resolutions]"}],“bookmarked_members”:null,“current_page_members”:["[Resolution].[Done]"]},{“name”:“Environment (Eng)”,“selected_set”:["[Environment (Eng)].[All Environment (Eng)s]"],“members”:[{“depth”:0,“name”:“All Environment (Eng)s”,“full_name”:"[Environment (Eng)].[All Environment (Eng)s]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Production”,“full_name”:"[Environment (Eng)].[Production]",“parent_full_name”:"[Environment (Eng)].[All Environment (Eng)s]"}],“bookmarked_members”:null,“current_page_members”:["[Environment (Eng)].[Production]"]},{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:"_Client Workspace",“full_name”:"[Project].[_Client Workspace]",“drillable”:true,“key”:“CW”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:null,“current_page_members”:["[Project].[_Client Workspace]"]},{“name”:“Scrum Team”,“selected_set”:["[Scrum Team].[All Scrum Teams]"],“members”:[{“depth”:0,“name”:“All Scrum Teams”,“full_name”:"[Scrum Team].[All Scrum Teams]",“drillable”:true,“type”:“all”}],“bookmarked_members”:null,“current_page_members”:["[Scrum Team].[All Scrum Teams]"]},{“name”:“Time”,“duplicate”:true,“selected_set”:["[Time].[All Times]","[Time].[Last 12 months]"],“members”:[{“depth”:0,“name”:“All Times”,“full_name”:"[Time].[All Times]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:“2021”,“full_name”:"[Time].[2021]",“drillable”:true,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Time].[All Times]"},{“depth”:2,“name”:“Q2 2021”,“full_name”:"[Time].[2021].[Q2 2021]",“drillable”:true,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Time].[2021]"},{“depth”:2,“name”:“Q3 2021”,“full_name”:"[Time].[2021].[Q3 2021]",“drillable”:true,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Time].[2021]"},{“depth”:3,“name”:“Jul 2021”,“full_name”:"[Time].[2021].[Q3 2021].[Jul 2021]",“drillable”:true,“parent_full_name”:"[Time].[2021].[Q3 2021]"},{“depth”:3,“name”:“Aug 2021”,“full_name”:"[Time].[2021].[Q3 2021].[Aug 2021]",“drillable”:true,“parent_full_name”:"[Time].[2021].[Q3 2021]"}],“bookmarked_members”:null,“current_page_members”:["[Time].[2021].[Q3 2021].[Jul 2021]","[Time].[2021].[Q3 2021].[Aug 2021]"]}]},“options”:{“nonempty”:“rows”},“view”:{“current”:“table”,“maximized”:false,“table”:{“freeze_header”:true}},“calculated_members”:null,“description”:“TFA = Time to First Assignment. Calculated as the difference in days between the date Created and the date first assigned. Currently does NOT include issues assigned during the creation step.”}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issue created date”,“format_string”:“mmm dd yyyy”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Created at’)"},{“dimension”:“Measures”,“name”:“Issue assignee”,“format_string”:"",“formula”:"[Assignee].[User].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Assignee name’)\n)"},{“dimension”:“Time”,“name”:“Last 12 months”,“format_string”:"",“formula”:“Aggregate(\n [Time].[Month].DateMembersBetween(‘12 months ago’, ‘today’)\n)”},{“name”:“First Assignee date”,“dimension”:“Measures”,“formula”:"-- First Assignee date \nTimestampToDate(\n(\n[Measures].[Transition to first timestamp],\n[Transition field].[Assignee],\n[Time].CurrentHierarchy.DefaultMember\n)\n) “,“format_string”:“mmm dd yyyy”},{“name”:“Average Days until assigned”,“dimension”:“Measures”,“formula”:“NonZero(Avg(\n Filter(\n Descendants([Issue].CurrentMember,[Issue].[Issue]),\n – issues with the first assignee change in the currently selected Time period\n DateInPeriod(\n [Measures].[First Assignee date],\n [Time].CurrentHierarchyMember\n )\n – issues that had a transition in the Assignee field\n AND\n ([Measures].[Transitions from],\n [Transition field].[Assignee]) \u003e 0\n ),\n – Number of days it took issues to the first assignee change\n DateDiffDays(\n [Issue].CurrentHierarchyMember.Get(‘Created at’),\n [Measures].[First Assignee date]\n )\n))”,“format_string”:”#,##0.00"},{“name”:“Average Hours until assigned”,“dimension”:“Measures”,“formula”:“NonZero(Avg(\n Filter(\n Descendants([Issue].CurrentMember,[Issue].[Issue]),\n – issues with the first assignee change in the currently selected Time period\n DateInPeriod(\n [Measures].[First Assignee date],\n [Time].CurrentHierarchyMember\n )\n – issues that had a transition in the Assignee field\n AND\n ([Measures].[Transitions from],\n [Transition field].[Assignee]) \u003e 0\n ),\n – Number of days it took issues to the first assignee change\n DateDiffHours(\n [Issue].CurrentHierarchyMember.Get(‘Created at’),\n [Measures].[First Assignee date]\n )\n))”,“format_string”:""}]
}

6

Hi @jtlande ,
Thanks for the report definition!
You are using in your report filtering by Resolution field, together with historical measure it filters the assignee date by the resolution at that moment (not by the current resolution). As the issue is created then the Resolution won’t be Done at that moment thus the assignee date is empty.
You can add in your formula ‘[Resolution].DefaultMember’ so the measure ignores selection in the Resolution dimension.

TimestampToDate(
(
[Measures].[Transition to first timestamp],
[Transition field].[Assignee],
[Time].CurrentHierarchy.DefaultMember,
[Resolution].DefaultMember
)
)

Also, filtering by the Priority dimension will retrieve the information for the priority that was at the assignee date moment (and not the current priority). See other histrocial dimensions in the documentation link shared above.

Kind regards,
Gerda