topic Re: Issue with timestamp in QlikView

Issue with timestamp

Anonymous — Fri, 09 Dec 2016 11:07:24 GMT

i need to subtract Response time from the difference of Opened and Resolved timestamps.

e.g Opened date - 11/17/2016 5:55:00 AM

Resolution date - 12/9/2016 12:00:00 AM

Response duration - 48:00:00

i used the formula =[Resolution Date]-[Opened Date]-BPS_Response_TimeStamp and gives the result but i am not able to convert it into a timestamp format.can anybody please help?

Re: Issue with timestamp

tresB — Fri, 09 Dec 2016 11:08:58 GMT

Put timestamp() around your expression like:

=Timestamp( [Resolution Date]-[Opened Date]-BPS_Response_TimeStamp , 'hh:mm:ss')

Re: Issue with timestamp

Anonymous — Fri, 09 Dec 2016 11:13:44 GMT

I've done that but doesnt give the right answer..

timestamp ( [Resolution Date]-[Opened Date]-BPS_Response_TimeStamp,'M/D/YYYY h:mm:ss')

I need to display the same format as opened date

Re: Issue with timestamp

tresB — Fri, 09 Dec 2016 11:17:30 GMT

What is your expected output from your above example?

Re: Issue with timestamp

Anonymous — Fri, 09 Dec 2016 11:23:56 GMT

it should be displayed in dd/mm/yyyy hh:mm:ss format

Re: Issue with timestamp

tresB — Fri, 09 Dec 2016 11:29:47 GMT

Format is fine. Could you let know the exact expected output for the example given?

Re: Issue with timestamp

Anonymous — Fri, 09 Dec 2016 11:35:02 GMT

I need to calculate the difference between Opened and Resolved timestamps and the Reponse duration to calculation the exact time taken to resolve a ticket.. Please let me know if you need further clarifications ..i get these in numbers but i need to display in timestamp format of dd/mm/yyy hh:mm:ss

Re: Issue with timestamp

jonathandienst — Fri, 09 Dec 2016 11:46:13 GMT

I think you need Interval() rather than TimeStamp():

=Interval([Resolution Date] - [Opened Date] - BPS_Response_TimeStamp, 'DD/MM/YYYY hh:mm:ss')

Re: Issue with timestamp

sunny_talwar — Fri, 09 Dec 2016 12:04:33 GMT

May be something like this:

=Interval(TimeStamp#('12/09/2016 12:00:00 AM', 'MM/DD/YYYY h:mm:ss TT') - TimeStamp#('11/17/2016 5:55:00 AM', 'MM/DD/YYYY h:mm:ss TT') - Interval#('48:00:00', 'h:mm:ss'), 'D h:mm:ss')

Re: Issue with timestamp

sunny_talwar — Fri, 09 Dec 2016 12:05:43 GMT

How can difference between two dates give you another date? Are you looking for difference in number of days may be?

19 days 18 hrs and 5 mins?

Re: Issue with timestamp

tresB — Fri, 09 Dec 2016 12:07:01 GMT

Yes, interval() would be ideal. However, with that too, format 'DD/MM/YYYY hh:mm:ss' doesn't make much sense. The time difference would never be converted to month or to year.

Re: Issue with timestamp

jonathandienst — Mon, 12 Dec 2016 04:43:58 GMT

I do agree that DD/MM/YYYY does not make much sense with Intervals. I am not even sure if this case is handled properly (whatever that may be). I was paying attention to the difference between TimeStamp() and Interval().

So this should probably be:

=Interval([Resolution Date] - [Opened Date] - BPS_Response_TimeStamp, 'D hh:mm:ss')

Re: Issue with timestamp

Mon, 12 Dec 2016 06:19:14 GMT

Me too wait for the solution