Hi everyone,
I’m new to Eazybi.
I’m trying to build a report that shows the working time of each issue.
In our organization the working hours are from 09 to 13 and from 14:30 to 18:30 (Mon-Fri)
I tried to use DateDiffWorkhours, but I don’t understand how to manage the lunch break.
DateDiffWorkhours(
[Issue].CurrentHierarchyMember.get(‘Created at’),
[Issue].CurrentHierarchyMember.get(‘Closed at’),
‘default’,
‘9-13+14:30-18:30’
)
Is there any way to get it?
I add that the working time should be calculated from the opening date to the closing date (if any) or to the current date (if still open)
Thanks in advance
Hi @DamianoDB,
Welcome to the eazyBI community!
The DateDiffWorkHours function presumes an uninterrupted business day and allows only one time interval to be provided as a function argument.
If you have two split time intervals, there are two options.
1) calculate morning and afternoon shift times separately and then add them together
2) calculate business hours during the day and then subtract business hours during the lunch break
Both options return the same result.
If you want to calculate the time until today for the still open issues, you might replace the empty “Closed at” with the Now() function.
That will require some additional processing of results to handle potential data type mismatch.
Please see below the expression for the option of morning shift + afternoon shift with included handling of empty Closed date.
DateDiffWorkhours(
[Issue].CurrentHierarchyMember.get('Created at'),
TimestampToDate(
CoalesceEmpty(
DateToTimestamp([Issue].CurrentHierarchyMember.get('Closed at')),
DateToTimestamp(Now()))),
'default',
'9-13')
+
DateDiffWorkhours(
[Issue].CurrentHierarchyMember.get('Created at'),
TimestampToDate(
CoalesceEmpty(
DateToTimestamp([Issue].CurrentHierarchyMember.get('Closed at')),
DateToTimestamp(Now()))),
'default',
'14:30-18:30')
Regards,
Oskars / support@eazyBI.com
Hi Oskars,
I tested your solution and it’s perfect. Exceed my expectations.
A thousand thanks
sorry, one last thing.
Is it possible to exclude from the calculation the time in which the ticket was in the “waiting for the customer” status?
I tried to calculate the “waiting for the customer” time separately, and then subtract it from the total working time.
I used the following formula
DateDiffWorkHours(
([Measures].[Transition to status first date],
[Transition Status].[Waiting for Customer],
[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition from status last date],
[Transition Status].[Waiting for Customer],
[Time].CurrentHierarchy.DefaultMember),
'67',
'9-13'
)+
DateDiffWorkHours(
([Measures].[Transition to status first date],
[Transition Status].[Waiting for Customer],
[Time].CurrentHierarchy.DefaultMember),
([Measures].[Transition from status last date],
[Transition Status].[Waiting for Customer],
[Time].CurrentHierarchy.DefaultMember),
'67',
'14:30-18:30'
)
This seems to work, but if the ticket is still in the “waiting for customer” status the time returned is null.
Is there any way to modify the formula considering the exit time from the status of “waiting for customer” or now() if the current status is “waiting for customer”?
In your opinion, does the formula work even if the ticket enters and exits the waiting for customer status several times?
Thanks in advance
Hi @DamianoDB,
The option of finding the time still in status is somewhat described in a colleagues community post here - How to calculate Days in transition status including the time in current status.
The approach of subtracting the work hours spent in a specific status might work if the issue was in that specific status once or twice. You might then separately calculate the time difference for to/from FIRST timestamp and, if required, to/from LAST timestamp.
However, if the issue is going into the undesired status more than twice, the rest of the cycles cannot be addressed clearly. Generally, that time might be found via the measure “Workdays in transition status”. Still, that measure would lack the precision of lunch breaks.
The next level of complexity would be to create a JavaScript that would scroll through the issue changelog and calculate the business hours spent in desired statuses, considering the lunch hours. That script would need the public holidays fed as an additional argument, though.
You might see a simpler option here - Sum DateDiffWorkhours on a specific status - #2 by oskars.laganovskis
You might then try to adapt the code for the workhours to also handle the lunch break. This might not be an easy task, yet not impossible.
Regards,
Oskars / support@eazyBI.com
2 Likes
Hi @oskars.laganovskis
What happens if I have these schedules?
Monday to Thursday: 8:30aM - 5:30PM
Friday: 8AM - 2:00 PM
How can I manage the difference between Monday to Thursday with Friday?
Hi @Marcelo_Ignacio_Cid1,
If you have a different schedule on Friday, you might split the calculation into two parts - one for Mon-Thu and another for Fri.
--mon-thu part - exclude friday
DateDiffWorkHours(
<starttime expression>,
<endtime expression>,
'567',
'8:30-17:30'
)
+
--fri part - exclude all other days
DateDiffWorkHours(
<starttime expression>,
<endtime expression>,
'123467',
'8-14'
)
Regards,
Oskars / support@eazyBI.com
1 Like
@oskars.laganovskis
Is it possible to obtain the specific time of a ticket in a state but given a working schedule?
For example:
I have the following states:
Backlog - In Progress - Waiting for customer - Done
I would like to know how long the ticket was in the in progress state given a “work time” parameter. I don’t know if a work time slot measure can be added to the days in transition status or work transition status measure.
@Marcelo_Ignacio_Cid1 pre-calculated measures would only allow one common work schedule for the whole eazyBI account.
There might still be a conceptually possible crazy option of calculating actual hours per volatile schedule on condition of issues only passing through In Progress once.
The approach might be to import individual user work schedule using additional data import, defining starttime and endtime as decimal measures and mapping to the user and Time dimensions.
The expression then might iterate through days from the first transition to until the last transition from and then calculate the hours based on the custom schedule applicable to the user on the specific date.
The expression might be capable of delivering results on a reasonably low number of issues but is doomed to face calculation timeout for larger issue categories. Therefore, it would not be of practical use.
Regards,
Oskars / support@eazyBI.com
