Good morning community!
I hope you can help me
I need to create a report in eazybi that shows me the time in which an issue is assigned to a particular group.
When the issue is created, it is assigned to a work group and during the resolution process, this issue can be assigned to another group and then in turn can be re-assigned to the original group.
My requirement is to make a report that only shows me the total time in which that issue was assigned to a particular group and not the total time from when the issue was created to its final state.
Thank you in advance for your help.
Hi @Saul_Alexander_Mata,
In eazyBI, you may measure how much time issues were assigned to each assignee group using measures representing issue change history. Before going further, make sure you have imported issue history and dimension to represent Assignee Groups in eazyBI:
Then you may define a new calculated measure (in Measures) to go through all issues and for each issue calculate days between the transition to an assignee (or assignee group) and from an assignee (or assignee group). The formula might look like this:
Sum(
--filter a set of issues that changed assignee in a selected period
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Transitions from assignee] > 0
),
--for each issue calculate days between assignee changes
DateDiffDays(
--date when for the first time assigned to assignee or group
TimestampToDate(
([Measures].[Transition to first timestamp],
[Transition field].[Assignee],
[Time].CurrentHierarchy.DefaultMember) ),
--date when for the last time assigned from assignee or group in selected period
TimestampToDate(
([Measures].[Transition from last timestamp],
[Transition field].[Assignee]) )
)
)
For more details how to create calcaulted measures and used functions, see the docmentation:
https://docs.eazybi.com/eazybijira/analyze-and-visualize/calculated-measures-and-members
Best,
Zane / support@eazyBI.com
Hi @zane.baranovska
Thanks for your answer. I will try what you recommend.
Hi,
I am trying to reproduce something similar using a select list field named ‘Assigned Queue’. As I’ve tried to set advanced settings to create the change log, I receive an error.
[jira.customfield_13222]
data_type = “string”
dimension = true
separate_table = true
changes = true
Error:
Execution of custom JavaScript code raised the following error:
ReferenceError: “jira” is not defined.
Can you please help?
Hi @sgoljer,
Those advanced settings for custom fields are supported only when using integration with Jira and selecting Jira in the data source screen.
Could this be that you have another system as a data source or have imported data from Jira using REST API or SQL SELECT?
Also, double-check that custom field settings are pasted in the right place: eazyBI >> System administration >> Settings >> tab “Advanced Settings”.
More details on advanced settings for Jira custom fields are here: Advanced settings for custom fields
Best,
Zane / spport@eazyBI.com
@zane.baranovska, thanks for tip! I was able to get it working. However, when checking the results, the calculation doesn’t account for the days in the last transition prior to the ticket closure. Is it possible to modify it?
Days in transition queue:
Sum(
–filter a set of issues that changed assignee in a selected period
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
–originally it was ‘Transitions from queue’
[Measures].[Transitions from queue] > 0
),
–for each issue calculate days between queue changes
DateDiffDays(
–date when for the first time assigned to queue
TimestampToDate(
([Measures].[Transition to first timestamp],
[Transition field].[Assigned Queue],
[Time].CurrentHierarchy.DefaultMember) ),
–date when for the last time assigned from queue in selected period
TimestampToDate(
([Measures].[Transition from last timestamp],
[Transition field].[Assigned Queue]) )
)
)
Thank you,
Silvia
Hi @sgoljer,
The calculation counts duration only in completed queues (cycles) - assigned queues issue already has left - because duration is calculated till the timestamp issue left that queue. If you would like to add up also duration in the current queue, you might want to slightly adjust the calculation and add time from the last transition to assignee queue and today.
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assigned Queue]) ),
"today"
)
The updated expression might look like this:
Sum(
--filter a set of issues that changed assignee in a selected period
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
--originally it was ‘Transitions to queue’
[Measures].[Transitions to queue] > 0
),
--for each issue calculate days between queue changes
DateDiffDays(
--date when for the first time assigned to queue
TimestampToDate(
([Measures].[Transition to first timestamp],
[Transition field].[Assigned Queue],
[Time].CurrentHierarchy.DefaultMember) ),
--date when for the last time assigned from queue in selected period
TimestampToDate(
([Measures].[Transition from last timestamp],
[Transition field].[Assigned Queue]) )
) +
--add time in the lates Assigned Queue
CASE WHEN --All Assigned Queues or latest Assigned Queue on report rows
[Assigned Queue].CurrentHierarchyMember IS [Assigned Queue].CurrentHierarchy.DefaultMember OR
[Measures].[Issue assigned queue] MATCHES [Assigned Queue].CurrentHierarchyMember.GetCaption
THEN --calcaulte duration since the last custom field change date
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assigned Queue]) ),
"today")
END
)
Note I also updated the filter criteria to [Measures].[Transitions to queue] > 0 (code line 6) line so it would include also issues that have never changed the custom field value and calculate duration in their current and only queue.
1 Like
thank you so much! I’ve altered it to use the resolution time, instead of ‘today’. It seems to work
–add time in the latest Assigned Queue in which the ticket was resolved
CASE WHEN --All Assigned Queues or latest Assigned Queue on report rows
[Assigned Queue].CurrentHierarchyMember IS [Assigned Queue].CurrentHierarchy.DefaultMember OR
[Measures].[Issue assigned queue] MATCHES [Assigned Queue].CurrentHierarchyMember.GetCaption
THEN --calculate duration since the last custom field change date
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assigned Queue]) ),
[Measures].[Issue resolution date])
END
In this report, I will be using the measure only for resolved issues so it’s not an issue, but if someone else wants to use is for issues in progress, it would be incorrect. Is there a way to add a condition to use the ‘issue resolution date’ for resolved issues, but use ‘today’ for others?
Thank you!
Silvia
@sgoljer, you might want to use the function CoalesceEmpty() to show resolution date by default and “today” if the resolution date is missing.
...
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assigned Queue])
),
--till today or resolution day if resolved
CoalesceEmpty(
[Measures].[Issue resolution date],
'today')
)
...
An alternative is to use the function iif() or condition CASE WHEN to write one expression with resolution date for resolved issues another expression with “today” for issues without resolution date.
1 Like
Thank you for your help! This worked perfectly. Really appreciate your time.
Continuing on this adventure …
We recently found an issue that I need help with. The Average days in queue per ticket should be calculated as a SUM of the time in the same transition. Instead, the calculation divides it by 2 considering the 2 transitions. For example, when a ticket is transitioned twice into the PWE Compute queue, it should not be divided by the number of transitions. So instead of cc. 31 days in PWE Compute queue, the Average days in queue shows 14.57 days. Please see detail below:
Ticket created and assigned to ‘PWE Compute’ 09/May/22 8:34 AM (duration is cc 8 hrs)
Reassigned to ‘eWAN Cyber’ 09/May/22 4:00 PM
Reassigned to ‘NOC’ 10/May/22 10:08 AM
Reassigned to ‘PWE Compute’ 10/May/22 10:17 AM - resolved on 08/Jun/22 6:08 AM (duration is cc 31 days)
And here are the formulas:
Average days in queue:
CASE
WHEN [Measures].[Transitions to queue] > 0 THEN
[Measures].[Days in queue] / [Measures].[Transitions to queue]
END
Transitions to queue
( [Measures].[Transitions to], [Transition Field].[Assigned Queue] )
Days in queue
Sum(
–filter a set of issues that changed queue in a selected period
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
[Measures].[Transitions to queue] > 0
),
–for each issue calculate days between queue changes
DateDiffDays(
–date when for the first time assigned to queue
TimestampToDate(
([Measures].[Transition to first timestamp],
[Transition field].[Assigned Queue],
[Time].CurrentHierarchy.DefaultMember) ),
–date when for the last time assigned from queue in selected period
TimestampToDate(
([Measures].[Transition from last timestamp],
[Transition field].[Assigned Queue]) )
) +
–add time in the latest Assigned Queue
CASE WHEN --All Assigned Queues or latest Assigned Queue on report rows
[Assigned Queue].CurrentHierarchyMember IS [Assigned Queue].CurrentHierarchy.DefaultMember OR
[Measures].[Issue assigned queue] MATCHES [Assigned Queue].CurrentHierarchyMember.GetCaption
THEN --calculate duration since the last custom field change date
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[Assigned Queue]))
,
–till today or resolution day if resolved
CoalesceEmpty(
[Measures].[Issue resolution date],
‘today’)
)
END
)
)
Hello there! I’ve been using these measures and so far, it’s been great. However, I need to add another restriction and I’m having trouble figuring it out. Specifically, I need to track the amount of time a ticket spent in the queue and in the escalated status. Would you be able to assist me with this?
The goal is to know how long each group has the ticket Escalated status before setting it to in progress.
By the way, I’m new to Eazy Bi reporting.
Thanks
