Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

hopkinsc
Valued Contributor II

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
Valued Contributor II

Re: Time taken to close call logic

Does anyone have any ideas?

Re: Time taken to close call logic

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
Valued Contributor II

Re: Time taken to close call logic

Hi, thanks for this.

When i try it it brings back incorrect results.

I have attached my sample

Re: Time taken to close call logic

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
Valued Contributor II

Re: Time taken to close call logic

of course, sorry, im having a blonde moment.

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

Re: Time taken to close call logic

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
Valued Contributor II

Re: Time taken to close call logic

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
Valued Contributor II

Re: Time taken to close call logic

Can anyone help with this please?

Re: Time taken to close call logic

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
Community Browser