Need a way to restrict hours in status Escalated to clock hours 7 to 7

Questions & Answers
1

Need a way to restrict hours in status Escalated to clock hours 7 to 7
I’m using transition and transition authors to get their time from the ticket escalated to in progress. But it works 24 hours clock and I need to restrict it to 7 to 7.
Any way a can get that?

2

Hi @mjunyent

Welcome to eazyBI Community!
​
​It seems that you are looking for DateDiffWorkHours function (learn more about the function here DateDiffWorkhours). You will require two dates for this function (when the issue transitioned to “Escalated” and when it transition form “Escalated” to "In progress.
​
​Here is an example I have written for the function. “67” in the formula stands for Saturday and Sunday, but feel free to adjust this according to your requirements. If Time dimension options are specified then you can use the argument ‘default’ to take into account these Time dimension non-working days.

“7 - 19” indicates the workhours.
​

​DateDiffWorkHours(
  ([Measures].[Transition to status first date],
[Transition Status].[Escalated]),
  ([Measures].[Transition to status last date],
[Transition].[Escalated => In Progress]),
  "67",
  "7-19"
)

Best wishes,

Elita from support@eazybi.com

3

Thanks for your reply. Can I add to that function to restrict the transition with a specific field value (single list It group) at the moment of the transition?
Regards, Marcela

4

Hi @mjunyent

Thanks for following up with me!
Is your custom field imported as a dimension? If so, you should be able to add another requirement in the second Tuple function for the date. In my example, I have added a requirement that the issue dimension Fruits must be with the value “Apple”. You can replace “Fruits” with the name of your custom field, and “Apple” should be replaced with the value of that custom feld.

DateDiffWorkHours(
  ([Measures].[Transition to status first date],
[Transition Status].[Escalated]),
  ([Measures].[Transition to status last date],
[Transition].[Escalated => In Progress]
[Fruits].[Apple]),
  "67",
  "7-19"
)

Best wishes,

Elita from support@eazybi.com

5

Look good,
Can I add other question. I wanted to do the same with the measure to check the days the ticket was on that it group , but it is not filtering correctly.
This is what I did:
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].[It Group],[IT Group].[Tier 2 ES],
[Time].CurrentHierarchy.DefaultMember)),
–date when for the last time assigned from queue in selected period
TimestampToDate(
([Measures].[Transition from last timestamp],
[Transition field].[IT Group ],[IT Group].[Tier 2 ES]) )
) +
–add time in the latest Assigned Queue
CASE WHEN --All Assigned Queues or latest Assigned Queue on report rows
[IT Group].CurrentHierarchyMember IS [IT Group].CurrentHierarchy.DefaultMember OR
[Measures].[Issue IT Group] MATCHES [IT Group].CurrentHierarchyMember.GetCaption
THEN --calculate duration since the last custom field change date
DateDiffDays(
TimestampToDate(
([Measures].[Transition to last timestamp],
[Transition Field].[It group],[IT Group].[Tier 2 ES]))
,
–till today or resolution day if resolved
CoalesceEmpty(
[Measures].[Issue resolution date],
‘today’)
)

END
)
)

Can you tell me what is the problem with my formula?
Regards, Marcela