Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

harson
New Contributor III

Time between tow timestamps

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,

15 Replies
MVP
MVP

Re: Time between tow timestamps

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

Business Working Hours Calculation

adamdavi3s
Honored Contributor

Re: Time between tow timestamps

This link might help

LMGTFY

dsharmaqv
Contributor III

Re: Time between tow timestamps

try this

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

Not applicable

Re: Time between tow timestamps

Hi

Directory;

LOAD Id,

     Start_Time,

     End_Time,

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

FROM TABLE

harson
New Contributor III

Re: Time between tow timestamps

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

harson
New Contributor III

Re: Time between tow timestamps

Kiran

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

MVP
MVP

Re: Time between tow timestamps

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

harson
New Contributor III

Re: Time between tow timestamps

@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

MVP
MVP

Re: Time between tow timestamps

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.