topic Re: Interval Expression Problem in QlikView

Interval Expression Problem

Mon, 22 Oct 2012 14:43:29 GMT

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!

Re: Interval Expression Problem

MayilVahanan — Mon, 22 Oct 2012 14:53:04 GMT

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

Re: Interval Expression Problem

Mon, 22 Oct 2012 15:30:56 GMT

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

Re: Interval Expression Problem

MayilVahanan — Mon, 22 Oct 2012 15:34:17 GMT

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..

Re: Interval Expression Problem

CELAMBARASAN — Mon, 22 Oct 2012 15:37:59 GMT

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.

Re: Interval Expression Problem

Mon, 22 Oct 2012 15:58:28 GMT

That did the trick. Thanks!!

Re: Interval Expression Problem

Mon, 22 Oct 2012 16:07:06 GMT

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?

Re: Interval Expression Problem

MayilVahanan — Mon, 22 Oct 2012 16:23:32 GMT

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

Re: Interval Expression Problem

Mon, 22 Oct 2012 16:36:07 GMT

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.