topic Re: Time between tow timestamps in QlikView

Time between tow timestamps

harson — Wed, 25 Nov 2020 16:16:04 GMT

Hello community,

I have to calculate the time of taking charge of a ticket taking into account only the hours of opening and closing every day.

Example :

open-close

Monday : 08h00 - 12h00 and 14h00 - 16h00

Tuesday : 08h00 - 11h00 and 16h00 - 20h00

Wednesday : 11h00 - 16h00 and 21h00-Thursday08h00

Thursday : 10h00-15h00 and 20h00-Friday09h00

Saturday : 08h00-12h00

Here are the data

Date_recept Date_traitement

06/02/2017 07:00:00 06/02/2017 15:00:00

06/02/2017 21:00:00 07/02/2017 08:30:00

08/02/2017 22:00:00 09/02/2017 07:00:00

09/02/2017 16:00:00 09/02/2017 20:30:00

11/02/2017 11:00:00 13/02/2017 09:00:00

And I would like to have the following results

Date_recept Date_traitement Duration

06/02/2017 07:00:00 06/02/2017 15:00:00 05:00:00

06/02/2017 21:00:00 07/02/2017 08:30:00 00:30:00

08/02/2017 22:00:00 09/02/2017 07:00:00 09:00:00

09/02/2017 16:00:00 09/02/2017 20:30:00 00:30:00

11/02/2017 11:00:00 13/02/2017 09:00:00 02:00:00

Any ideas ?

Thanks in advance,

Re: Time between tow timestamps

MK_QSL — Wed, 22 Feb 2017 08:40:46 GMT

Go through below link. You will get the idea what to do with your problem.

Business Working Hours Calculation

Re: Time between tow timestamps

adamdavi3s — Wed, 22 Feb 2017 08:41:34 GMT

This link might help

LMGTFY

Re: Time between tow timestamps

dsharmaqv — Wed, 22 Feb 2017 09:32:08 GMT

try this

Date#(rDate_recept,'MM/DD/YYYY HH:MM:SS') -Date#(Date_traitement,'MM/DD/YYYY HH:MM:SS')

Re: Time between tow timestamps

Wed, 22 Feb 2017 11:16:55 GMT

Directory;

LOAD Id,

Start_Time,

End_Time,

Interval(Start_Time-End_Time,'h-mm-ss') as Difference

FROM TABLE

Re: Time between tow timestamps

harson — Wed, 22 Feb 2017 12:27:11 GMT

Deepak

Thanks, but we should not count the hours outside of the opening and closing hours. As I did in the above examples.

The duration is calculated according to the hours of opening and closing. Hours outside the opening and closing times are ignored

Re: Time between tow timestamps

harson — Wed, 22 Feb 2017 12:27:23 GMT

Kiran

Thank, but we should not count the hours outside of the opening and closing hours. As I did in the above examples

Re: Time between tow timestamps

MK_QSL — Wed, 22 Feb 2017 12:30:59 GMT

Have you checked the link I have provided. That link will solve your problem.

Re: Time between tow timestamps

harson — Wed, 22 Feb 2017 12:45:15 GMT

@Manish

I tried but it did not meet my expectations.

The opening and closing hours are not fixed, variable for each day.

And the duration is calculated according to the hours of opening and closing. Hours outside the opening and closing times are ignored

Re: Time between tow timestamps

MK_QSL — Wed, 22 Feb 2017 12:51:20 GMT

You have to create two new fields having closing and opening hours and left join them with the fact table.

You will not get 100% accurate ready-made answer...

Try to use the logic provided and implement it with some additional changes.

Re: Time between tow timestamps

adamdavi3s — Wed, 22 Feb 2017 13:33:03 GMT

I agree with this... it might be possible to create a formula but i started down that rabbit hole and it gets a bit crazy, this is about 1/10th done if you want to take it from here

=if(floor(Date_traitement)=floor(Date_recept),
pick(
  match(WeekDay(Date_recept),'Mon','Tue','Wed','Thu','Fri','Sat','Sun'),
  (if(time(Date_recept)<'08:00:00' AND time(Date_traitement)>'12:00:00',4,if(time(Date_recept)>='08:00:00' and time(Date_recept)<'12:00:00',num(time(Date_recept) - '08:00:00'))))+//Monday am
  (if(time(Date_traitement)>'16:00:00' and time(Date_recept)<'14:00:00',2,0)), //Monday pm
  'Tue','Wed','Thu','Fri','Sat','Sun'),'x')

Re: Time between tow timestamps

MK_QSL — Wed, 22 Feb 2017 13:43:24 GMT

It's a time consuming work.. I can't do right now.. Will try later... Till then keep trying.!!!

Re: Time between tow timestamps

adamdavi3s — Wed, 22 Feb 2017 13:46:52 GMT

sorry I am not the OP so won't be spending more time on this~!

Re: Time between tow timestamps

MK_QSL — Wed, 22 Feb 2017 13:50:57 GMT

Sorry for the confusion..

Re: Time between tow timestamps

sasiparupudi1 — Thu, 23 Feb 2017 08:02:11 GMT

Will There be a case where say

Date_recept Date_traitement

06/02/2017 07:00:00 06/02/2017 11:00:00

or the time intervals specific to the each date?

first open-close is only applicable to Date_recept

Second open-close is only applicabel to Date_traitement

let me know please

Sasi

Re: Time between tow timestamps

harson — Thu, 23 Feb 2017 08:53:59 GMT

Hi Sasi

Date_recept = Date and time of receipt of ticket

Date_traitement = Ticket processing date and time

In fact, I want to count the waiting time of a ticket before its processing.

For your example there, the ticket is received on 06/02/2017 07:00:00 and processed on 06/02/2017 11:00:00 (24 hour format)

On Monday 06/02/2017, the opening time is at 08:00 and the closing at 12:00. Then for this ticket the waiting time is 3:00:00, It's counted from the opening time if the ticket is received outside the opening and closing time,

And the hours outside opening and closing hours are not taken into account.