Welche Kalenderwoche ist das denn? | Tipps Tuesday #074

[RolandSchubert]

Generell gehören ja Datumsfelder zu den häufigsten Problemenursachen, wenn man mit Daten arbeitet. Oft passen die Datumsformate verschiedener Datenquellen nicht zusammen, manchmal gibt es sogar schon innerhalb einer Datenquelle unterschiedliche Schreibweisen. Aber auch die Aggregation von Tagesdaten kann zur Herausforderung werden. Wenn es darum geht, auf Monate zu verdichten, ist es ganz einfach - aus em Datum leiten wir mit den entsprechenden Funktionen den Monat und gegebenenfalls das Jahr ab.

Falls das Datum als Date-Datentyp vorliegt - und davon gehen wir jetzt mal aus - können wir den Monat mit Hilfe der Funktion DateTimeFormat ermitteln. Wenn wir den Parameter "%m" verwenden, erhalten wir die entsprechende zweistellige Zahl.

DateTimeFormat([Date], '%m')

 Alternativ würden "%b" beziehungsweise "%B" den Monatsnamen (Kurz- oder Langform) liefern, mit "%Y" bekommen wir das Jahr.

Aber wie funktioniert es bei der Kalenderwoche?

Eigentlich ist es bei der Kalenderwoche nicht so sehr viel anders, wir tauschen nur den Parameter aus - "%W" liefert die Kalenderwoche, dabei ist der Wochenbeginn immer der Montag.

DateTimeFormat([Date], '%W')

Für den 24. Juni 2022 erhalten wir so die Kalenderwoche 25. Passt! Auch für den 24. Juni 2021 wird Kalenderwoche 25 ausgegeben, auch das scheint zu stimmen. 

Aber schon beim 24. Juni 2020 ist der Kalender anderer Meinung - Alteryx liefert wieder 25, dabei sollte es hier die Kalenderwoche 26 sein.   

Vielleicht werden jetzt Erinnerungen an ältere Excel-Versionen wach, da gab es doch ein ähnliches Problem?  

Ja, gab es wirklich. Da wurde immer ab der Woche gezählt, die den 01. Januar enthielt. Schauen wir doch mal, ob das hier genauso funktioniert und wenden DateTimeFormat einfach auf den 01.01. an - vielleicht für die letzten sieben Jahre. Das Ergebnis sieht dann so aus:

Nur für den 01. Januar 2018 wird Woche 01 ermittelt, bei allen anderen ist das Ergebnis "00" - das macht es nicht besser.

Wenn wir uns hier zusätzlich zur Kalenderwoche noch den Wochentag anschauen, wird vielleicht klarer, was hier passiert:

Der 01. Januar 2018 ist der einzige Fall, bei dem der Wochentag ein Montag ist - vielleicht hat das ja System? Wenn wir die Kalenderwoche für jeweils den ersten Montag des Jahres ermitteln, sind wir in jedem Fall bei "01":

Wenn wir für die Jahre, in denen es vor dem ersten Montag einen Tag gibt (d.h. alle außer 2018), mal die Kalenderwoche für den Vortag ermitteln, wird die Sache noch klarer - vor dem ersten Montag ist offensichtlich die Kalenderwoche immer "00".

Offensichtlich beginnt Kalenderwoche 1 immer mit dem ersten Montag im Jahr, die Tage davor werden einfach einer Kalenderwoche "00" zugeordnet. Auch Tage im Dezember, die gegebenenfalls schon zu Kalenderwoche 1 des nächsten Jahres gehören würden, werden  nicht korrekt zugeordnet. 

Leider entspricht diese Zählweise nicht der in Europa gebräuchlichen Nummerierung nach ISO 8601. In Excel wurde dieses Problem vor einigen Jahren durch einen zusätzlichen Parameter behoben, in Alteryx gibt es diesen Paramter leider (noch) nicht.

Und wie können wir die Kalenderwoche nun ermitteln?

Aber irgendwie muss doch die korrekte Kalenderwoche herauszufinden sein. Ist sie natürlich. 

Als Ausgangspunkt benutzen wir einfach die Formel, die wir in Excel vor der Einführung des zusätzlichen Parameters verwendet haben.  Dabei wurde die Kalenderwoche über die Differenz zwischen dem Sonntag vor dem aktuellen Datum und dem Sonntag vor Beginn der ersten Kalenderwoche des Jahres ermitteln. Diese Formel sieht so aus (wir nehmen einfach mal an, das Datum finden wir in Zelle A1):

=KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)

Da diese Formel in Excel die richtige Kalenderwoche gemäß ISO-Standard lieferte, sollte das die "Übersetzung" in Alteryx doch auch tun.

Um dies besser nachvollziehen zu können zerlegen wir die Formel in ihre Bestandteile und bilden sie Schritt für Schritt nach.

