Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Gestion-PSD
Creator II
Creator II

Merge times with timetable to obtain effective time

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:

Event EventTime Old Value NewValue
Creation 2022-04-25 10:25:00 - -
Status Change 2022-04-26 17:51:00 Open Solved

 

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:

Event EventTime Start End
Creation 2022-04-25 10:25 2022-04-25 08:00 22-04-25 18:00
Status Change 2022-04-26 17:51 2022-04-26 08:00 22-04-26 18:00

 

How can I select correct subinterval?

 

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

Event Event time StartSubsegment EndSubSegment
Creation 2022-04-25 10:25 2022-04-25 10:25 22-04-25 18:00
Status Change 2022-04-26 17:51 2022-04-26 17:51 22-04-26 18:00
Creation 2022-04-25 10:25 2022-04-25 08:00 2022-04-25 10:25
Status Change 2022-04-26 17:51 2022-04-26 08:00 2022-04-26 17:51

 

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

Labels (1)
1 Reply
Gestion-PSD
Creator II
Creator II
Author

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.

Event Event time Old Value New Value
woking time start 2022-04-25 08:00 - -
creation 2022-04-25 10:25 - -
working time end 2022-04-25 18:00 - -
working time start 2022-04-26 08:00 - -
status change 2022-04-26 17:51 open solved
working time end 2022-04-26 18:00 - -

 

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