21 Replies Latest reply: Jun 12, 2015 4:18 PM by Thomas Burnson

Calculating how long a request takes

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

• Re: Calculating how long a request takes

Hi Burnson,

For this scenario you need time stamp in your data .

Cheers,

Ganesh

• Re: Calculating how long a request takes

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

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

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

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

You can us it in both, script or gui

• Re: Calculating how long a request takes

@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

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

@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

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

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.

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

@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

thanks

regards

Marco

• Re: Calculating how long a request takes

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

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

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

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

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

• Re: Calculating how long a request takes

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

• Re: Calculating how long a request takes

the first line is a so called preceding load, it takes the result form the load below. I could have loaded the interval in the same load as the dates, but then I would have to repeat the date formatting in that as well.

From the number of questions regarding dates on this forum, dates seem to be difficult. Always check if your dates are loaded as real dates. (see the listbox, I added an expression with Num(date) to check if they were)

Yes you can load from webfiles, in the load script editor click on the Web Files button on the bottom of the screen to start the wizard and paste in the web link.

Now about the negative numbers, you either have a mixed date format or your data is wrong (different sources??). The example in your screenshot show 9/12/2014 and 10/1/2014, which if you take the dateformat of Day/Month/Year is saying that this was created on 9 December 2014 and completed on 10 January 2014, hence the negative number.

• Re: Calculating how long a request takes

Thanks Piet,

I have marked the question as answered as I did indeed find out how to find the difference of two dates and display them.

I have also fixed the negative values by exchanging the month and day in the date format.

However my new problem is something in my data that I do not understand. I will work on it on the meantime, but I may create another forum if I become stuck.

Thank you, gentlemen, for all of your help.

• Re: Calculating how long a request takes

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

hope this helps

regards

Marco

• Re: Calculating how long a request takes

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