Solved: Re: Calculating how long a request takes - Qlik Community

Report Inappropriate Content · ‎2015-06-11

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:

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

stigchel · ‎2015-06-12

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

View solution in original post

ganeshreddy · ‎2015-06-11

Hi Burnson,

For this scenario you need time stamp in your data .

Cheers,

Ganesh

stigchel · ‎2015-06-11

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

MarcoWedel · ‎2015-06-11

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

thanks

regards

Marco

petter · ‎2015-06-11

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.

Report Inappropriate Content · ‎2015-06-11

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?

stigchel · ‎2015-06-11

You can us it in both, script or gui

Report Inappropriate Content · ‎2015-06-11

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

Report Inappropriate Content · ‎2015-06-11

stigchel · ‎2015-06-11

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

I've made a verys simple one: