Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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')) |
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
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.
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')) |
Thanks Ajay, How can I merge date and time into timestamp please
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
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'))
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'))
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
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.