topic Re: Interval timings in QlikView

Interval timings

capriconuser — Fri, 15 Nov 2024 23:24:48 GMT

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

Re: Interval timings

edwin — Wed, 23 Dec 2020 14:33:27 GMT

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

Re: Interval timings

capriconuser — Thu, 24 Dec 2020 04:57:04 GMT

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

Re: Interval timings

edwin — Thu, 24 Dec 2020 15:08:36 GMT

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:

Re: Interval timings

edwin — Thu, 24 Dec 2020 15:40:47 GMT

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