Interval Expression Problem

[#1]

Hi All,

Can anyone diagnose why this expression is not working?

The Total Time expression is:

=Interval(Timestamp(activity_totime,'M/DD/YY HH:MM:SS TT')-Timestamp(activity_fromtime,'M/DD/YY HH:MM:SS TT'),'HH:MM')

Thanks!

[MayilVahanan]

HI

Try like this

=Interval(Timestamp('9/11/2012 10:30:00 AM','M/DD/YY hh:mm:ss tt')-Timestamp('9/11/2012 11:15:00 AM','M/DD/YY hh:mm:ss tt'),'hh:mm') gives -00:45

Hope it helps

[#3]

Thanks for the help, Mayil.  Using my field names, what would be different about the statements though? Am I missing something?

[MayilVahanan · Accepted solution]

HI

Use hh:mm:ss tt instead of caps..HH:MM:SS TT

So it brings. please test it in textbox bu using both format.. you can find the difference..

[CELAMBARASAN]

Qlikview expression evaluator takes MM as Month becasue M(Capital) is used for month.

m is used for minutes.

Dates

• To describe the day, use the symbol "D" for each digit. 
• To describe the month number, use the symbol "M" or "MM" for one or two digits. "MMM" denotes short month name in letters as defined by the operating system or by the override system variable MonthNames in the script. "MMMM" denotes long month name in letters as defined by the operating system or by the override system variable LongMonthNames in the script. 
• To describe the year, use the symbol "Y" for each digit. 
• To describe the weekday, use the symbol "W". One W will return the number of the day (e.g. 0 for Monday) as a single digit. "WW" will return the number with two digits (e.g. 02 for Wednesday). "WWW" will show the short version of the weekday name (e.g. Mon) as defined by the operating system or by the override system variable DayName in the script. "WWWW" will show the long version of the weekday name (e.g. Monday) as defined by the operating system or by the override system variable LongDayName in the script

Times

• To describe the hours, use the symbol "h" for each digit. 
• To describe the minutes, use the symbol "m" for each digit. 
• To describe the seconds, use the symbol "s" for each digit. 
• To describe the fractions of a second, use the symbol "f" for each digit. 
• To describe the time in AM/PM format, use the symbol "tt" after the time.

[#6]

That did the trick. Thanks!!

[#7]

One more question.  When I export it to Excel, Excel changes the format so that an elapsed time of 45:49 (45 hours and 49 minutes) shows up as 45:49:00, or 1/1/1900 9:49:00 PM.  Is there a way to adjust the format in QlikView so that it will be exported properly?

[MayilVahanan]

HI

Try like this

Interval(Timestamp('9/11/2012 10:30:00 AM','M/DD/YY hh:mm:ss tt')-Timestamp('9/11/2012 11:15:00 AM','M/DD/YY hh:mm:ss tt'),'hh:mm:ss')

may be it helps

[#9]

I tried that but then it forces me to have hh:mm:ss in the QlikView table instead of hh:mm like I want. Hmm.