Calculate Time Interval Between Two Dates On Business Days For Business Hours Only

[#1]

Dear Community,

I have two dates (close date and open date). I need to calculate the interval between these two and visualize it on a bar chart or maybe, even a table chart.

REQUIREMENT: Calculate Time Interval Between Close Date and Open Date

VISUALIZATION:

Bar Chart showing average time taken to resolve a ticket (i.e., close_date - open_date) in %  against a cyclic dimension.

CONSIDERATIONS:

1. Calculated time interval should exclude weekends (Thu, Fri) in this case

2. Calculated time interval should exclude non-business hours

Valid business hours are 7 AM to 5 PM.

Additionally, it would be great if I could also exclude the tea/lunch break duration from the business hours of a working day.

I have found some posts on the community that almost solve this, but I could use some assistance to get this right, esp the valid business hours and weekends (Thu, Fri).

Please find the Sample document attached.

Thank you for your time.

Regards,

-Khaled.

[vivientexier]

1)

If you only need data from saturday to wednesday and from 7am to 5pm... just exclude them.

2)

If you still need this data for other chart, add a flag for tickets that occurs during the good period of time. Then you will do your calculation using set analysis like {< flag_valide_ticket={1} >} That's it.

[#3]

Vivien,

Thank you for your suggestion.

Yes, I will need the data for weekends to use in other charts.

I have figured out most of the part (many thanks to the Community).

I am still hoping experts could help me with the sample I attached.

Regards,

-Khaled.

[pennetzdorfer]

Hey Khaled,

I'm trying to do something similar ... have you been successful on this topic?

I would really appreciate, if you could share your approach!

Regards,

Florian

[swuehl]

You can look into one of the suggested approaches here.

[pennetzdorfer]

Thank you, Stefan - that really helps a lot!

At the moment I'm trying to add an additional challenge: there should be different working hours on Fridays (8 am to 1 pm). Have you ever done anything like that?

Regards,

Florian