Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date and Time difference in Hours only workingdays

I have the following data:

File name is Data Warehouse Sep 2013
APPL_IDevent_typeEVT_DTEEVT_TIME
19681349entered02/09/201315:49:56
File name is September data
APPL_IDInput_Date_Time
1968134904/09/2013 14:24
File name is Warehouse Data Sep, 2103
APPL_IDDECISION_DTEDECISION_TIME
1968134904/09/201314: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?

1 Solution

Accepted Solutions
Not applicable
Author

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'))

View solution in original post

9 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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'))

View solution in original post

Not applicable
Author

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

Not applicable
Author

 

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

Not applicable
Author

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'))


Not applicable
Author

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'))

Not applicable
Author

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

Not applicable
Author

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.

TrayActivity_CodeApplication Status
NAPS Contact CustomerNCOOutbound
NAPS Print DocumentsNDOOutbound
NAPS Follow Up DocsNFUCustomer
NAPS Returned DocumentsNRDCustomer
NAPS Check DocumentsNCHInbound
NAPS Security PerfectionNSPSecurity
NAPS Sec Special ConditionsNSCBooked
NAPS BookingNBOBooked
NAPS Call OffNCABooked

Each tray has different State_Code a below.

State_CodeCode description
RDYReady to work
RUNIn process
TRMTerminated
CMPCompleted

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.