Berechnung Dauer zwischen zwei Zeitstempeln

[Highty66]

Ich habe bereits eine Liste mit Feiertagen generiert:

[Feiertage]:
LOAD
    date(Datum) as Feiertag,
    #IsFeiertag
FROM [lib://QVD-Sense/System/Werktagsbestimmung.qvd] (qvd);


[Tmp_Feiertags_Liste]:
LOAD
    Concat(
    Chr (39) & Date(Feiertag,'DD.MM.YYYY') & Chr (39), ',') as Feiertags_Liste
RESIDENT Feiertage
Where #IsFeiertag='J';

LET vFeiertagsListe = Peek('Feiertags_Liste', 0, 'Tmp_Feiertags_Liste');

DROP TABLE Tmp_Feiertags_Liste;

Es sollen nun nur die Zeiten an Werktagen zwischen 06:00 und 20:00 Uhr erfasst werden. Ich habe einen Zeitstempel für den Start (TRACK_Bestellt_TS) und einen für das Ende (TRACK_Erfasst_TS). Für diese sind nun die Zeiten nach folgenden Regeln anzupassen, um darauf aufbauend die Berechnung der Dauer durchzuführen:

Start (TRACK_Start_TS)

1. Werktag + zwischen 06:00–20:00 → Zeitstempel von TRACK_Bestellt_TS behalten
2. Werktag + vor 06:00 → 06:00 desselben Tages 
3. Werktag + nach 20:00 → nächster Werktag 06:00
4. Wochenende oder Feiertag → nächster Werktag 06:00

Ende (TRACK_End_TS)

1. Werktag + zwischen 06:00–20:00 → Zeitstempel von TRACK_Bestellt_TS behalten
2. Werktag + nach 20:00 → 20:00 desselben Tages
3. Werktag + vor 06:00 → letzter Werktag 20:00
4. Wochenende oder Feiertag → letzter Werktag 20:00

Wie kann ich nun die Zeitstempel neu für TRACK_Start_TS und TRACK_End_TS so anpassen, dass ich daraus eine Berechnung der Dauer zwischen diesen neuen Zeitstempeln durchführen kann? Meine bisherige Vorgehensweise liefert teilweise Fehler, insbesondere bei Start und Ende an Feiertagen.

// Start nur auf Zeitfenster begrenzen
LEFT JOIN (Trackingdaten)
LOAD
    %BelegID,
    Timestamp(
        If(
            Frac(TRACK_Bestellt_TS) < $(vWorkStartTime)/24,
            Floor(TRACK_Bestellt_TS) + $(vWorkStartTime)/24,
            If(
                Frac(TRACK_Bestellt_TS) > $(vWorkEndTime)/24,
                Floor(TRACK_Bestellt_TS) + $(vWorkEndTime)/24,
                TRACK_Bestellt_TS
            )
        )
    ) AS TRACK_Start_TS
RESIDENT Trackingdaten;

// Ende nur auf Zeitfenster begrenzen
LEFT JOIN (Trackingdaten)
LOAD
    %BelegID,
    Timestamp(
        If(
            Frac(TRACK_Erfasst_TS) < $(vWorkStartTime)/24,
            Floor(TRACK_Erfasst_TS) + $(vWorkStartTime)/24,
            If(
                Frac(TRACK_Erfasst_TS) > $(vWorkEndTime)/24,
                Floor(TRACK_Erfasst_TS) + $(vWorkEndTime)/24,
                TRACK_Erfasst_TS
            )
        )
    ) AS TRACK_End_TS
RESIDENT Trackingdaten;

//**************************************************************************************************************
// ganze Arbeitstage NUR zwischen Start und Ende (ohne Start- & Endtag; inkl. Berückichtigung von Feiertagen)
//**************************************************************************************************************

[WorkDaysBetween]:
LOAD
    %BelegID,

    If(
        Floor(TRACK_End_TS) - Floor(TRACK_Start_TS) < 2,
        0,

        NetworkDays(
            Date(Floor(TRACK_Start_TS)),
            Date(Floor(TRACK_End_TS)),
            $(vFeiertagsListe)
        ) - 2
    ) AS WorkDaysBetween

RESIDENT Trackingdaten;

[marcus_sommer]

Ich würde das über ein Mapping gegen eine spezielle Arbeitszeiten-Tabelle umsetzen. Ausgang wäre ein klassischer Kalender, der um eine Stunden-Information erweitert wird - also pro Tag dann 24 Datensätze. Hier kann man dann per weekday() und ein Feiertagsmapping die WE und Feiertage flaggen.

Die nächste Schritte wäre dann per if-Bedingungen ein paar der Start- und End-Zeiten-Logiken zu flaggen und per interrecord-functions wie peek() und previous() die vorherigen Datensätze zu prüfen, um den jeweils finalen Wert zu definieren.

Hier mal ein Beispiel, dass das Thema anreißt, jedoch keine Arbeitszeiten beinhaltet - wobei es auch hier Postings gibt, die ich momentan aber nicht wiederfinde:

Solved: Re: Ermitteln der einzelnen Datums zwischen zwei D... - Qlik Community - 1608562

[Highty66 · Accepted solution]

Danke Marcus. Mein Kalender definiert bereits reine Werktage  und den Stundenansatz habe ich mithilfe der KI ausschließlich in die Kennzahl verlagert.

Mein derzeitiger (und funktionierender) Lösungsansatz: Im Skript lege ich nun Start und Ende (ggf. korrigiert auf 6:00 bzw. 20:00 Uhr eines Werktages) fest und in der Kennzahlberechnung summiere ich nun alle Stunden, die in diesen Werktagen zwischen 6:00 und 20:00 Uhr liegen.

D.h., dass mein Ladeskript so bestehen bleiben kann, ich nur die Logik in der Kennzahl ändern musste.

[williamjohn5987son]

Dein Problem kommt daher, dass du Start/End-Zeit getrennt kappst, aber Feiertage und Wochenenden nicht sauber als „echte Arbeitstage“ behandelst. Dadurch entstehen falsche Verschiebungen, vor allem an Grenztagen. Besser ist, zuerst eine vollständige Kalenderlogik mit „IstWerktag“, „nächster“ und „vorheriger Werktag“ (inkl. Feiertage) aufzubauen und dann die Zeitstempel darauf zu mappen: Start immer auf Ulta Card login nächsten gültigen Werktag 06:00, wenn außerhalb des Fensters, und Ende auf letzten gültigen Werktag 20:00. Erst danach die Differenz berechnen.  

[Qrishna]

Hi - Do you mind attaching atleast one year data from your QVD?