Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date/time Difference

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

11 Replies
vishnus85
Partner
Partner

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 ??

swuehl
MVP
MVP

try

=interval( TimeStamp2 - TimeStamp1, 'mm')

Not applicable
Author

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,

joaquinlr
Specialist II
Specialist II

Try this:

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

or

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

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

vishnus85
Partner
Partner

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.

issue.bmp

kumarnatarajan
Partner
Partner

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'

swuehl
MVP
MVP

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

vishnus85
Partner
Partner

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.

felixobes
Contributor III
Contributor III

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.