Calculating how long a request takes

[#1]

Hi,

I'd like to ask how I can calculate the total time between two dates and then display this data in a visual representation?

For instance:

Open Dates:

5/24/2014

6/11/2014

6/22/2014

6/22/2014

9/12/2014

Close Dates:(respectively)

5/24/2014

6/12/2014

6/23/2014

6/22/2014

11/11/1014

Then what I am looking for is a new table that I would like to display in hours

Hours Open:

2

12

14

1

1443

I know how to take the data and display it, but I am more so aiming to display statistics on how long a ticket was open with support or a secondary approver.

Any thoughts?

Thanks Kindly

[#2]

@Piet,

I am using a personal version so I cannot see your file, but example load of what I am doing is here

[#3]

And although I am still trying to display the difference of time between the two dates,

When I choose the only value in the list box with the expression, it isolates one value

[MarcoWedel]

please post some lines of your source file.

thanks

regards

Marco

[MarcoWedel]

formatting your Difference field as Timestamp makes no sense as it contains the integer part of the number of hours.

i.e. 4632:00:00 is displayed for a difference of actually only eight days:

4632/(24*24) = 8

try

Interval(Floor(complete_date-create_date,'01:00:00')) as Difference

instead.

hope this helps

regards

Marco

[stigchel]

This is probably about your date format, can you try with:

Interval(timestamp(complete_date), 'hh:mm:ss')-timestamp(create_date), 'hh:mm:ss'), 'D hh:mm') as Diff;

If that does not help please post a sample qvw, or at least some lines of your source data.

This is what I had:

Data:

LOAD *, Interval([Close Dates]-[Open Dates], 'D hh:mm') as Diff;

Load * inline [Open Dates,Close Dates

5/24/2014 12:00:00,5/24/2014 16:00:00

6/11/2014 12:00:00,6/12/2014 17:21:00

6/22/2014 12:00:00,6/23/2014 18:11:00

6/22/2014 12:00:00,6/22/2014 20:22:00

9/12/2014 12:00:00,11/11/2014 13:51:00

];

Result:

[#7]

@Piet,

I tried this method in my QVW and also in a test QVW but could not get results for diff.

Is there something in the sheet object that I need to change? I can't seem to display any results for the Diff column

[#8]

Hey Marco,

By source data I think you mean the original data where I am pulling my information from.

Here are a few lines from my source.

request_id	create_date	submit_date	complete_date	status_date	route_status
256713	6/9/2015 08:24:48:34	6/9/2015 08:26:15:467	6/9/2015 10:26:41:793	6/9/2015 10:26:41:793	Approved
256713	6/9/2015 08:24:48:34	6/9/2015 08:26:15:467	6/9/2015 10:26:41:793	6/9/2015 10:26:41:793	Submitted
256716	6/9/2015 08:35:52:343	6/9/2015 08:39:34:673	6/9/2015 10:25:15:323	6/9/2015 10:25:15:323	Approved
256716	6/9/2015 08:35:52:343	6/9/2015 08:39:34:673	6/9/2015 10:25:15:323	6/9/2015 10:25:15:323	Submitted
256752	6/9/2015 10:21:26:9	6/9/2015 10:24:43:857	6/9/2015 10:24:54:107	6/9/2015 10:24:54:107	Approved
256752	6/9/2015 10:21:26:9	6/9/2015 10:24:43:857	6/9/2015 10:24:54:107	6/9/2015 10:24:54:107	Submitted
242922	2/6/2015 08:50:54:39	2/6/2015 08:58:46:053	2/6/2015 09:15:26:557	Pending
251614	4/29/2015 15:29:33:58	4/29/2015 15:47:44:883	5/5/2015 07:23:19:82	Rejected
251614	4/29/2015 15:29:33:58	4/29/2015 15:47:44:883	5/5/2015 07:23:19:82	Pending

[stigchel · Accepted solution]

Yes, so it was your date format. I've successfully tested the following:

Load *,Interval([complete_date]-[create_date], 'D hh:mm') as Diff;

LOAD request_id,

     Date(Date#(create_date,'D/MM/YYYY hh:mm:ss:fff')) as create_date,

     Date(Date#(submit_date,'D/MM/YYYY hh:mm:ss:fff')) as submit_date,

     Date(Date#(complete_date,'D/MM/YYYY hh:mm:ss:fff')) as complete_date,

     status_date,

     route_status

FROM

[https://community.qlik.com/thread/168043?sr=inbox&ru=200580]

(html, codepage is 1252, embedded labels, table is @2);

[#10]

Hey Marco,

So I tried this line and I think there is something basic I am not understanding.

There is something in my objects that Isolates the date to one value. I'm also trying to expand that as well.

Otherwise the format looks okay in HH:MM:SS if that is what is displayed.

Thank you for your patience with me

[#11]

@Piet

Okay!! I'm getting somewhere. I see how you load the interval first as a separate line.

I also see how you formatted the dates beforehand as well.

I also appreciated how you were able to pull the data specifically from my entry on this webpage. That was cool.

Now I am looking to fix up some values and trying to understand why some values are negative here.