Komponente 1:  

A1-WOCHENTAG(A1;2)

Im Grunde wird hier nur der Sonntag ermittelt, der dem jeweiligen Datum (in Zelle A1) vorausgeht, indem der Wochentag (Zahl von 1-7, der Parameter "2" in der Funktion Wochentag legt fest, dass die Zählung am Montag beginnt) vom Datum subtrahiert wird. In Alteryx adressieren wir natürlich keine Zelle, sondern eine Spalte ([Date]), die Zahl für den Wochentag liefert die Funktion DateTimeFormat mit dem Parameter '%u', dabei wird immer ab Montag gezählt. Um nun diese Zahl vom Datum zu subtrahieren, verwenden wir die Funktion DateTimeAdd. Ingesamt sieht das dann so aus:

DateTimeAdd([Date], -ToNumber(DateTimeFormat([Date], '%u')), 'days')

Die Ergebnisspalte nennen wir [CurrentDate], sie erhält dem Datentyp "Date".

Eine Anmerkung noch: DateTimeFormat liefert den "Wochentagszähler" als String, den wir mit "ToNumber" noch in eine Zahl umwandeln müssen

Komponente 2:   

JAHR(A1+4-WOCHENTAG(A1;2))

Hier geht es um das Jahr, zu dem die Kalenderwoche gehört. Falls das Datum (z.B. der 01. Januar) ein Freitag ist, werden 4 Tage addiert, dann der Wochentag subtrahiert (eine 5, also 5 Tage) - damit landen wir beim 31.12. - und das entsprechende Jahr ist das Vorjahr, zu dem dann die Kalenderwoche gehört.

DateTimeFormat(DateTimeAdd(DateTimeAdd([Date], 4, 'days'), - ToNumber(DateTimeFormat([Date], '%u')), 'days'), '%Y')

Entsprechend addieren wir in Alteryx (mit DateTimeAdd) zunächst 4 Tage, dann (wieder mit DateTimeAdd) die Zahl für den Wochentag. Das Ergebnis ist ein Datum, aus dem  wir nun das Jahr ziehen. 

Das Ergebnis landet in der Spalte [Year] mit einem String-Datentyp.

Komponente 3:  

DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))

Aus diesem Jahr wird nun wieder ein Datum erzeugt, das als Basis für die Ermittlung der Kalenderwochenanzahl verwendet werden soll. Übersetzt sieht das so aus:

DateTimeAdd(DateTimeTrim(DateTimeAdd(DateTimeAdd([Date], 4, 'days'), - ToNumber(DateTimeFormat([Date], '%u')), 'days'), 'year'), -11, 'days')

Im Unterschied zu Excel bauen wir hier das Datum nicht aus dem ermittelten Jahr, zu dem die Kalenderwoche gehört, dem Monat "1" und dem Tag "-10" (also 10 Tage vor dem Beginn des Jahres) zusammen, sondern gehen einfach vom 01. Januar dieses Jahres (den bekommen wir mit DateTimeTrim und dem Parameter "year") 11 Tage zurück - gleiches Ergebnis.

Die Ergebnisspalte nennen wir [StartDate], wieder mit dem Datentyp "Date".

Kalenderwoche berechnen:

Um die Kalenderwoche zu ermitteln, wird jetzt die Anzahl Tage zwischen dem Sonntag von dem aktuellen Datum und dem Startdatum ermittelt, durch  7 dividiert und auf den ganzzahligen Wert abgerundet.

=KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)

Wir ermitteln in Alteryx zunächst die Differenz in Tagen mit DateTimeDiff:

DateTimeDiff([CurrentDate], [StartDate], 'days')

Und diese Differenz wird dann durch 7 dividiert und abgerundet:

Floor([Days] / 7)

Insgesamt benötigen wir für die Ermittlung der Kalenderwoche ein Formula Tool mit 5 einzelnen Formeln, die man natürlich auch zusammenfassen könnte - einzeln ist es aber übersichtlicher.

Jetzt scheinen auch die Ergebnisse zu passen:

Neben der Kalenderwoche liefert das Formula Tool auch das entsprechende Kalenderjahr, zu dem die Woche gehört - für die meisten Anwendungsfälle wird das ja auch gebraucht.

Fazit

In diesem Fall liefert Alteryx ausnahmsweise mal nicht genau das, was wir brauchen. Aber die entsprechende Logik lässt sich leicht "nachrüsten" - ein paar Formeln genügen. Und natürlich können wir das auch in ein Makro umwandeln ... vielleicht ist das ja ein Thema für einen folgenden Tuesday Tipp. 

Weitere Tipps Tuesday Beiträge:

Dieser Eintrag ist Teil der Tipps Tuesday-Serie, alle Einträge dieser Serie findest du in unserem Index aufgelistet.

[RolandSchubert]

