Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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
Specialist III
Specialist III

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
Specialist III
Specialist III

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
Specialist III
Specialist III

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