Bilanzierung für aggregierte Beträge in Pivottabelle

[SebastianSchenk]

Liebe Community, ich wünsche euch allen ein gutes neues Jahr.

Folgende Fragestellung begleitet mich seit Tagen und ich hoffe auf jemanden, der mir helfen kann.

In einer Pivottabelle wird die Kennzahl Betragbc dargestellt über die Monate hinweg, die in den Spalten aufgeklappt werden können. Betragbc sind allerdings Beleg-Werte und können positiv wie negativ sein und sollten bis zu dem jeweiligen Datum über alle auch nicht aufgelisteten Monate hinweg aufsummiert werden. So ergäbe sich die Bilanzierung. Das ist der IST-Stand:

Die Darstellung mit RangeSum( Before (TOTAL Sum(Betragbc), 0, ColumnNo(TOTAL))) ergibt genau das, was ich sehen will. Aber!

Allerdings kommt Müll raus, sobald ich einen Monat auswähle. Denn ich will ja nicht nur die Spalten summieren, sondern alle Belege in den jeweiligem Kontext Mandant und Sachkontonummer bis zu dem Ende des jeweiligen Monats.

Jetzt experimentiere ich mit SetAnalysis und verzweifle. Kann mir jemand bitte helfen? Geht das überhaupt so?

Ich habe gedacht, mit TOTAL komme ich an alle Belege ran, aber ich bekomme sie mit SetAnalysis nicht eingeschränkt, liegt vielleicht am TOTAL selbst?

Folgendes habe ich ausprobiert:

Aggr(
Sum(
{<YearMonth = {"<= $(max(YearMonth))"} >}
TOTAL Betragbc)
, Mandant
, Sachkontonummer
, YearMonth
)

[marcus_sommer]

Sowas ist nicht über eine klassische Set Analysis möglich, da man hiermit nicht kumulieren kann. Kumulationen gehen nur mit Interrekord-Funktionen, die in Range-Funktionen eingebettet sind - also genau das, was du schon hast.

Um jetzt zu erreichen, dass die Kumulation auch Zeiträume berücksichtigt, die von der Auswahl ausgeschlossen wurden, sind einige Zusatzschritte erforderlich, wie:

sum(aggr(
RangeSum( Before (TOTAL Sum({< Monat >} Betragbc), 0, ColumnNo(TOTAL))),
Dim1, Dim2)) * sign(count(Monat))

Das bedeutet, innerhalb der eigentlichen Aggregation, wird über eine Set Analysis die gesetzte Auswahl angepasst - hier mit der Feld-Nennung einfach ignoriert, aber man könnte hier auch explizite Werte einsetzen/generieren. Damit man hierzu aber auch den dimensionalen Kontext über die Zeiträume behält, bettet man die Kumulation in eine aggr() ein, wobei Dim1 und Dim2 jetzt nur Platzhalter für die richtigen Dimensionsfelder stehen.

Nur die Auswahlen per Set Analysis zu überschreiben und mit/ohne aggr() würde bedeuten, das weiterhin alle Zeiträume angezeigt werden, als ob keine Auswahl getroffen wäre. Um jetzt das Objekt doch auf die Auswahl zu begrenzen wird anschließend ein boolsche Prüfung gegen alle Einzel-Ergebnisse angewendet - hier per Multiplikation von 1, wenn es die Monate gibt und mit 0, falls nicht.

Insbesondere in komplexeren Objekten mit multiplen vertikalen + horizontalen Dimensionen und/oder wenn weitere Bedingungen/Auswahlen/Besonderheiten dazu kommen, ist sowas nicht unbedingt trivial. Hier empfiehlt es sich, mit einem reduzierten Objekt (nur eine Dimension und eine Formel sowie keine weiteren Bedingungen) zu starten und dann Schritt für Schritt zu erweitern.

Alternativ sollte man schauen, ob man die Kumulierungen nicht als zusätzliches Feld bereits im Skript mit umsetzen kann.

Denkbar wäre ansonsten auch, die Kumulierungen über The As-Of Table - Qlik Community - 1466130 abzubilden.

[SebastianSchenk]

Lieber Markus,

ich danke dir ganz herzlich für deine ausführliche und professionelle Antwort. Du bist schon ein Jahrzehnt (?) dabei und eine große Bereicherung für die Community.

