topic Re: subtracting two dates and times in QlikView

subtracting two dates and times

Tue, 03 Nov 2015 05:21:22 GMT

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 Date	Last Drop Departure Date
28/10/2015 06:15	28/10/2015 7:45
28/10/2015 07:30	28/10/2015 09:30
29/10/2015 09:50	29/10/2015 11:30
28/10/2015 21:00	29/10/2015 08:00

Re: subtracting two dates and times

oknotsen — Tue, 03 Nov 2015 10:21:21 GMT

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)

Re: subtracting two dates and times

Tue, 03 Nov 2015 11:21:03 GMT

Hi;

Thanks

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

Re: subtracting two dates and times

Anonymous — Tue, 03 Nov 2015 11:23:09 GMT

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

Re: subtracting two dates and times

Anonymous — Tue, 03 Nov 2015 11:28:35 GMT

Format it with the Interval() function.

Re: subtracting two dates and times

oknotsen — Tue, 03 Nov 2015 11:29:00 GMT

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.

Re: subtracting two dates and times

Kushal_Chawda — Tue, 03 Nov 2015 11:31:58 GMT

try

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

Re: subtracting two dates and times

Anonymous — Tue, 03 Nov 2015 11:32:45 GMT

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

Re: subtracting two dates and times

swuehl — Tue, 03 Nov 2015 11:35:22 GMT

Please have a look at

Why don’t my dates work?‌

Get the Dates Right

Re: subtracting two dates and times

Tue, 03 Nov 2015 11:47:12 GMT

What is the end result you want to see?

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

or something else?

Re: subtracting two dates and times

Tue, 03 Nov 2015 12:25:15 GMT

Hi Nard;

l want to see difference in time, minutes

Re: subtracting two dates and times

oknotsen — Tue, 03 Nov 2015 16:29:44 GMT

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

Re: subtracting two dates and times

Anonymous — Wed, 04 Nov 2015 12:51:31 GMT

Hi Moses,

Please see my post to get the interval in minutes

Re: subtracting two dates and times

MarcoWedel — Wed, 04 Nov 2015 23:20:43 GMT

Hi,

one solution could be:

LOAD *,
    Interval([Last Drop Departure Date]-[Last Drop Arrival Date],'hh:mm') as Duration;
LOAD Timestamp#([Last Drop Arrival Date], 'DD/MM/YYYY hh:mm') as [Last Drop Arrival Date],
    Timestamp#([Last Drop Departure Date], 'DD/MM/YYYY hh:mm') as [Last Drop Departure Date]
FROM [https://community.qlik.com/thread/190047] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco