Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

subtracting two dates and times

l would like to subtract 2 dates and times. What formula do l use in qlikview? Last drop departure date minus Last drop arrival date

  

Last Drop Arrival DateLast Drop Departure Date
28/10/2015 06:1528/10/2015 7:45
28/10/2015 07:3028/10/2015 09:30
29/10/2015 09:5029/10/2015 11:30
28/10/2015 21:0029/10/2015 08:00
13 Replies
Highlighted
Master III
Master III

Re: subtracting two dates and times

Assuming those fields are not text fields, you can just do one minus the other:

[Last Drop Arrival Date] - [Last Drop Departure Date]

The end result will be the number of days it took between those dates.

Since you result seems to be mainly a difference in hours, you could multiply that result by 24 to get the number of hours:

([Last Drop Arrival Date] - [Last Drop Departure Date]) * 24

... and round that to the number of decimals you like...:

round(([Last Drop Arrival Date] - [Last Drop Departure Date]) * 24, 0.01)

May you live in interesting times!
Highlighted
Not applicable

Re: subtracting two dates and times

Hi;

Thanks

My format is a general format. What formula do l use to incorporate date format?

Highlighted

Re: subtracting two dates and times

Also the Interval() function could be useful to you.  Below pasted from the QV Desktop Help :

Interval

interval(expression [ , format-code ])

The interval function formats the expression as a time interval according to the string given as a format-code. If the format code is omitted, the time format set in the operating system is used. Intervals may be formatted as a time, as days or as a combination of days, hours, minutes, seconds and fractions of seconds.

Examples:

The examples below assume the following operating system settings:

   

Short date format:

YY-MM-DD

Time format:

hh:mm:ss

Number decimal separator:

.

interval( A ) where A=0.375 returns:

   

String

09:00:00

Number

0.375

interval( A ) where A=1.375 returns:

   

String

33:00:00

Number

1.375

interval( A, 'D hh:mm' ) where A=1.375 returns:

   

String

1 09:00

Number

1.375

interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:

   

String

365 09:00

Number

365.375

QlikView 11.20 SR11

Highlighted

Re: subtracting two dates and times

Format it with the Interval() function.

Highlighted
Master III
Master III

Re: subtracting two dates and times

That "general format" sounds like something from Excel. If you convert it to a number in Excel, will it show a number or still text?

In Qlik data is (to keep it easy) a number of text. If it is a number you can do calculations with it. A date secretly is a number, just formatted as a date. You can format every number as a date by just putting the date() function around it in your load script.

May you live in interesting times!
Highlighted

Re: subtracting two dates and times

try


interval( [Last Drop Departure Date]-[Last Drop Arrival Date],'hh:mm') as Interval

Highlighted
Partner
Partner

Re: subtracting two dates and times

Hi Moses,

Try this:

num#(Interval(timestamp#(timestamp(Last Drop Departure Date,'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss')

              -timestamp#(timestamp(Last Drop Arrival Date,'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss')

             ,'m')) as Interval

Highlighted
MVP
MVP

Re: subtracting two dates and times

Highlighted
Not applicable

Re: subtracting two dates and times

What is the end result you want to see?

Is it the difference in time? number of days and hours?

or something else?