topic Re: Calculate Time Interval Between Two Dates On Business Days For Business Hours Only in QlikView

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

Wed, 13 Feb 2013 21:33:44 GMT

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.

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

vivientexier — Wed, 13 Feb 2013 22:03:26 GMT

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

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.

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

Thu, 14 Feb 2013 15:56:42 GMT

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.

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

pennetzdorfer — Sun, 08 Sep 2013 16:59:30 GMT

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

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

swuehl — Sun, 08 Sep 2013 21:53:42 GMT

You can look into one of the suggested approaches here.

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

pennetzdorfer — Mon, 09 Sep 2013 08:52:15 GMT

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