Endzeitpunkt und Dauer ermitteln / Determine end time and duration

[Sarah_GB]

Hallo,

ich möchte mit QlikSense einen Verlauf von Buchungen visualisieren.

In regelmäßigen Abständen werden Statusbuchungen getätigt. Dazu wird der Status in Form der ID und die Zeit der Stempelung in der Datenbank dokumentiert.

Nun möchte ich die Dauer ermitteln, bis die nächste Stempelung erfolgt ist sowie den Endzeitpunkt bestimmen (Endzeitpunkt = Startzeitpunkt der nächsten Buchung), sodass ich in einem Balkendiagramm/Gantt-Diagramm den tatsächlichen Verlauf darstellen kann. In der Datenbank selber kann ich keine Änderungen vornehmen, darum benötige ich eine Lösung, wie ich es in QlikSense selber berechnen kann.

Hat jemand eine Idee, wie ich die Dauer und den Endzeitpunkt bestimmen kann?

Danke im Voraus!

_____________________________

Hello,

I would like to use QlikSense to visualize a history of bookings.

Status bookings are made at regular intervals. For this purpose, the status and time of stamping is documented in the database.

Now I would like to determine the duration until the next booking is made, as well as the end time (end time = start time of the next booking), so that I can display the actual course in a bar chart/Gantt chart. I can't make any changes in the database itself, so I need a solution how to calculate it in QlikSense itself.

Does anyone have an idea how I can determine the duration and the end time?

Thanks in advance!

[hic]

Assuming that start_machine_status is a timestamp with both date and time information, the following would work:

// === Step 1: Load all data ===
RawData:
Load ..., start_machine_status From ... ;

// === Step 2: Re-sort (newest timestamps first) and create end-time and duration ===
Data:
Load
Interval(end_machine_status-start_machine_status) as duration,
*;
Load
Peek(start_machine_status) as end_machine_status,
*
Resident RawData
Order By start_machine_status Desc ;

Drop Table RawData ;

If you have multiple machines, you need to add the MachineID in the logic (ordering and making sure that Peek() doesn't look into the data of an other machine).

Good Luck!

[Sarah_GB]

Thanks for your reply!

I'm new to QlikSense. Could you also help me with the Machine ID?

Would that be a Step 3? Or is the integration already needed in Step 2?

Thanks again

[hic]

Two changes:

1. Order by machine_id also 
2. Handle first row within each machine_id. Here the end time is missing, so I have used Now(). But maybe it's better with Null()?

// === Step 1: Load all data ===
RawData:
Load ..., machine_id, start_machine_status From ... ;

// === Step 2: Re-sort (newest timestamps first, grouped by machine_id) and create end-time and duration ===
Data:
Load
Interval(end_machine_status-start_machine_status) as duration,
*;
Load
If(machine_id=Peek(machine_id),Peek(start_machine_status),Now()) as end_machine_status,
*
Resident RawData
Order By machine_id, start_machine_status Desc ;

Drop Table RawData ;

[Sarah_GB · Accepted solution]

That's how it looks now:

// === Steps for calculating the duration ===

Concatenate (Main)
LOAD
id as machin_status_time_id,
personal_id,
workorder_id,
workplace_id,
machine_status_id,
machine_status_duration,
start_machine_status,
end_machine_status,
active as machine_status_time_active
FROM .... ;

// === Re-sort (newest timestamps first, grouped by machine_id) and create end-time and duration ===
Data:
Load
Interval(end_machine_status-start_machine_status) as duration,
*;

Load
If(workplace_id=Peek(workplace_id),Peek(start_machine_status),Now()) as end_machine_status,
*
Resident Main
Order By workplace_id, start_machine_status Desc ;

Drop Table Main;

But if I try to upload it, the following error comes up:

But as far as I checked every field name is unique.

I would really appreciate if you could help me again 

[hic]

You have two definitions of "end_machine_status" - one is in the "*".

[Sarah_GB]

Thank you! I renamed it 'end_time_machine_status'

// === Re-sort (newest timestamps first, grouped by machine_id) and create end-time and duration ===
Data:
Load
Interval(end_time_machine_status-start_machine_status) as duration,
*;

Load
If(workplace_id=Peek(workplace_id),Peek(start_machine_status),Now()) as end_time_machine_status,
*
Resident Main
Order By workplace_id, start_machine_status Desc ;

Drop Table Main;

However the duration is not issued: