Merge times with timetable to obtain effective tim... - Qlik Community

Gestion-PSD · ‎2022-05-12

Hi all!

I'll try to explain the puzzle I'm trying to solve.

We've a list of events wiht event_time and event_type which we get events duration:

With those events, we can calculate a solution time of 2022-04-26 17:51 - 2022-04-25 10:25 = 1886 minutes= 1 day, 7 hours, 26 minutes. Fine.

But we now have a problem: This solution time is sobreestimated because it counts non-wokring hours, so the solution time isn't real.

Out working time is form 08:00 to 18:00. So, time between 18:00 and 08:00 wouldn't count in solution times.

So, what I'm looking for is (for that example) to have a solution time of

(2022-04-25 18:00 - 2022-04-25 10:25) + (2022-04-26 17:51 - 2022-04-26 08:00) = 1046 minutes= 17 hours, 26 minutes.

But I don't know hot to reach it.

I'd tried with IntervalMatch with working timetable and events time:

How can I select correct subinterval?

If I play with data, I can get subsegments start -> event time and event time -> end,

but how can I get only the subsegments I need (colored) ?

Maybe is better to take another approax, but i'm lost!!

Thanks in advance.

Best regards,

Alonso Torres

Gestion-PSD · ‎2022-05-12

Hi all again.

Maybe, another aprroach is to expand events table with wkorking time starts and ends, and later use Peek() + If() to mark the rows we need.

The problem is that I have no idea of How to create the "working time events", and how to select only needed rows.

Regards,

Alonso Torres

Merge times with timetable to obtain effective time