Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Hi;
Thanks
My format is a general format. What formula do l use to incorporate date format?
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.
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
Format it with the Interval() function.
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.
try
interval( [Last Drop Departure Date]-[Last Drop Arrival Date],'hh:mm') as Interval
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
What is the end result you want to see?
Is it the difference in time? number of days and hours?
or something else?