Generell gehören ja Datumsfelder zu den häufigsten Problemenursachen, wenn man mit Daten arbeitet. Oft passen die Datumsformate verschiedener Datenquellen nicht zusammen, manchmal gibt es sogar schon innerhalb einer Datenquelle unterschiedliche Schreibweisen. Aber auch die Aggregation von Tagesdaten kann zur Herausforderung werden. Wenn es darum geht, auf Monate zu verdichten, ist es ganz einfach - aus em Datum leiten wir mit den entsprechenden Funktionen den Monat und gegebenenfalls das Jahr ab.

Falls das Datum als Date-Datentyp vorliegt - und davon gehen wir jetzt mal aus - können wir den Monat mit Hilfe der Funktion DateTimeFormat ermitteln. Wenn wir den Parameter "%m" verwenden, erhalten wir die entsprechende zweistellige Zahl.

DateTimeFormat([Date], '%m')

 Alternativ würden "%b" beziehungsweise "%B" den Monatsnamen (Kurz- oder Langform) liefern, mit "%Y" bekommen wir das Jahr.

Aber wie funktioniert es bei der Kalenderwoche?

Eigentlich ist es bei der Kalenderwoche nicht so sehr viel anders, wir tauschen nur den Parameter aus - "%W" liefert die Kalenderwoche, dabei ist der Wochenbeginn immer der Montag.

DateTimeFormat([Date], '%W')

Für den 24. Juni 2022 erhalten wir so die Kalenderwoche 25. Passt! Auch für den 24. Juni 2021 wird Kalenderwoche 25 ausgegeben, auch das scheint zu stimmen. 

Aber schon beim 24. Juni 2020 ist der Kalender anderer Meinung - Alteryx liefert wieder 25, dabei sollte es hier die Kalenderwoche 26 sein.   

Vielleicht werden jetzt Erinnerungen an ältere Excel-Versionen wach, da gab es doch ein ähnliches Problem?  

Ja, gab es wirklich. Da wurde immer ab der Woche gezählt, die den 01. Januar enthielt. Schauen wir doch mal, ob das hier genauso funktioniert und wenden DateTimeFormat einfach auf den 01.01. an - vielleicht für die letzten sieben Jahre. Das Ergebnis sieht dann so aus:

Nur für den 01. Januar 2018 wird Woche 01 ermittelt, bei allen anderen ist das Ergebnis "00" - das macht es nicht besser.

Wenn wir uns hier zusätzlich zur Kalenderwoche noch den Wochentag anschauen, wird vielleicht klarer, was hier passiert:

Der 01. Januar 2018 ist der einzige Fall, bei dem der Wochentag ein Montag ist - vielleicht hat das ja System? Wenn wir die Kalenderwoche für jeweils den ersten Montag des Jahres ermitteln, sind wir in jedem Fall bei "01":

Wenn wir für die Jahre, in denen es vor dem ersten Montag einen Tag gibt (d.h. alle außer 2018), mal die Kalenderwoche für den Vortag ermitteln, wird die Sache noch klarer - vor dem ersten Montag ist offensichtlich die Kalenderwoche immer "00".

Offensichtlich beginnt Kalenderwoche 1 immer mit dem ersten Montag im Jahr, die Tage davor werden einfach einer Kalenderwoche "00" zugeordnet. Auch Tage im Dezember, die gegebenenfalls schon zu Kalenderwoche 1 des nächsten Jahres gehören würden, werden  nicht korrekt zugeordnet. 

Leider entspricht diese Zählweise nicht der in Europa gebräuchlichen Nummerierung nach ISO 8601. In Excel wurde dieses Problem vor einigen Jahren durch einen zusätzlichen Parameter behoben, in Alteryx gibt es diesen Paramter leider (noch) nicht.

Und wie können wir die Kalenderwoche nun ermitteln?

Aber irgendwie muss doch die korrekte Kalenderwoche herauszufinden sein. Ist sie natürlich. 

Als Ausgangspunkt benutzen wir einfach die Formel, die wir in Excel vor der Einführung des zusätzlichen Parameters verwendet haben.  Dabei wurde die Kalenderwoche über die Differenz zwischen dem Sonntag vor dem aktuellen Datum und dem Sonntag vor Beginn der ersten Kalenderwoche des Jahres ermitteln. Diese Formel sieht so aus (wir nehmen einfach mal an, das Datum finden wir in Zelle A1):

=KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)

Da diese Formel in Excel die richtige Kalenderwoche gemäß ISO-Standard lieferte, sollte das die "Übersetzung" in Alteryx doch auch tun.

Um dies besser nachvollziehen zu können zerlegen wir die Formel in ihre Bestandteile und bilden sie Schritt für Schritt nach.

Komponente 1:  

A1-WOCHENTAG(A1;2)

