Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have following dates
start_Date due_Date complete_Date
25-11-2020 09:00 AM | 25-11-2020 06:00 PM | 30-11-2020 10:27 AM |
when I minus between start_date and due_Date like this
interval(due_date - start_Date, 'hh:mm:ss')
I got 33:00 :00 hours which is wrong .......this should be 09:00:00 because from 09 am to 06 pm there is 9 hours
but when i used same in object and use this formula like this
=Interval('09:00:00 AM' - '06:00:00 PM', 'HH:MM:SS')
i got 09:00:00
why this is happened?
any solution
make sure the format of your timestamp is correct:
SET TimestampFormat='DD-MM-YYYY hh:mm[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
load timestamp#(start_Date) as start_Date,timestamp#(due_Date) as due_Date,timestamp#(complete_Date) as complete_Date inline [
start_Date,due_Date,complete_Date
25-11-2020 09:00 AM,25-11-2020 06:00 PM,30-11-2020 10:27 AM
];
=interval(due_Date-start_Date,'hh:mm:ss')
i do this but now i unable to get the result when I use timestamp# . but when I use timestamp i got 33:00 instead of 09:00 @edwin
check file
you may have a problem with the data format. can you create a small XLS with just the dates and attach it?
these are what your dates look like when you loaded them:
you use timestamp and timestamp# depending on your input. i used timestamp# just for my specific case
Timestamp - script and chart function ‒ QlikView
Timestamp# - script and chart function ‒ QlikView