Liebe Datenanalysten,
in der Juni-Ausgabe der clicks!, rügten einige Leser, haben wir uns schmählich über Prozentzahlen geäußert: Sie zu vergleichen, unkten wir da, sei in manchen Fällen schlicht großer Käse. Das wollen wir hier relativieren, wie es dem Naturell von Prozentzahlen entspricht, und etwas zu ihrer Ehrenrettung beitragen. Kritisch mag man die relativen Größen wohl sehen, wenn die absoluten Bezugswerte unterschiedlich, unbekannt oder ungenannt sind. Von großem Nutzen sind sie aber genau dann, wenn sie uns dabei helfen, die Bedeutung einzelner Objekte im Vergleich zu einer Gesamtheit zu beurteilen. Welchen Anteil beispielsweise haben einzelne Kunden oder Produkte am Gesamtumsatz? Solche und andere Fragen können Sie in DeltaMaster rasch beantworten. Und wie so oft steht Ihnen eine Reihe von Lösungswegen dafür offen. Welche das sind, das möchten wir in den vorliegenden clicks! erörtern.
Herzliche Grüße
Ihr Team von Bissantz & Company
Wer wissen möchte, welchen Anteil beispielsweise der Umsatz mit einem Kunden am Umsatz aller Kunden ausmacht, dem legt die Mathematik keine großen Steine in den Weg: Man dividiert einfach den Einzelumsatz durch den Gesamtumsatz und schon hat man die gewünschte Prozentzahl. Einzig den Divisor müssen wir im Blick behalten: Er darf nicht null sein, denn das Teilen durch null ist nicht definiert und deshalb „verboten“. In vielen Reports weist man die Anteile direkt neben den absoluten Größen aus, sodass schnell zu erkennen ist, um welche Beträge es geht und welche Bedeutung sie in Bezug auf die Gesamtheit haben.
In DeltaMaster lassen sich solche Anteilsbetrachtungen auf verschiedene Art und Weise lösen:
- über Analysewerte,
- über berechnete Elemente,
- in der Rangfolge, quasi als Nebenprodukt,
- als Formel im Flexreport und
- im Flexreport über Darstellungsoptionen in der Pivottabelle.
Wir möchten Ihnen die Varianten im Überblick vorstellen und damit eine Entscheidungshilfe geben, welches Vorgehen sich in welchem Zusammenhang besonders empfiehlt. Als Beispiel dient uns stets die Frage, welchen Umsatzanteil einzelne Kunden bzw. Kundengruppen erreichen; selbstverständlich könnten aber auch andere additive Kennzahlen und andere Dimensionen so untersucht werden.
Analysewerte für Summe und Anteile: Filter- und Quotientenwert
Eine Möglichkeit ist, die Division in einem Analysewert zu hinterlegen, der den Einzelumsatz durch den Gesamtumsatz teilt. In einem ersten Schritt gilt es somit, den Gesamtumsatz über alle Kunden zu bestimmen.
Dazu legen wir einen neuen Analysewert an (Menü Modell oder Analysewertbrowser, Menü Ich möchte), und zwar vom Typ Filter. In der Kunden-Dimension fixieren wir den Umsatz auf das Top-Element, auf alle Kunden; in unserer Referenzanwendung „Chair“ heißt das „Europa“. (Den Unterschied zwischen variablen, identischen und fixierten Filtern haben wir in den DeltaMaster clicks! 03/2008 beschrieben; hier benötigen wir einen fixierten.) Die so definierte Kennzahl liefert für jede Sicht immer die Summe der Umsätze über alle Kunden, unabhängig davon, welche Kunden etwa im Fenster Meine Sicht ausgewählt oder in einer Pivottabelle dargestellt sind. Die Auswahl in den anderen Dimensionen, zum Beispiel eine Einschränkung auf eine Produktgruppe oder einen bestimmten Zeitraum, wird jedoch weiterhin berücksichtigt.
Im zweiten Schritt legen wir einen weiteren neuen Analysewert an, dieses Mal einen Quotientenwert – das ist genau der Anteil, um den es uns ging. Dass keine Division durch null passieren kann, stellt DeltaMaster sicher: In der MDX-Definition, die der Assistent erzeugt, wird automatisch eine „IIf()“-Klausel eingefügt, die diesen Fall abfängt.
Auf diese Weise lassen sich der Umsatz und der Umsatzanteil für jeden Kunden übersichtlich in einer Pivottabelle darstellen. Die dritte Spalte mit dem Gesamtumsatz würde man in einem „echten“ Bericht natürlich nicht anzeigen; sie soll lediglich die Wirkung des fixierten Filters verdeutlichen.
Die Berechnung, die wir hier mit zwei Kennzahlen umgesetzt haben, könnte man mithilfe von MDX auch in einem benutzerdefinierten Analysewert zusammenfassen und auf die Summe als Hilfskennzahl verzichten.
Dieser Weg führt schnell ans Ziel und hat den Vorteil, dass die angelegten Analysewerte auch für andere Zwecke zur Verfügung stehen, zum Beispiel für eine Zeitreihenanalyse der Umsatzanteile.
Berechnete Elemente
Die oben beschriebene Vorgehensweise liefert den Anteil in Bezug auf einen bestimmten Analysewert. Sollen neben dem Umsatz etwa auch der Absatz, die Materialkosten und die Herstellkosten anteilsmäßig untersucht werden, könnte man weitere, spezialisierte Kennzahlen anlegen. Schneller und einfacher ist es dann aber, die Anteilsberechnung nicht als Analysewert, sondern in Form eines berechneten Elements abzubilden. Ein solches kann DeltaMaster mit jeder beliebigen Kennzahl kombinieren und so den Anteil an der Summe bestimmen, ähnlich wie sich etwa auch eine Plan-Ist-Abweichung für alle Messwerte bestimmen lässt und nicht für jeden erneut definiert werden muss.
Idealerweise legen Sie in der OLAP-Datenbank eine eigene Hilfsdimension an, zum Beispiel „Anteilsdarstellung“. An dieser Achse kann DeltaMaster dann die berechneten Elemente anordnen. Hilfsweise oder zum Ausprobieren können Sie auch eine bestehende Dimension verwenden, etwa „Darstellung“ oder „Wertarten“. Über den Dimensionsbrowser fügen Sie dann ein berechnetes Element hinzu, ein benutzerdefiniertes.
Zum gewünschten Ergebnis führt dieser MDX-Ausdruck:
([Kunden].CurrentMember, [Wertarten].[Ist]) / ([Kunden].[Europa], [Wertarten].[Ist])
Er liest für alle jeweils relevanten Analysewerte die Ausprägungen für die aktuell ausgewählten Elemente der Kundendimension („[Kunden].CurrentMember“) ab und teilt sie durch die Werte für das Top-Element („[Kunden].[Europa]“). Die Operanden werden als Tupel angegeben, zusammen mit einem Referenzelement. Im abgebildeten Beispiel haben wir dafür das Element „[Wertarten].[Ist]“ missbraucht, das wir auch für Plan-Ist-Abweichungen verwenden würden. Wir können deshalb die Anteilsbetrachtungen nicht mit Plan-Ist-Abweichungen kombinieren. Aus diesem Grund empfiehlt es sich, eine eigene Dimension für solche Elemente im Datenmodell einzurichten, wenn man es oft mit Anteilsberechnungen zu tun hat.
In Pivottabellen, die die Anteile auf diese Weise bestimmen, können Sie ohne weiteres Zutun beliebige Kennzahlen untersuchen; die Berechnung ist jetzt unabhängig von den Analysewerten. Lediglich der Bezug zur Dimension muss gegeben sein, damit DeltaMaster erkennen kann, wovon der Anteil zu errechnen ist.
Der guten Ordnung halber sollten wir den MDX-Ausdruck noch vor der berüchtigten Division durch null schützen. Das sieht dann so aus:
IIf( ([Kunden].[Europa], [Wertarten].[Ist]) = 0, Null, ([Kunden].CurrentMember, [Wertarten].[Ist]) / ([Kunden].[Europa], [Wertarten].[Ist]) )
Eine interessante Variante ist, den Anteil am jeweils nächsthöheren Element zu berechnen, also beispielsweise die Bedeutung, die ein Kunde in seiner Region hat, sowie den Anteil, den die Region am Gesamtmarkt hat. Dazu genügt eine kleine Modifikation des Ausdrucks:
([Kunden].CurrentMember, [Wertarten].[Ist]) / ([Kunden].CurrentMember.Parent, [Wertarten].[Ist])
Dem nebenstehenden Umsatzbericht ist zu entnehmen: Das Gebiet Nord 2 macht etwa 60 % an der Region Nord aus und die Region Nord macht 30 % am Umsatz in ganz Deutschland aus.
In dem genannten Ausdruck ist das oberste Element gesondert zu behandeln, da dieses naturgemäß keinen „Parent“ hat. Besser schreiben wir also:
([Kunden].CurrentMember, [Wertarten].[Ist]) / IIf([Kunden].CurrentMember.Level.Ordinal > 0, ([Kunden].CurrentMember.Parent, [Wertarten].[Ist]), ([Kunden].CurrentMember, [Wertarten].[Ist]))
Auch hier lässt sich noch eine Null-Prüfung einbauen.
Die berechneten Elemente bieten die größtmögliche Flexibilität, was die Verwendung von Anteilen in Berichten und Analysen angeht.
Anteilsspalte in der Rangfolge
In einigen Fällen bietet sich auch das Analyseverfahren Rangfolge an: Dessen Ergebnisse werden standardmäßig mit einer Anteilsspalte ausgegeben. Die Ermittlung des Anteils geschieht dann vollautomatisch und Sie müssen nichts weiter tun. Diese Variante ist besonders dann geeignet, wenn die Summe nicht ausgewiesen werden muss bzw. an anderer Stelle, etwa in einem Kombinationscockpit, erkennbar ist.
Berechnung als Formel im Flexreport
Mit den Formeln im Flexreport können Sie Anteile berechnen, wie man es in einer Tabellenkalkulation tun würde. Zweckmäßigerweise blenden Sie dazu zunächst die Zellkoordinaten ein (Kontextmenü, Menü Ich möchte), um die Adressen der Zellen, die ins Verhältnis gesetzt werden sollen, leichter erkennen zu können. Dann bearbeiten Sie die Zelleigenschaften (Kontextmenü) der Zelle, in der der Anteilswert ausgegeben werden soll: Auf der Registerkarte Inhalt wählen Sie den Typ Formel aus und auf der Registerkarte Formel geben Sie die Division ein.
In der nebenstehenden Abbildung haben wir mit relativen und absoluten Referenzierungen gearbeitet. Der erste Teil des Ausdrucks „=R(0)C(?1)/R3C2“ wird wegen der Klammern relativ interpretiert: Angesprochen ist die Zelle, die in derselben Zeile und links neben der aktuellen Spalte liegt („R(0)“ = Zeile (Row) mit einem Abstand von 0, „C(?1)“ = Spalte (Column) mit einem Abstand von ?1). Im zweiten Teil („R3C2“) kommen keine Klammern vor: Er wird als absolute Koordinatenangabe verstanden. Wenn Sie den Zellinhalt so definieren, genügt es, die Formel nur in einer einzigen Zelle einer Spalte explizit anzugeben; in die restlichen Zellen der Anteilsspalte können Sie die Formel einfach kopieren: erste Zelle markieren, Tastenkombination Strg+C drücken, die übrigen Zellen markieren, Strg+V drücken. Da wir die Summenzelle absolut referenziert haben, bleibt der Verweis in jeder per „Copy & Paste“ befüllten Zelle erhalten, während die Einzelwerte jeweils aus der relativ referenzierten linken Nachbarzelle ausgelesen werden.
Die Null als Teiler lässt sich hier am einfachsten mit einer bedingten Formatierung abfangen: Wenn der Wert der Summenzelle null ist, soll der Inhalt der jeweiligen Zelle zum Beispiel ein leerer Text sein.
Flexreport mit Zellreferenzen auf eine Pivottabelle in Anteilsdarstellung
Seit der Version DeltaMaster 5.3.5 gibt es noch eine weitere Möglichkeit, Anteilsspalten (oder ?zeilen) in Flexreports zu erzeugen (siehe DeltaMaster deltas! 5.3.5, Nr. 8). Dazu nutzt man die Option Darstellung von Pivottabellen, mit der Sie die gesamte Tabelle umschalten können zwischen den eigentlichen Werten (den Ergebnissen der Datenbankabfrage) oder eben auch einer prozentualen Darstellung des jeweiligen Anteils an der Zeilen-, Spalten? oder Tabellensumme. Wenn Sie eine Pivottabelle mit prozentualer Darstellung speichern und aus einem Flexreport heraus Zellreferenzen darauf legen, berücksichtigt DeltaMaster die Darstellung auch im Flexreport.
Zu beachten ist, dass DeltaMaster zur Berechnung der Anteile stets alle Werte der Zeile, Spalte oder Tabelle heranzieht – auch eventuelle Aggregate, wie die Summe oder Zwischensummen. Die zu untersuchenden Elemente sollten also alle auf derselben Ebene stehen und es sollten keine Zwischensummen oder Ähnliches vorkommen, sonst erhalten wir zwar korrekte, auf den ersten Blick aber irritierende Resultate. Mit einem Drill-Across im Kontextmenü der Zeilen- oder Spaltenüberschriften ist das schnell bewerkstelligt; alternativ stellen Sie die gewünschte Ebene in der Achsendefinition per Ebenenauswahl ein.
So haben wir zwei Pivottabellen vorbereitet, jetzt auf Ebene der Kundenregionen: eine mit den absoluten Werten (Standardansicht), die andere dank der Darstellungsoption mit den prozentualen. In letztere haben wir über das Kontextmenü zusätzlich die sogenannten 100-Prozent-Balken eingeblendet, die die Verhältnisse grafisch darstellen (siehe DeltaMaster deltas! 5.3.1, Nr. 25).
Diese beiden Cockpits führen wir jetzt in einem Flexreport zusammen. Wir beginnen mit der Anteilsdarstellung und wandeln sie in einen Flexreport mit Zellreferenzen um (Menü Ändern im Fenster Mein Cockpit). Damit sind die Prozentwerte und die Balken bereits in den Bericht übernommen, es fehlen noch die absoluten Ergebnisse. Die bekommen wir, indem wir weitere Spalten einfügen und in diese wiederum Referenzen einfügen auf das Ergebnis-Cockpit. Beide Optionen finden sich im Kontextmenü des Flexreports. Nach ein paar Umformatierungen entsteht der nebenstehende Bericht, in dem die Anteile neben den Ergebnissen aufgeführt werden.
