Discussion Board for collaboration related to QlikView App Development.
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|
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)
Also the Interval() function could be useful to you. Below pasted from the QV Desktop Help :
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.
The examples below assume the following operating system settings:
Short date format:
Number decimal separator:
interval( A ) where A=0.375 returns:
interval( A ) where A=1.375 returns:
interval( A, 'D hh:mm' ) where A=1.375 returns:
interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:
QlikView 11.20 SR11
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.
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