Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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

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.

Tags (3)
5 Replies
vivientexier
Contributor II

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

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.

Not applicable

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

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
Contributor III

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

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

MVP
MVP

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

You can look into one of the suggested approaches here.

pennetzdorfer
Contributor III

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

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

Community Browser