Date and Time difference in Hours only workingdays

[#1]

I have the following data:

File name is Data Warehouse Sep 2013
APPL_ID	event_type	EVT_DTE	EVT_TIME
19681349	entered	02/09/2013	15:49:56
File name is September data
APPL_ID	Input_Date_Time
19681349	04/09/2013 14:24
File name is Warehouse Data Sep, 2103
APPL_ID	DECISION_DTE	DECISION_TIME
19681349	04/09/2013	14:32:54

I want to calculate the following difference of Networking days in Hours : (7.5 working hours in a day Monday to Friday)

1).  (Input_Date_TIME) - (Evt_DTE & EVT_TIME)

2).  (Decision_date & Decision_Time)  -  (Input_Date_Time)

Any help please?

[#2]

Hey Shoaib,

If you search for the topic 'Help with SLA calculation' i believe you will find what you are looking for. Someone posted a similar requirement and it was solved in that discussion. I am not posting the link as it will take time for the link to get approved.

There are some cache issues going on so instead of a google search do a search in Qlikcommunity and you will find it.

Thanks

AJ

[#3]

Hi Ajay

Thanks but I can't open qlikview because i don't have licence.

Could you please paste code here based on my data.

[#4 · Accepted solution]

First you define your Day start and end time

Let vStartTime = '08:00:00';

Let vQuitTime = '18:00:00';

Let vWorkDay = '$(vQuitTime)'-'$(vStartTime)';

Then you can use the below expression to calculate your hours. In your script you have to join those three tables and then use Input_Date_Time instead of DateInitial and Decision_Date_Time as DateEnd. Before that concateante Decision_Date and Decision_Time into the right Timestamp format like DD/MM/YYYY hh:mm:ss.

Similarly for the other one. Give it a whirl.

	fabs(interval(((Networkdays(Date(DateInitial,'DD/MM/YYYY hh:mm:ss'),Date(DateEnd,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')
	+
	if(frac(date(DateInitial))<num('$(vQuitTime)'),if(frac(date(DateInitial))>num('$(vStartTime)'),Date#(date(floor(DateInitial),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(DateInitial,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(DateInitial),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(DateInitial),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)
	+
	if(frac(date(DateEnd))>num('$(vStartTime)'),if(frac(date(DateEnd))<num('$(vQuitTime)'),(Date(DateEnd,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(DateEnd),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(DateEnd),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(DateEnd),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))

[#5]

Thanks Ajay, How can I merge date and time into timestamp please

[#6]

I used the following but it gives me e.g. 30-09-3036 10:21:15. Year 3036 is wrong it should be 2013

Timestamp(DECISION_DTE & ' ' & DECISION_TIME , 'DD-MM-YYYY hh:mm:ss') as DecisionDateTime

[#7]

I pasted the follwoing in script

Let vStartTime = '08:00:00';

Let vQuitTime = '18:00:00';

Let vWorkDay = '$(vQuitTime)'-'$(vStartTime)';

. I got decisiondatetime. After that pasted the followin in expression but does not give me results. Could you please have a look on it:

fabs(interval(((Networkdays(Date(Input_Date_Time,'DD/MM/YYYY hh:mm:ss'),Date(Decisiondatetimefinal,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(Input_Date_Time))<num('$(vQuitTime)'),if(frac(date(Input_Date_Time))>num('$(vStartTime)'),Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(Input_Date_Time,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(Decisiondatetimefinal))>num('$(vStartTime)'),if(frac(date(Decisiondatetimefinal))<num('$(vQuitTime)'),(Date(Decisiondatetimefinal,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))

[#8]

I have Input_Date_Time = 15/09/2013 09:17:48 (This is the timestamp when other system sends Application in this system as it operates 24 Hours but I just want to calculate hours 9am to 5pm) and Decisiondatetimefinal = 19/09/2013 13:56:00. These two fiels are on two different files. I want to calculate (Decisiondatetimefinal - Input_Date_Time) in hours only working days.

I pasted the follwoing start and finishng time in script

Let vStartTime = '09:00:00';

Let vQuitTime = '17:00:00';

Let vWorkDay = '$(vQuitTime)'-'$(vStartTime)';

After that pasted the following in expression but does not give me results. Any idea????:

fabs(interval(((Networkdays(Date(Input_Date_Time,'DD/MM/YYYY hh:mm:ss'),Date(Decisiondatetimefinal,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')

+

if(frac(date(Input_Date_Time))<num('$(vQuitTime)'),if(frac(date(Input_Date_Time))>num('$(vStartTime)'),Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(Input_Date_Time,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(Input_Date_Time),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)

+

if(frac(date(Decisiondatetimefinal))>num('$(vStartTime)'),if(frac(date(Decisiondatetimefinal))<num('$(vQuitTime)'),(Date(Decisiondatetimefinal,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(Decisiondatetimefinal),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))

[#9]

Hey Shoaib,

A few things i can think of.

The two date fields should be in the same table. Like

Load

InputDateTime,

DecisionDateTime,

Expression

From Table;

Can you post your sample data. I can give it a whirl.

Thanks

[#10]

Hi Ajay, I multiplied the expression with 24 and it's workig now. is this correct?

Second thing is I have the following data in notepad file.

Process_Code Application_id Workitem_Id Activity_Code Txn_Date State_Code Assigned_User_Id Assigned_Dept_Id Actioned_User_Id
NAP 10003507 79909714 NBG 2013-09-06 10:32:39.900 CMP NULL NULL 99999
NAP 10003507 79909714 NBG 2013-09-06 10:32:39.900 RDY NULL NULL 99999
NAP 10003507 79909715 NSA 2013-09-06 10:32:39.900 RDY NULL NULL 99999
NAP 10003507 79909715 NSA 2013-09-11 09:00:28.380 RUN NULL NULL 12781
NAP 10003507 79909715 NSA 2013-09-11 09:21:09.713 CMP NULL NULL 12781
NAP 10003507 80307667 NSX 2013-09-11 09:21:09.713 RDY NULL NULL 12781
NAP 10003507 80307667 NSX 2013-09-11 09:44:07.147 RUN NULL NULL 29037
NAP 10003507 80307667 NSX 2013-09-11 09:44:10.617 CMP NULL NULL 29037
NAP 10003507 80308416 NSA 2013-09-11 09:44:10.617 RDY NULL NULL 29037
NAP 10003507 80308416 NSA 2013-09-11 09:44:11.787 RUN NULL NULL 29037
NAP 10003507 80308416 NSA 2013-09-11 09:44:29.117 CMP NULL NULL 29037
NAP 10003507 80308424 NSX 2013-09-11 09:44:29.117 RDY NULL NULL 29037
NAP 10003507 80308424 NSX 2013-09-11 09:44:34.600 TRM NULL NULL 29037
NAP 10003507 80403761 NBG 2013-09-17 17:01:54.310 RDY NULL NULL 99999
NAP 10003507 80403761 NBG 2013-09-17 17:01:54.327 CMP NULL NULL 99999
NAP 10003507 80403762 NSA 2013-09-17 17:01:54.327 RDY NULL NULL 99999
NAP 10003507 80403762 NSA 2013-09-19 15:48:37.117 RUN NULL NULL 29037
NAP 10003507 80403762 NSA 2013-09-19 15:57:16.813 CMP NULL NULL 29037
NAP 10003507 80438748 CJA 2013-09-19 15:57:16.813 RDY NULL NULL 29037
NAP 10003507 80438748 CJA 2013-09-19 16:02:13.400 CMP NULL NULL 99999
NAP 10003507 80438829 NCO 2013-09-19 16:02:13.413 RDY NULL NULL 99999
NAP 10003507 80438829 NCO 2013-09-19 16:20:41.390 RUN NULL NULL 29396
NAP 10003507 80438829 NCO 2013-09-19 16:21:07.717 CMP NULL NULL 29396
NAP 10003507 80439054 NFU 2013-09-19 16:21:07.717 SUS NULL NULL 29396
NAP 10003507 80439054 NFU 2013-09-25 08:21:40.923 RDY NULL NULL 88603
NAP 10003507 80439054 NFU 2013-09-25 08:21:41.033 RUN NULL NULL 88603
NAP 10003507 80439054 NFU 2013-09-25 08:21:50.877 CMP NULL NULL 88603
NAP 10003507 80507822 NCH 2013-09-25 08:21:50.893 RDY NULL NULL 88603
NAP 10003507 80507822 NCH 2013-09-25 15:24:55.157 RUN NULL NULL 15095
NAP 10003507 80507822 NCH 2013-09-25 15:24:58.657 RDY NULL NULL 15095
NAP 10003507 80507822 NCH 2013-09-25 15:25:05.563 RUN NULL NULL 15095
NAP 10003507 80507822 NCH 2013-09-25 15:28:54.660 CMP NULL NULL 15095
NAP 10003507 80515611 NBO 2013-09-25 15:28:54.660 RDY NULL NULL 15095
NAP 10003507 80515611 NBO 2013-09-25 15:28:58.067 RUN NULL NULL 15095
NAP 10003507 80515611 NBO 2013-09-25 15:29:02.910 CMP NULL NULL 15095
NAP 10003507 80515614 NCA 2013-09-25 15:29:02.910 CMP NULL NULL 99999
NAP 10003507 80515614 NCA 2013-09-25 15:29:02.910 RDY NULL NULL

Each application passes through different trays but I am only intersted in the following Activity_Code.

Tray	Activity_Code	Application Status
NAPS Contact Customer	NCO	Outbound
NAPS Print Documents	NDO	Outbound
NAPS Follow Up Docs	NFU	Customer
NAPS Returned Documents	NRD	Customer
NAPS Check Documents	NCH	Inbound
NAPS Security Perfection	NSP	Security
NAPS Sec Special Conditions	NSC	Booked
NAPS Booking	NBO	Booked
NAPS Call Off	NCA	Booked

Each tray has different State_Code a below.

State_Code	Code description
RDY	Ready to work
RUN	In process
TRM	Terminated
CMP	Completed

I want to calculate time in hours only workingdays in each application_status when State_code is 'CMP' when application changes its application_status from one to other e.g. from outbound to customer. start time = 9:00:00 and quit time = 17:00:00.