Duplicate Dates for Incident

[hitha1512]

Hi Team,

Considering all the tickets are opened and closed between 7AM to 7PM which is 12 hours a day excluding weekends. I would like to get the below output using open date and closed date for every ticket. 

I would like to generate the dates in between the open date and closed date for each ticket.

Raw data :

Incident	Open date	Closed date
INC002	12/8/2022 12:45:00 PM	12/13/2022 9:45:00 AM

Required output: Column "New Dates" & "Total hours" 

INC	New Dates	Total Hours	Reason
INC001	12/8/2022 12:45:00 PM	6:15 hours	( 12:45PM to 7:00 PM (Business hour))
INC002	12/9/2022	12 hours	(Fullday- 7AM to 7PM business hours)
INC003	12/10/2022	0	Since weekends are not considered
INC004	12/11/2022	0	Since weekends are not considered
INC005	12/12/2022	12 hours	(Fullday- 7AM to 7PM business hours)
INC006	12/13/2022 9:45:00 AM	2:45 hours	( 7AM(Business hour) to 9:45AM)

Any Help on this will be appreciated.

Thank you,

Hitha Dhani

[edwin]

without giving the business rules how to calculate the total hours, supplying sample data and the sample output would help others understand your problem. 

i would be interested to see a sample incident that closes before the the close date of the requested range:  
incidentx: opened 12/1/2022 closes 12/30/2022 but the user wants to see all incidents between 11/1/2022 and 12/15/2022.  is this incident included?  what if the incident opened before the requested date range but closed inside the date range or after the date range is it included?

[edwin]

also it is curious why your topic is "duplicate date" when there is no mention of duplicate dates in your question.  appears to me theres a lot of info missing here

[MarcoWedel]

maybe helpful

https://community.qlik.com/t5/Design/Creating-Reference-Dates-for-Intervals/ba-p/1463944

https://community.qlik.com/cyjdu72974/attachments/cyjdu72974/qlikview-documents/8117/1/Generating%20...

[brunobertels]

hi 

see attached sample app 

hope it helps