Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
capriconuser
Creator
Creator

Interval timings

i have following dates

start_Date                                     due_Date                           complete_Date

25-11-2020 09:00 AM25-11-2020 06:00 PM30-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

 

 

Labels (2)
4 Replies
edwin
Master II
Master II

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')

edwin_0-1608733997984.png

 

capriconuser
Creator
Creator
Author

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

edwin
Master II
Master II

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:

edwin_0-1608822509406.png

 

edwin
Master II
Master II

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