Benutzerdefinierte Ausdrücke erlauben in DeltaMaster das Erstellen fortgeschrittener dynamischer Berichte, die sich wie von Zauberhand an die eingestellte Periode anpassen. Zeit für einen Überblick der grundsätzlichen Möglichkeiten – aber auch die Neuerungen sollen nicht zu kurz kommen!
Benutzerdefinierte Ausdrücke in der Zeitdimension
Benutzerdefinierte Ausdrücke in Zeitanalyseelementen
In unserer Business-Intelligence-Software DeltaMaster lassen sich Berichte auch in relationalen Anwendungen durchgängig dynamisieren. Einmal angelegt, passen sich dann in Grafischen Tabellen Sparklines, Zeitachsen und Vergleichsperioden, aber auch darauf aufbauende Vergleichswerte wie zum Beispiel relative Zuwächse oder Anteile automatisch an die aktuell eingestellte Periode an.
Solchermaßen aufgebaute Berichte sind somit für die Ewigkeit konzipiert und benötigen dann keinen weiteren Pflegeaufwand.
Das könnte wie im folgenden Beispiel aussehen:
Der Bericht passt sich automatisch an die gewählte Periode an!
Zunächst ist der Bericht auf den Monat September 2021 eingestellt. Die ersten drei Spalten mit aktuellem Wert, Differenz zum Vorjahr absolut und Differenz relativ können wir, ebenso wie die Sparklines über die Magischen Knöpfe anlegen bzw. aktivieren.
Benutzerdefinierte Ausdrücke haben wir hier an mehreren Stellen verwendet: zur Bestimmung des Startelementes der Sparklines – diese starten mit der ersten Periode des Vorjahres. Dabei habe ich bereits einen neuen Befehl “Descendants” aus dem aktuellen Release 6.4.2.1 eingesetzt: Current.year.lag(1).descendants(current.level).first – aber dazu später mehr.
Weiterhin haben wir hier Zeitanalyseelemente angelegt. Das Maximum aller Perioden des aktuellen Jahres wird berechnet, also im Moment aller 12 Monate, für die letzten 3 Monate des Jahres liegen dabei noch keine Werte vor. Wir haben ein Zeitanalyseelement über einen benutzerdefinierten Ausdruck year.descendants(current.level).max erstellt, das uns das Maximum aller Perioden liefert.
Schließlich folgt ein Quotient, der die aktuelle Periode mit dem Maximum vergleicht. Hierfür verwenden wir ein berechnetes Element. In diesen Quotienten gehen auch Definitionen ein, die auf benutzerdefinierten Ausdrücken aufbauen.
Der Bericht passt sich vollautomatisch an, wenn eine andere Periode eingestellt wird. Das kann ein anderer Monat sein, aber im genannten Beispiel funktioniert die Logik auch für Quartale. Nun beginnen die Sparklines mit dem 1. Quartal 2020 und das Maximum berechnen wir nun über die Quartale des Jahres 2021.
Benutzerdefinierte Ausdrücke in der Zeitachse
Eben hatten wir in der Grafischen Tabelle Zeitanalyseelemente verwendet, die auf benutzerdefinierten Ausdrücken basieren, und ebenso das Startelement der Sparklines über einen benutzerdefinierten Ausdruck bestimmt.
Aber auch die Achse einer Grafischen Tabelle kann durch benutzerdefinierte Ausdrücke dynamisch erzeugt werden:
Die Spaltenachse passt sich dynamisch an
Hier habe ich einmal die Spaltenachse über einen benutzerdefinierten Ausdruck “range(Case Current.Level When 1 Then 2 When 2 Then 3 When 3 Then 7 End)” so definiert, dass auf Jahresebene 2 Jahre, auf Quartalsebene 3 Quartale und auf Monatsebene 7 Monate angezeigt werden. Der Zeitraum endet jeweils mit der Periode im Berichtsfilter.
Dabei definieren wir eine Menge von benachbarten Periodenelementen. Die Menge kann aber auch aus einem einzigen Element bestehen.
Schließlich können wir die gleiche Definition des benutzerdefinierten Ausdrucks auch wieder für ein Zeitanalyseelement verwenden. Kennzahlen können bei der Modellierung mit verschiedenen Aggregationsvorschriften angelegt werden, z. B. mit Summation, Maximum- oder Minimumbildung, Anzahl der Einträge in der Faktentabelle und noch weiteren. Trifft dann eine Kennzahl auf eine per benutzerdefiniertem Ausdruck generierte Menge, werten wir sie automatisch gemäß ihrer Aggregationsvorschrift aus:
Die Auswertung erfolgt hier auf den letzten 3 Quartalen
Da wir im Berichtsfilter auf Q3 2021 stehen, werden die Aggregationen nun automatisch auf den Quartalen Q1 2021, Q2 2021 und Q3 2021 berechnet. Die 133,6 Mio. Gesamtumsatz lassen sich oben aus der vorhergehenden Grafischen Tabelle aus 41,4 + 45,5 + 46,7 = 133,6 Mio. rekonstruieren.
Es gab in diesen 3 Quartalen 3223 Transaktionen in der Faktentabelle mit einem durchschnittlichen Umsatz von 41 Tsd. und einem maximalen Einzelumsatz von 6,7 Mio. usw.
Hilfreiche Blogbeiträge
Zum Thema Zeitanalyseelemente und Zeitachsen, die wir als benutzerdefinierte Ausdrücke anlegen, existieren bereits einige Blog-Beiträge:
- Selfservice – nur eine Frage der Zeitanalyseelemente vom September 2020 gibt eine allgemeine Einführung und konzentriert sich auf Zeitanalyseelemente und berechnete Elemente, zeigt aber auch fortgeschrittene Anwendungen, die über Linregslope Prognosen erlauben.
- Dynamische Berichte in relationalen Modellen vom Mai 2021 erläutert, wie man Hilfsdimensionen zur Einstellung von Parametern in den Definitionen der Zeitanalyseelemente nutzen kann, um auch im Präsentationsmodus Berichte in verschiedenen Varianten darstellen zu können. Hier waren auch Beispiele vertreten, in denen zum Beispiel die Anzahl der sichtbaren Monate auf der Achse aus einer Hilfsdimension ausgewählt wird.
- Zeitanalyseelemente einsetzen vom September 2021 konzentriert sich auf Periodenelemente, die sehr flexibel angesprochen werden. Hier gab es zum ersten Mal die Möglichkeit, von einem Periodenelement wieder per firstchild oder lastchild in tiefere Ebenen hinabzusteigen.
- Startelemente für Sparklines vom Dezember 2021 betrachtet ebenfalls dynamisch angesprochene Periodenelemente, die aber bei Sparklines auf der gleichen Ebene wie das ausgewählte Element im Berichtsfilter liegen müssen.
Elemente im benutzerdefinierten Ausdruck
Grundsätzlich besteht ein benutzerdefinierter Ausdruck mindestens aus einem Verweis auf ein Periodenelement. An diesem Element können wir optional eine Menge verankern. Eine solche Menge lässt sich bereits als Achsendefinition oder Ausdruck in einem Zeitanalyseelement nutzen. Schließlich können wir die Menge mit einer zusätzlichen Auswertungsvorschrift versehen und den Ausdruck in einem Zeitanalyseelement einsetzen. Auch in ein berechnetes Element können Zeitanalyseelemente oder benutzerdefinierte Ausdrücke eingehen.
Bei Sparklines wird ein Startelement festgelegt und hier nutzen wir also nur den ersten Schritt. Auch wenn wir den Wert einer einzelnen Periode sehen wollen, reicht dieser Bezug bereits aus und wir haben unser Ziel erreicht.
Im folgenden Bild sind noch einmal die möglichen Befehle zum Verweis auf andere Elemente (diese Befehle können wir auch verketten) dargestellt:
So können andere Periodenelemente angesprochen werden!
Sei ein Ausgangselement orangefarben dargestellt, dann bewegt man sich mit lead(n) und lag(n) jeweils n Schritte vor- oder rückwärts auf der gleichen Ebene. Mit ancestor(m) springt man nach oben zum Vorgänger auf der Ebene mit Levelnummer m. Ebenfalls nach oben geht es mit Befehlen wie year oder quarter.
Mit parent geht es zum direkten Vorgänger.
Ob Ihre Ebenen den logischen Namen richtig zugeordnet sind, können Sie im Logik-Fenster, Modus Modellieren, kontrollieren:
Die Logik stimmt: alle Ebenen richtig zugeordnet!
Zur nächsten Ebene nach unten kann man mit Befehlen wie firstchild oder lastchild gelangen, die jeweils den ersten oder letzten direkten Nachfolger ansprechen.
Am Element verankerte Mengen: Range
Für Achsen oder auch aggregierende Auswertungen benötigt man normalerweise aber Mengen, die wir an einem Element verankern werden!
Ein häufig verwendeter Befehl ist durch range(n) gegeben. Hierbei bezeichnet n die Anzahl der vorangehenden Elemente inklusive des aktuell referenzierten Elements:
Wirkung von range(4) und range(3,2)
Hat man ein Element referenziert, hier z. B. das 3. Quartal 2021, so enthält range(4) dieses Quartal und die drei vorhergehenden bis zum 4. Quartal 2020 in der zeitlichen Reihenfolge. Analog erhält man die Monate von Oktober 2020 bis Januar 2021, wenn man auf Januar 2021 range(4) anwendet.
Es gibt den range-Befehl auch mit einem zweiten Parameter, der den Abstand festlegt. Range(3,2) für Dezember 2021 ergibt 3 Perioden im Abstand von 2 Monaten, also August, Oktober und Dezember 2021. Range(4) verhält sich wie range(4,1).
Ein Element verbunden mit einem “range” ergibt somit eine Menge, die man als benutzerdefinierten Ausdruck in einer Achse verwenden kann. Z. B. sei Juli 2021 im Berichtsfilter gewählt, dann erzeugt die Kombination “current.lag(1).range(3,6)” die folgende Achse:
Drei Monate im Abstand von jeweils 6 Monaten
Bemerkung: Ich verwende den jeweils eingesetzten Achsenausdruck auch als Überschrift eines Berichts.
Vom eingestellten Monat Juli 2021 geht es mit lag(1) zum Juni 2021 und range(3,6) erzeugt dann die drei Monate Jun 2020, Dez 2020 und Jun 2021 im Abstand von jeweils 6 Monaten. Wird der Ausdruck in einem Zeitanalyseelement eingesetzt, so wird über diese 3 ausgewählten Monate aggregiert.
Neue benutzerdefinierte Ausdrücke im Release 6.4.2: Descendants und Children
Mit den Ausdrücken Descendants und Children können wir ausgehend von einem Element ebenfalls Mengen erzeugen. Sie stellen die Gegenstücke zu Ancestor bzw. Parent dar. Sei etwa das Jahr 2021 mit “Current” referenziert:
Children und Descendants
Die unmittelbaren Nachfolger von 2021, also die Quartale im Jahr 2021, lassen sich beispielsweise mit Current.Children() ansprechen (das Current und die “()” können hier auch entfallen). Alternativ könnte ich die vier Quartale auch über Current.Descendants(2) erzeugen, da die Quartale als Nachfolger des Jahres auf dem Level mit der internen Nummer 2 liegen.
Man kann die Quartale auch logisch ansprechen, mit Current.Quarters(). Analog könnte man auch direkt vom Jahr zu allen Monaten dieses Jahres springen: Hierfür eignen sich die Befehle Current.Months() oder Current.Descendants(3). Mit Children kommt man immer nur eine Ebene weiter, ein direkter Sprung von Jahr zu den Monaten nur mit Anwendung von Children ist nicht möglich.
Liegt ein entsprechendes Modell vor, existieren auch noch die Befehle Current.Weeks() und Current.Days().
Wichtig sind hier zwei Dinge: Erstens muss die angesprochene Ebene unterhalb des aktiven Elements liegen, gleiche Ebene ist auch noch möglich. Steht man auf einem Monat, erzeugt Years() eine leere Menge.
Zweitens muss man immer den feinen Unterschied zwischen bspw. “Quarters” und “Quarter” beachten. Quarters nutzt man, wenn man auf dem Jahr steht und die untergeordneten Quartale sehen möchte. Quarter (ohne s) nehmen wir, wenn wir auf einem Tag oder Monat stehen und das übergeordnete Quartal ansprechen wollen.
Schauen wir einmal auf eine Anwendung.
Alle Perioden des Jahres auf dem Level der ausgewählten Periode
Stellen wir uns einmal vor, dass wir im Berichtsfilter entweder einen Monat oder ein Quartal einstellen. Die Grafische Tabelle soll im ersten Fall alle Monate des aktuellen Jahres und im zweiten Fall alle Quartale des Jahres auf der Spaltenachse haben.
Wir nutzen nun die Tatsache, dass wir die Nummer des aktuellen Levels des Elements im Berichtsfilter mit “current.level” auslesen können. Weitere Beispiele hierzu gibt es auch im Beitrag Startelemente für Sparklines.
Mit dem benutzerdefinierten Ausdruck “Current.year.descendants(current.level)” passiert Folgendes:
Die Perioden des Jahres auf dem gleichen Level sind sichtbar
Steht man auf dem Quartal, erhält man nun alle Quartale des Jahres, bei einem Monat sind es alle 12 Monate. Da in diesem Beispiel ab Oktober noch keine Daten vorliegen und wir leere Spalten ausgeblendet haben, sehen wir hier faktisch dann doch nicht alle Monate bzw. Quartale.
Menge PeriodsToDate
Möchte man nur die Monate bis zum laufenden, eingestellten Monat sehen, bietet sich der Mengenbefehl PeriodsToDate(n) an. Über das Level n kann gesteuert werden, ob die Monate beginnend mit dem ersten Monat im Datensatz (n = 0), mit dem ersten Monat des aktuellen Jahres (n = 1) oder mit dem ersten Monat des aktuellen Quartals (n = 2) gezeigt werden sollen.
Für den Mai 2021 ergibt sich beispielsweise mit Current.PeriodsToDate(1) als benutzerdefinierter Ausdruck in der Achse das folgende Bild:
Die Monate des aktuellen Jahres bis zum eingestellten Monat
Das eingestellte Level darf nicht unterhalb der Ebene liegen, deren Element im Filter ausgewählt ist.
Gleichermaßen funktioniert der Befehl Current.PeriodsToDate(1) auch für Quartale:
Die Quartale des aktuellen Jahres bis zum eingestellten Quartal
Es gibt auch eigene Befehle pro Level, bei denen der Zahlenwert des Levels nicht bekannt sein muss und die Zuordnung aus den Ebenen-Einstellungen (Modus Modellieren, Reiter Logik) abgeleitet wird. In unserem Beispiel könnte anstelle Current.PeriodsToDate(1) auch Current.YearToDate oder abkürzend Current.Ytd verwendet werden.
Monate seit Quartalsbeginn (mit einem Monat im Berichtsfilter) werden mit QuarterToDate (kurz Qtd) erzeugt.
Liegt ein Modell mit Tagen vor und ist ein Tag auch im Filter als Periode ausgewählt, könnten Tage seit Monatsanfang mit MonthToDate (kurz Mtd) und Tage seit Wochenbeginn mit WeekToDate (kurz Wtd) generiert werden.
Nutzen wir solche benutzerdefinierten Ausdrücke in einem Zeitanalyseelement, wird wieder ein Wert über die ausgewählten Perioden zugewiesen.
Benutzerdefinierte Ausdrücke, die aus Mengen wieder auf Elemente schließen
Wir hatten bereits erwähnt, dass wir mit FirstChild und LastChild zum ersten bzw. letzten direkten Nachfolger eines Elements wechseln können.
Hier gibt es nun mit dem neuen Release 6.4.2.1 eine weitere Möglichkeit, aus der Menge der Nachfolger das erste oder das letzte Element zu bestimmen. Im Beitrag Startelemente für Sparklines wollten wir von einem eingestellten Quartal oder Monat auf den Januar des aktuellen Jahres verweisen. Der passende benutzerdefinierte Ausdruck lautete Year.FirstChild.FirstChild.
Hier haben wir nun eine Alternative, da es einen neuen Befehl “First()” gibt, den wir auf Mengen anwenden können, etwa auf Year.Descendants(3), sodass der vollständige Ausdruck Year.Descendants(3).First lautet. Wir springen dabei, egal ob sich ein Jahr, Quartal oder Monat im Berichtsfilter befindet, zunächst zum zugehörigen Jahr. Dann ermitteln wir die 12 Monate als Nachfolger dieses Jahres auf der Ebene 3 der Monate. Hiervon wird das erste Element genommen, also der Januar:
Erster und letzter Monat des aktuellen Jahres
Analog können wir durch Anfügen von “last” zum letzten Element der Menge springen.
Ausgehend von diesem Element könnten wir wieder eine Menge erzeugen. Somit haben wir den bisher zwingend vorgegebenen Aufbau “Element.Menge.Auswertungsvorschrift” eines Zeitanalyseelements gelockert, da nach Menge nun doch noch einmal ein Element (First oder Last) folgen darf und wir sozusagen von vorne neu starten.
Normalerweise gibt es immer mehrere Wege, die letztendlich gewünschte Menge oder das gewünschte Element zu generieren. Das erste Quartal des zugehörigen Jahres zur ausgewählten Periode (in unserem Modell ein Monat, Quartal oder Jahr) könnten wir mit “Year.children.first”, mit “Year.quarters.first” oder auch mit “Year.descendants(2).first” ansprechen.
Darstellung eines zusammenhängenden Zeitraums
Sind wir an einer Menge von aufeinanderfolgenden Perioden interessiert, so gibt es eine einfache Lösung. Wollen wir etwa immer die mittleren vier Monate von Mai bis August des aktuellen Jahres sehen, so passt der Ausdruck Year.Descendants(3).Last.lag(4).range(4) – vom Dezember aus gehen wir dabei mit lag(4) zum August und range(4) erzeugt dann die Monate von Mai bis August:
Herausschneiden aufeinanderfolgender Monate
Hier könnte auch ein Quartal oder das Jahr selbst im Berichtsfilter stehen.
Ausblick
Für die Nutzung als Achsendefinition reicht ein benutzerdefinierter Ausdruck, der eine Menge von Perioden erzeugt. Für die Verwendung als Zeitanalyseelement könne wir diese Menge direkt angeben, wobei sich die Aggregationsvorschrift aus den Eigenschaften des Analysewerts ergibt, bei additiven Werten also normalerweise die Summe.
Die Aggregationsvorschrift kann auch durch Anfügen von Sum, Avg, Min, Max, Stddev (Standardabweichung) und Var explizit gesetzt werden. Darüber hinaus können wir auch die durchschnittliche Steigung über die Elemente der Menge – implizit wird eine Regression verwendet! – durch Anhängen von LinregSlope berechnen.
Da wir Zeitanalyseelemente und auch die benutzerdefinierten Ausdrücke wiederum in berechneten Elementen einsetzen dürfen, ergeben sich hier auch Möglichkeiten für komplexere Berechnungen.
Diesen weiterführenden Berechnungen möchte ich aber lieber einen eigenen Beitrag widmen.
