topic Re: Date/time Difference in QlikView

Date/time Difference

Thu, 06 Jun 2013 09:42:47 GMT

Hi,

I need to find the difference between 2 date/time fields (e.g 01/02/2012 00:00:00) to show the difference in minutes.

So for example: schedule start time: 01/01/2012 11:00:00

Actual start time: 01/01/2012 11:15:00

I want it to show 15mins. I need it to work for negative numbers too, so:

schedule start time: 01/01/2012 11:00:00

Actual start time: 01/01/2012 10:45:00

so it needs to show -15.

Please help.

Thanks in advance

Re: Date/time Difference

vishnus85 — Thu, 06 Jun 2013 09:52:49 GMT

Actually I too was about to post the same question.

QlikView exprets out there, please help.

How to find the diiference (in minutes) between two timestamp/date fields ??

Re: Date/time Difference

swuehl — Thu, 06 Jun 2013 10:08:26 GMT

try

=interval( TimeStamp2 - TimeStamp1, 'mm')

Re: Date/time Difference

Thu, 06 Jun 2013 10:34:55 GMT

Thanks, can I use this in an IF statement now? Like IF (interval( TimeStamp2 - TimeStamp1, 'mm') < -10, 'Early Finish', IF (interval( TimeStamp2 - TimeStamp1, 'mm') >10, 'Late Finish' ... etc?

Or is there another way to do it? I want to find out the time differewnce thenhave another box saying how many minutes the session started late/finished early by.

Thanks,

Re: Date/time Difference

Joaquin_Lazaro — Thu, 06 Jun 2013 10:55:46 GMT

Try this:

a) = 'Diference: ' & time(Now() - ReloadTime(), 'hh:mm:ss')

b) = 'Diference: ' & time(ReloadTime() - Now(), '-hh:mm:ss')

You could improve this with an IF Now > Reload use a) else use b)

Re: Date/time Difference

vishnus85 — Thu, 06 Jun 2013 10:56:50 GMT

hi mohit and swuehl,

I still face the problem with formating the days.

Say for example, one of the dates, CASE_START_DATE

This comes to qlikview as a number

But further formating using the date functions as u mentioned is not helping either.

please see evolution of one of the date field and let me know what is it that I am missing.

Re: Date/time Difference

kumarnatarajan — Thu, 06 Jun 2013 11:06:15 GMT

Hi,

I think your time is string format. So you have problam. Try this one it is very helpful for you

Ex:

=Interval(Timestamp(Timestamp#('01/01/2012 11:15:00','DD/MM/YYYY hh:mm:ss'))-Timestamp(Timestamp#('01/01/2012 11:00:00','DD/MM/YYYY hh:mm:ss')),'mm')&' mins'

Re: Date/time Difference

swuehl — Thu, 06 Jun 2013 12:43:03 GMT

It seems to me that you already have a QV timestamp in field CASE_STOP_TIME.

Don't use Date#() interpretation function then.

Use something like

interval(CASE_STOP_TIME - CASE_START_TIME,'mm')

if you want to compare against a certain time in minutes, use something like

if (CASE_STOP_TIME - CASE_START_TIME < maketime(0,15), 'smaller 15 minutes','>= 15 min')

Re: Date/time Difference

vishnus85 — Thu, 06 Jun 2013 13:23:53 GMT

The expression

Interval(Date(Date#(StopDateTime,'DD-MM-YYYY hh:mm'),'mm') - Date(Date#(StartDateTime,'DD-MM-YYYY hh:mm'),'mm') ,'mm') worked for me.

I was not getting it correct when the (seconds) 'DD-MM-YYYY hh:mm:ss' portion was included.

removing the :ss part, giving only 'DD-MM-YYYY hh:mm' resulted in correct answer for me.

Re: Date/time Difference

felixobes — Tue, 25 Feb 2014 15:38:12 GMT

The expressions work well, thanks for the info, but what about date and time from 2 fields? I am having issues getting the time differences not computing as negatives when they are past midnight, so I would need to use the date field to add 24 hours to the minutes past midnight, not sure how to pull it off.

I am using the interval function on the minutes cause I got time data for instance that says, Scheduled time, 2200 actual time, 2205 so what I did was, =sum(interval(Scheduled - Actual, 'hhmm') and it works, but when the clock goes over midnight the result is a big negative number. How do you suggest I solve this. Example that does not work, Scheduled time 2350, Actual time 0050. This gives me -23hours instead.

Re: Date/time Difference

Thu, 31 Jul 2014 20:43:30 GMT

Facing Same problem. How to get rid of it?

Re: Date/time Difference

vishnus85 — Thu, 31 Jul 2014 22:55:51 GMT

Interval(Date(Date#(StopDateTime,'DD-MM-YYYY hh:mm'),'mm') - Date(Date#(StartDateTime,'DD-MM-YYYY hh:mm'),'mm') ,'mm') worked for me.

I was not getting it correct when the (seconds) 'DD-MM-YYYY hh:mm:ss' portion was included.

removing the :ss part, giving only 'DD-MM-YYYY hh:mm' resulted in correct answer for me.