Im Grunde wird hier nur der Sonntag ermittelt, der dem jeweiligen Datum (in Zelle A1) vorausgeht, indem der Wochentag (Zahl von 1-7, der Parameter "2" in der Funktion Wochentag legt fest, dass die Zählung am Montag beginnt) vom Datum subtrahiert wird. In Alteryx adressieren wir natürlich keine Zelle, sondern eine Spalte ([Date]), die Zahl für den Wochentag liefert die Funktion DateTimeFormat mit dem Parameter '%u', dabei wird immer ab Montag gezählt. Um nun diese Zahl vom Datum zu subtrahieren, verwenden wir die Funktion DateTimeAdd. Ingesamt sieht das dann so aus:

DateTimeAdd([Date], -ToNumber(DateTimeFormat([Date], '%u')), 'days')

Die Ergebnisspalte nennen wir [CurrentDate], sie erhält dem Datentyp "Date".

Eine Anmerkung noch: DateTimeFormat liefert den "Wochentagszähler" als String, den wir mit "ToNumber" noch in eine Zahl umwandeln müssen

Komponente 2:   

JAHR(A1+4-WOCHENTAG(A1;2))

Hier geht es um das Jahr, zu dem die Kalenderwoche gehört. Falls das Datum (z.B. der 01. Januar) ein Freitag ist, werden 4 Tage addiert, dann der Wochentag subtrahiert (eine 5, also 5 Tage) - damit landen wir beim 31.12. - und das entsprechende Jahr ist das Vorjahr, zu dem dann die Kalenderwoche gehört.

DateTimeFormat(DateTimeAdd(DateTimeAdd([Date], 4, 'days'), - ToNumber(DateTimeFormat([Date], '%u')), 'days'), '%Y')

Entsprechend addieren wir in Alteryx (mit DateTimeAdd) zunächst 4 Tage, dann (wieder mit DateTimeAdd) die Zahl für den Wochentag. Das Ergebnis ist ein Datum, aus dem  wir nun das Jahr ziehen. 

Das Ergebnis landet in der Spalte [Year] mit einem String-Datentyp.

Komponente 3:  

DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))

Aus diesem Jahr wird nun wieder ein Datum erzeugt, das als Basis für die Ermittlung der Kalenderwochenanzahl verwendet werden soll. Übersetzt sieht das so aus:

DateTimeAdd(DateTimeTrim(DateTimeAdd(DateTimeAdd([Date], 4, 'days'), - ToNumber(DateTimeFormat([Date], '%u')), 'days'), 'year'), -11, 'days')

Im Unterschied zu Excel bauen wir hier das Datum nicht aus dem ermittelten Jahr, zu dem die Kalenderwoche gehört, dem Monat "1" und dem Tag "-10" (also 10 Tage vor dem Beginn des Jahres) zusammen, sondern gehen einfach vom 01. Januar dieses Jahres (den bekommen wir mit DateTimeTrim und dem Parameter "year") 11 Tage zurück - gleiches Ergebnis.

Die Ergebnisspalte nennen wir [StartDate], wieder mit dem Datentyp "Date".

Kalenderwoche berechnen:

Um die Kalenderwoche zu ermitteln, wird jetzt die Anzahl Tage zwischen dem Sonntag von dem aktuellen Datum und dem Startdatum ermittelt, durch  7 dividiert und auf den ganzzahligen Wert abgerundet.

=KÜRZEN((A1-WOCHENTAG(A1;2)-DATUM(JAHR(A1+4-WOCHENTAG(A1;2));1;-10))/7)

Wir ermitteln in Alteryx zunächst die Differenz in Tagen mit DateTimeDiff:

DateTimeDiff([CurrentDate], [StartDate], 'days')

Und diese Differenz wird dann durch 7 dividiert und abgerundet:

Floor([Days] / 7)

Insgesamt benötigen wir für die Ermittlung der Kalenderwoche ein Formula Tool mit 5 einzelnen Formeln, die man natürlich auch zusammenfassen könnte - einzeln ist es aber übersichtlicher.

Jetzt scheinen auch die Ergebnisse zu passen:

Neben der Kalenderwoche liefert das Formula Tool auch das entsprechende Kalenderjahr, zu dem die Woche gehört - für die meisten Anwendungsfälle wird das ja auch gebraucht.

Fazit

In diesem Fall liefert Alteryx ausnahmsweise mal nicht genau das, was wir brauchen. Aber die entsprechende Logik lässt sich leicht "nachrüsten" - ein paar Formeln genügen. Und natürlich können wir das auch in ein Makro umwandeln ... vielleicht ist das ja ein Thema für einen folgenden Tuesday Tipp. 

Weitere Tipps Tuesday Beiträge:

Dieser Eintrag ist Teil der Tipps Tuesday-Serie, alle Einträge dieser Serie findest du in unserem Index aufgelistet.