Theoretisch kann ich deinen Ansatz nachvollziehen, praktisch leider noch nicht. Ich habe uns ein Beispiel gemacht und deine Formel implementiert.

Kumulierung im Diagramm oben:

* Was ich sehe: Akkumulation über die Monate hinweg egal was ich auswähle, das ist schonmal ein guter Anfang mit

RangeSum(Before( TOTAL Sum( {<Month>} Betrag), 0, ColumnNo(TOTAL)))

Jetzt gehts weiter im Diagramm unten Kumulierung im Diagramm, ich füge noch das Quartal als dritte Zeitdimension hinzu und bekomme Nullen mit derselben Formel sobald ich ein Quartal einklappe. Im Grunde möchte ich hier den letzten Stand sehen, das wäre schon super. 

Bei Ummünzung auf deinen Vorschlag bekomme ich leider insgesamt nur Nullen.  Probiert habe ich:

sum(aggr(
RangeSum(Before( TOTAL Sum( {<Year, Quarter, Month>} Betrag), 0, ColumnNo(TOTAL)))
,Typ, Year, Quarter, Month)) * sign(count(Month))

Meine Interimslösung mit zyklischen Gruppen anstelle Aufklappen der Pivot-Tabelle hat mir zunächst aus der Patsche geholfen.

[marcus_sommer · Accepted solution]

Ich muss gestehen, dass ich nicht wirklich viel praktische Erfahrungen mit Kumulierungen in der Oberfläche habe, insbesondere nicht in komplexeren Objekten mit multiplen Dimensionen, die dann auch aggr() Konstruktionen erfordern. Zum Einen, da wir nur sehr wenig Anforderungen in diese Richtung haben und zum Anderen, da sowohl UI-Kumulationen als auch aggr() sehr performance-lastig sind und das bei unseren Datasets sowie den verfügbaren Ressourcen eher nicht zu (noch) akzeptablen Reaktionszeiten führt.

Insofern versuche ich sowas zu vermeiden, insbesondere durch einfachere Objekte und/oder Dimensionsgruppen und/oder der Zusammenfassung von Dimensionen im Skript sowie der Aufteilung in mehrere parallele Objekte, häufig auch in Sammelboxen. Fast immer wird man über diesen Weg alle benötigten Sichten abbilden können, auch wenn es vielleicht nicht dem ursprünglichen (Excel) Layout entspricht. Wichtiger sind die Informationen selbst und komplexere Layouts sind häufig kontraproduktiv, da sie langsamer/schwieriger zu lesen/interpretieren sind.

Mein obiges aggr() Beispiel für die Kumulierung über mehrere Dimensionen, stammt aus einem größeren Forecasting-Projekt, welches jedoch nicht mit vertikalen und horizontalen Dimensionen gearbeitet hat, sondern nur mit vertikalen Dimensionen. Und hier kann ich mir auch die besondere Herausforderung vorstellen, denn eine einzelne Kumulierung kann nur vertikal oder horizontal verlaufen, was sich prinzipiell erstmal mit vertikalen und horizontalen Dimensionen in der aggr() in die Quere kommt. Denkbar wäre, die Kumulierungen und/oder die aggr() weiter zu verschachteln, aber die Komplexität und die Performance-Anforderungen steigen hier tendenziell nicht linear, sondern eher exponentiell. Ich persönlich würde diesen Weg nicht so gehen, u.a. da unser Forecasting-Projekt an einer zu hohen Komplexität fehlgeschlagen ist - grundsätzlich funktionierend und auch mit leicht besseren Ergebnissen als der bestehende Forecasting-Prozess, aber nicht mehr produktiv händelbar (es komplett neu aufzusetzen, war dann nicht mehr gewünscht).

Insofern mein Vorschlag, halte es so einfach wie möglich und falls doch komplexere Logiken benötigt werden, dann schaue mal in den obigen Link zu den As-Of-Tables, denn auch damit sollten Kumulierungen möglich sein.

[SebastianSchenk]

Hallo Markus.

Deiner Einschätzung folge ich gern. Am Anfang schon zuviel Komplexität zu haben ist nie eine gute Idee, wie du schon schreibst. Herzlichen Dank nochmal, dass du mir und uns deine Zeit geschenkt hast.