calendar-ipad-laptop-digital-istock-1239583186-jpg

Berechnung der Anzahl an Werktagen zwischen Zeitstempeln mit Qlik Sense

Maurice Bätge |

25. Juli 2022 |

Die BI-Software Qlik Sense bietet verschiedene Funktionen, um Werte zu berechnen. Die Interval-Funktion ist dabei die am häufigsten verwendete Methode, um die Differenz zwischen zwei Zeitstempeln zu errechnen. Ein Anwendungsfall für diese Funktion wäre die Berechnung der Schichtzeiten von Maschinen in einer Automobilfertigung. Auch in weiteren Themenbereichen, wie in der Stundenerfassung beim Arbeitgeber oder in der Projektplanung, findet diese Funktion Einsatz. Die Interval-Funktion stößt allerdings an ihre Grenzen, wenn die Anforderung lautet, nur die Anzahl der Werktage als Differenz anzuzeigen. Es fehlen passende Parameter, die diese Einschränkung implizieren können. Folglich wird eine Erweiterung der Formel benötigt.

In den folgenden Abschnitten wird eine Möglichkeit geboten, welche diese Einschränkung umgeht, damit die Anzahl an Werktagen zwischen zwei Zeitstempeln in den verschiedensten Anwendungsfällen ermittelt werden kann.

INTERVAL(DATE([endDate]+[endTime])-DATE([startDate]+[startTime]),‘d/hh:mm:ss’)

Abbildung 1: Formel zur Berechnung von zeitlichen Differenzen mit Interval-Funktion

Herleitung der erweiterten Formel

Eine erste Herangehensweise ist, dass die Werktage zwischen den beiden Zeitstempeln getrennt berechnet werden. Anschließend wird die Zeitdifferenz (Stunden, Minuten und Sekunden) mittels einer separaten Kalkulation hinzugefügt.

Die Anzahl der Werktage kann anhand der Funktion »NetWorkDays()« berechnet werden:

NetWorkDays([startDate],[endDate])

Abbildung 2: Befehl zur Ermittlung der Anzahl an Werktagen

Zu beachten ist jedoch, dass mit dieser Funktion alle Werktage (inklusive Start- und Endtag) gezählt werden. Damit ausschließlich die Differenz zwischen den Daten berechnet wird, muss in der Formel jeweils ein Tag zum Startdatum hinzuaddiert (»+1«) und ein Tag vom Enddatum abgezogen (»-1«) werden:

NetWorkDays([startDate]+1,[endDate]-1)

Abbildung 3: Berechnung der Werktage zwischen Start- und Enddatum

Nachdem die Anzahl der Werktage zwischen den Zeitstempeln ermittelt wurde, wird die fehlende Zeitdifferenz ergänzt. Zur Berechnung kann die bekannte Interval-Funktion benutzt werden:

timestamp(INTERVAL(DATE([endDate]+[endTime])-DATE([startDate]+[startTime]),‘mm.ss’),‘hh:mm:ss’)

Der Output beinhaltet die komplette Differenz (inklusive der Anzahl an Tagen). Da die Tage zwischen den Zeitstempeln vorab berechnet wurden, können diese mithilfe der Angabe eines Datumsformates »abgeschnitten« werden.

Vollständig sieht die Formel und der resultierende Output nun wie folgt aus:

=NetWorkDays([startDate]+1,[endDate]-1)&‘days and’&timestamp(INTERVAL(DATE([endDate]+[endTime])-DATE([startDate]+[startTime]),‘mm.ss’),‘hh:mm:ss’)&‘hours’

Abbildung 4: Tabellarische Anzeige der ersten Lösung

Vorberechnung im Skript – Neuer Lösungsansatz

Die erste Lösung führt zwar zum Ziel, bringt jedoch zwei Probleme mit sich. Zum einen kann diese nur als Dimension (in Textform) und nicht als Kennzahl verwendet werden. So sind Möglichkeiten der Weiterberechnung, beispielsweise die durchschnittliche Differenz aller Einträge, nicht anwendbar. Zum anderen wird die Performance der App beeinträchtigt, da die erste Lösung zur Laufzeit berechnet wird. Immer wenn eine Selektion getätigt wird, wird die Berechnung erneut ausgeführt.

Aus diesem Grund ist eine weitere Lösung entstanden, welche im Skript vorberechnet wird. Der Lösungsweg wurde hier leicht abgeändert.

Schritt 1

if([endDate]=[startDate], 0, ([endDate]–[startDate])-1)-NetWorkDays([startDate]+1, [endDate]-1)asWeekend_Days

Zunächst wird geprüft, ob sich Start- und Enddatum denselben Tag teilen. In diesem Fall ist die Anzahl der Wochenendtage automatisch gleich 0. Befinden sich die Zeitstempel an verschiedenen Tagen, wird zunächst die Differenz zwischen diesen ermittelt. Die Zeitwerte werden subtrahiert und ein weiterer Tag (»-1«) wird abgezogen. Als anschauliches Beispiel liegt somit zwischen dem Startdatum 16.03.2022 (44636) und dem Enddatum 18.03.2022 (44638) eine Differenz von einem statt von zwei Tagen.

Schritt 2

Im nächsten Schritt werden die Werktage von der Differenz abgezogen, sodass ausschließlich die Wochenendtage enthalten bleiben. Durch ein erneutes Laden der Daten, können diese Wochenendtage nun benutzt werden, um diese in der bekannten „Interval“-Funktion einzubeziehen:

INTERVAL((DATE([endDate]+[endTime])-DATE([startDate]+[startTime])))-Weekend_Days as Diff_Time

Die Differenz wird somit »ganz normal« berechnet und die Anzahl der Wochenendtage werden anschließend abgezogen.

Das vollständige Skript lautet:

Abbildung 5: Auszug aus dem Skriptbeispiel

Ein Vorteil der zweiten Lösung ist, dass die Differenz als Kennzahl verwendet werden kann.

Diese sieht unformatiert wie folgt aus:

Abbildung 6: Tabellarisches Zwischenergebnis (unformatiert)

Der Wert kann in den Einstellungen der Kennzahl formatiert werden:

Abbildung 7: Einstellungen zur Formatierung der Werte

Zusätzlich können anhand der Daten weitere Wert, wie z.B. die aufsummierte Gesamtdifferenz oder die durchschnittliche Differenz, angezeigt werden.

Abbildung 8: Gesamtwert-Funktionen

Fazit

Qlik Sense bietet viele Funktionen, um diverse Kalkulationen zu erstellen. Es werden jedoch nicht alle Anwendungsfälle abgedeckt, sodass Probleme wie die Berechnung der Werktage zwischen zwei Zeitstempeln keine leichte Lösung mit sich bringen.

Diese sind jedoch nicht unlösbar. Durch die Verknüpfung mehrerer Funktionen können etliche Lösungswege erstellt werden, sodass alle Probleme gelöst werden können. Die Vor- und Nachteile der einzelnen Lösungen müssen abgewogen werden. In unserem Fall war der Lösungsweg, die Kalkulation im Skript vorzubereiten, mit vielen Vorteilen behaftet. Durch die Vorberechnung wird die Performance der App kaum beeinträchtigt und die Werte können gut zur Weiterberechnung wiederverwertet werden.

Haben Sie weitere Frage zu Qlik Sense? Dann beantworte ich Ihnen diese gerne.