topic Re: Calculating how long a request takes in QlikView

Calculating how long a request takes

Thu, 11 Jun 2015 19:04:08 GMT

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

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:

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

Re: Calculating how long a request takes

ganeshreddy — Thu, 11 Jun 2015 19:45:59 GMT

Hi Burnson,

For this scenario you need time stamp in your data .

Cheers,

Ganesh

Re: Calculating how long a request takes

stigchel — Thu, 11 Jun 2015 19:49:40 GMT

Have a look at the interval function:

interval( A-B, 'D hh:mm' ) where A=97-08-06 09:00:00 and B=96-08-06 00:00:00 returns:


String	365 09:00
Number	365.375

Re: Calculating how long a request takes

MarcoWedel — Thu, 11 Jun 2015 19:57:53 GMT

please post an example for how you wish the hours between two dates to be calculated.

thanks

regards

Marco

Re: Calculating how long a request takes

petter — Thu, 11 Jun 2015 19:59:21 GMT

If the open dates and close dates are in the same table or they are connected through a common key:

Floor( ( [Close Dates] - [Open Dates] ) * 24 )

And the two fields need to have the hours also as part of what is stored just like Ganesh is pointing out.

Re: Calculating how long a request takes

Thu, 11 Jun 2015 20:18:11 GMT

Thanks for the responses.

I've added timestamps to my data and I believe I want the end result to be calculated in total amount of hours.

@piet: I do like the description for the interval. Does that calculation go into the script where interval( complete_date-start_date, 'D hh:mm' )? Or is this within the properties of the list object?

@Marco: I have attached a picture of what I am currently looking at. I would like the format to display the total amount of time difference for each combination of close-open dates. Each open date has a close date in my data.

@petter: I have applied that to my script and have applied the hours to both my fields.

It is still not calculating to what I am looking for. I also do not know if it is calculating one instance or just a set number because the value in "Difference" does not change. Could someone help me understand?

Re: Calculating how long a request takes

stigchel — Thu, 11 Jun 2015 20:21:51 GMT

You can us it in both, script or gui

Re: Calculating how long a request takes

Thu, 11 Jun 2015 20:28:11 GMT

@Piet:

I placed it in my script, however it has a constant value and does not differ with my data. May I ask why this may be happening?

Re: Calculating how long a request takes

Thu, 11 Jun 2015 20:35:09 GMT

Re: Calculating how long a request takes

stigchel — Thu, 11 Jun 2015 20:50:07 GMT

How does your load look like?, can you prepare a sample demonstrating the problem?

I've made a verys simple one:

Re: Calculating how long a request takes

Thu, 11 Jun 2015 21:12:11 GMT

@Piet,

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

Re: Calculating how long a request takes

Thu, 11 Jun 2015 21:17:23 GMT

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

Re: Calculating how long a request takes

MarcoWedel — Thu, 11 Jun 2015 22:21:51 GMT

please post some lines of your source file.

thanks

regards

Marco

Re: Calculating how long a request takes

MarcoWedel — Thu, 11 Jun 2015 22:28:47 GMT

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

Re: Calculating how long a request takes

stigchel — Fri, 12 Jun 2015 09:19:18 GMT

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:

Re: Calculating how long a request takes

Fri, 12 Jun 2015 14:24:39 GMT

@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

Re: Calculating how long a request takes

Fri, 12 Jun 2015 14:27:25 GMT

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

Re: Calculating how long a request takes

stigchel — Fri, 12 Jun 2015 14:51:52 GMT

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

Re: Calculating how long a request takes

Fri, 12 Jun 2015 14:55:18 GMT

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

Re: Calculating how long a request takes

Fri, 12 Jun 2015 15:10:27 GMT

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