Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Issue with timestamp

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?

12 Replies
tresesco
MVP
MVP

Put timestamp() around your expression like:

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

Anonymous
Not applicable
Author

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

tresesco
MVP
MVP

What is your expected output from your above example?

Anonymous
Not applicable
Author

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

tresesco
MVP
MVP

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

Anonymous
Not applicable
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
sunny_talwar

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

Capture.PNG

sunny_talwar

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?