topic Re: Timestamp difference in hours in QlikView

Timestamp difference in hours

Thu, 13 Feb 2014 12:31:57 GMT

Input_Date_Time = 06/09/2013 16:49:01, Decisiondatetimefinal = 11/09/2013 14:20:46 . I used the following in script:

Let vStartTime = '08:00:00';

Let vQuitTime = '18:00:00';

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

I used the following in expression and gives me result as 1.14. This result is wrong becuase it should give me more hours.

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

Re: Timestamp difference in hours

hic — Thu, 13 Feb 2014 13:18:19 GMT

If I run the following lines in my script

Let vStartTime = '08:00:00';

Let vQuitTime = '18:00:00';

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

I get "0,4166666" stored into vWorkDay - which is correct. It is the number or days between the two times.

If you want the number of hours, you need to multiply by 24. Further, when you use this variable it may expand with a decimal comma (depends on your settings). But you want a decimal point. To always get decimal point, you should expand it with a #-sign: 24*$(#vWorkDay).

HIC

Re: Timestamp difference in hours

Thu, 13 Feb 2014 14:26:46 GMT

Thanks . I multiplied the following expression with 24 as below and it is worked:

Am I right to say that with the above expression I will get only difference (decisionfinaldatetime - Input_date_time) in hour of workingdays if time start and end is 9:00:00 to 17:00:00