When using the predefined measure Days Assigned and choose all times in time dimensions, tickets are calculated correctly how many days it stayed assigned to a specific team, but if for example I want to see the result for April, I am getting a completely different result which does not reflect the reality and does not even reflect how many days it stayed assigned in April… What could be the reason for this and is there a workaround? I spent already a lot of time on this…
1 Like
Hi @natali,
The measure “Days assigned” is based on the issue change history. It works similarly to the “Days in transition status” measures, where eazyBI returns the duration once an issue has transitioned out of that particular status.
With “Days assigned”, the duration is presented on Time dimension periods when the assignee changed to a different one or the issue was resolved. In other words, the measure doesn’t show the duration the issue is currently assigned to the user.
Share more details of what you are after and how your report looks.
Best,
Roberts // support@eazybi.com
Hey @roberts.cacus,
Thank you so much for your answer!
I am trying to get the total assignment days including reassignments for a particular team.
Example:
For April, I have a hundred open tickets.
each ticket has been assigned to team X multiple times and reassigned to team y and z.
I need the total assignment days for team X for each open ticket and I get the correct days only when I am using it with “All Times”. If I change it to “previous month” instead, I am not getting the assignment days of team X for April, but a totally different number.
Do you think it is achievable at all?
Please help me out with this:(
Hey again, @roberts.cacus
Is there a way to see the source code of the Days Assigned measure?
I would like to use and modify it, so it takes the current assignment as well.
Thank you for your time in advance!
Hi @natali,
The “Days assigned” measure is predefined and can’t be modified. A calculated measure could be defined, but to suggest a formula, I need to see the report. Please share a screenshot (with sensitive information covered), share a description of what dimensions you use in the report, or export and share the definition of the report - Export and import report definitions.
Best,
Roberts // support@eazybi.com
@roberts.cacus thank you so much for trying to help! Report Definition:
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Average Days Assigned per Phase",
"folder_name": "TEST1",
"result_view": "bar_chart",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Days assigned]"],"members":[]}]},"rows":{"dimensions":[{"name":"Resolution","selected_set":["[Resolution].[All except rejected and duplicates]"],"members":[x],"bookmarked_members":[]},{"name":"Time","selected_set":["[Time].[Last 4 months]","[Time].[Previous month]"],"members":[{"depth":0,"name":"Last 4 months","full_name":"[Time].[Last 4 months]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false,"removed":true},{"depth":3,"name":"Feb 2024","full_name":"[Time].[2024].[Q1 2024].[Feb 2024]","drillable":true,"removed":true,"parent_full_name":"[Time].[Last 4 months]"},{"depth":3,"name":"Mar 2024","full_name":"[Time].[2024].[Q1 2024].[Mar 2024]","drillable":true,"removed":true,"parent_full_name":"[Time].[Last 4 months]"},{"depth":3,"name":"Apr 2024","full_name":"[Time].[2024].[Q2 2024].[Apr 2024]","drillable":true,"removed":true,"parent_full_name":"[Time].[Last 4 months]"},{"depth":3,"name":"Jan 2024","full_name":"[Time].[2024].[Q1 2024].[Jan 2024]","drillable":true,"removed":true,"parent_full_name":"[Time].[Last 4 months]"}],"bookmarked_members":[]},{"name":"Issue Type","selected_set":["[Issue Type].[All except sub-tasks]"],"members":[ ],"bookmarked_members":[]},{"name":"Assignee","selected_set":["[Assignee].[TeamTest2]"],"members":[],"bookmarked_members":[{"depth":1,"name":"TeamTest2","full_name":"[Assignee].[TeamTest2]","key":"TeamTest2"}]}]},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[TEST1]"],"members":[{"depth":1,"name":"TEST1","full_name":"[Project].[TEST1]","drillable":true,"key":"TEST1"}],"bookmarked_members":[{"depth":2,"name":"TEST1","full_name":"[Project.Category].[Internal Project].[TEST1]","drillable":true,"key":"TEST1"},{"depth":1,"name":"TEST1","full_name":"[Project].[TEST1]","drillable":true,"key":"TEST1"}],"current_page_members":["[Project].[TEST1]"]}]},"options":{},"view":{"current":"bar_chart","maximized":false,"bar_chart":{"stacked":false,"vertical":true,"swap_axes":true,"data_labels":true,"series_options":{}},"table":{"vertical_header":true}},"calculated_members":[]}
} ],
"calculated_members": [{"dimension":"Time","name":"Previous month","format_string":"","formula":"Aggregate({\n [Time].[Month].CurrentDateMember.PrevMember\n})"},{"name":"All except sub-tasks","dimension":"Issue Type","formula":"Aggregate(\n Except(\n [Issue Type].[Issue Type].Members,\n {[Issue Type].[Sub-task]}\n )\n)","format_string":""},{"name":"Last 4 months","dimension":"Time","formula":"Aggregate(\n [Time].[Month].DateMembersBetween('5 months ago', '1 month ago')\n)","format_string":""},{"name":"All except rejected and duplicates","dimension":"Resolution","formula":"Aggregate(\n Except(\n [Resolution].[Resolution].Members,\n { [Resolution].[Rejected], [Resolution].[Duplicate], [Resolution].[Won't Do]}\n )\n)","format_string":""}]
}
All i am trying to do is get how many days TestTeam2 spent in each ticket
Hi @natali,
Try the following formula:
Sum(
Filter(
Descendants([Issue].CurrentMember,[Issue].[Issue]),
DateBeforePeriodEnd(
[Issue].CurrentMember.Get('Created at'),
[Time].CurrentHierarchyMember
)
AND
IIf(
DateCompare(
Now(),
[Time].CurrentHierarchyMember.StartDate
)>=0,
[Measures].[Issues history],
NULL
) >= 0
),
DateDiffDays(
-- start for assignee
CASE WHEN IsEmpty([Measures].[Transitions to assignee])
THEN
[Time].CurrentHierarchyMember.StartDate
ELSE TimestampToDate(([Measures].[Transition to last timestamp],
[Transition Field].[Assignee]))
END
,
-- end for assignee
CASE WHEN IsEmpty([Measures].[Transitions from assignee])
THEN
CASE WHEN DateCompare(Now(),[Time].CurrentHierarchyMember.NextStartDate) > 0
THEN [Time].CurrentHierarchyMember.NextStartDate
ELSE Now()
END
ELSE TimestampToDate(([Measures].[Transition from last timestamp],
[Transition Field].[Assignee]))
END
)
)
It should work for the report you shared. The edge case where assignees change multiple times in the selected Time dimension period, and the same assignee is assigned multiple times won’t work correctly.
Please look at our documentation page for more information on defining calculated measures - Calculated measures and members.
Best,
Roberts // support@eazybi.com
hey @roberts.cacus
Thank you so much for taking the time to create a measure! Appreciate it, however, I receive a lot of negative results. Do you know why?
Thanks again!
the negative results are all from tickets that have been reassigned more than once for the selected time period. So, in this case this is impossible to trace?
Hi @natali,
Unfortunately, yes. That is the edge case. Multiple assignee changes for the same user in the time period can produce such results, where the last “transition to” assignee happened after the last “transition from” user.
We could potentially alter the calculation, but then it would take into account only the most recent duration in the period. That is, the calculation would ignore the first duration an issue spent with an assignee if it was assigned to another user and then re-assigned to the original one.
You may have to look into JavaScript calculated custom fields to satisfy such a requirement - JavaScript calculated custom fields. Unfortunately, I don’t have an example.
Best,
Roberts // support@eazybi.com