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: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Time taken to close call logic

Hi All,

I need some help with the following if possible please?

I need to find the Time to Close time.

I have a Call Open timestamp and a Call Close timstamp and i need to fine the difference beween them to give me the time taken to resolve the call.

But it gets a bit tricky as the working day is between 9-5, so if a call is opened on Tuesday at 10am and closed on Wednesday at 11am then the time would be:

Tues 10am to Tues 5pm = 7hrs

Wed 9am to 11am = 2hrs

Total time taken = 9hrs

I have attached a sample.

Thanks

11 Replies
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Does anyone have any ideas?

Gysbert_Wassenaar

Try this expression:

(Date#([Closed WorkingDateTime],'DD/MM/YYYY hh:mm')

  - Date#([Create WorkingDateTime],'DD/MM/YYYY hh:mm'))*24

- 16 * (floor(Timestamp#([Closed WorkingDateTime],'DD/MM/YYYY hh:mm'))

                  - floor(Timestamp#([Create WorkingDateTime],'DD/MM/YYYY hh:mm')))


talk is cheap, supply exceeds demand
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, thanks for this.

When i try it it brings back incorrect results.

I have attached my sample

Gysbert_Wassenaar

That's because you're formating it for display as time (the fraction of a day) which it isn't. The difference between created and closed for ID 10001 is fifteen minutes, that's 1/4th of an hour or 0.25 hour. That is the value that is calculated. However, by formatting it as time you get it as 0.25 days which displays as 6 hours and 0 minutes: 6:00


talk is cheap, supply exceeds demand
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

of course, sorry, im having a blonde moment.

How would i display it as days/hours/minutes?

Gysbert_Wassenaar

If you give the first expression the name Duration, you can create another one like this:

floor([Duration]/8) & '/' & Mod(floor(Duration),8) & '/' & floor(Frac(Duration)*60)


talk is cheap, supply exceeds demand
hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Hi, thanks that sort of works, the only thing that is wrong now is that if a call is opened on the 1st of the month and closed on the 26th month, the days taken is being reported as 25. but its actually only about 17 as weekends should not be included.

Do you know how i can resolve this? in the script i have a 'Schedule' containing times for weekdays and weekends. Would i also need days?

Thanks

See attached

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

Can anyone help with this please?

Gysbert_Wassenaar

I think you should split the Datetimes in a date and a time field. Then you can simply use the networkdays function to calculate the number of work days between the creation and closing dates: networkdays(CreateDate, CloseDate). If you have dates for holidays available you can add those as additional arguments to the networkdays function.

And you can substract the times to get the hours and minutes with something like if(CloseTime<CreateTime, CloseTime - (CreateTime - 8), CloseTime - CreateTime)


talk is cheap, supply exceeds demand