Werte in QlikView und Qlik Sense manuell kumulieren

designer qlik sense qlikview
Werte manuell kumulieren

Moin!

In diesem Blog zeige ich Dir, wie Du Werte in QlikView und Qlik Sense akkumulieren kannst, also fortlaufend summieren.

Dabei gibt es eine kleine Stolperfalle in QlikView, die Du kennenlernen wirst und natürlich auch, wie Du trotzdem zum richtigen Ergebnis kommst.

Das ganze zeige ich Dir einmal in QlikView und danach auch für Qlik Sense, weil die Berechnung grundsätzlich in beiden Programmen fast identisch ist.


Eine kumulierte Darstellung von Werten ist nicht unüblich und in diesem Blog stelle ich die Umsätze Actual und Budget gegenüber.

Also die Ist-Umsätze und die Plan-Umsätze.

Das ganze für das Jahr 2019 - also aktuell das letzte abgeschlossene Jahr.


Umsatzabweichung in QlikView

Fangen wir ganz simpel in QlikView an. Hier eine Gegenüberstellung von Umsatz und Budget pro Monat für das Jahr 2019:


An dieser Stelle ist noch nichts kumuliert, stattdessen wird hier in einem simplen Tabellendiagramm der Umsatz und das Budget mit der Funktion Sum() aggregiert.

Formel für den Umsatz: Sum(Umsatz)

Formel für das Budget: Sum(Budget)


Außerdem wird in der Spalte Diff. die Differenz mit der Column() Funktion berechnet:

Formel für die Abweichung: Column(1) / Column(2)


Umsatzabweichung in Qlik Sense

Das gleiche habe ich in einer Tabelle in einer Qlik Sense App dargestellt:

Hier habe ich exakt das gleiche Datenmodell und auch exakt die gleichen Formeln zur Berechnung der Kennzahlen verwendet.


Werte in QlikView kumulieren (Standard-Funktion)

Jetzt kumuliere ich die Werte in den Spalten Umsatz und Budget mit der Standard-Funktion in QlikView.

Dafür klicke ich unter Akkumulation auf Vollständige Akkumulation (in beiden Formeln):

Das gleiche nochmal für die Formel Budget:


Durch die Akkumulation haben sich die Werte in den Spalten Umsatz und Budget geändert, aber die Spalte Diff. liefert immer noch die gleichen Werte, als wenn die Akkumulation nicht stattfinden würde:


Hier scheint QlikView in der Berechnung der Formel Diff. die Akkumulation der Spalten Umsatz und Budget komplett zu ignorieren.


Werte in Qlik Sense kumulieren (Standard-Funktion)

Machen wir das gleiche in Qlik Sense.

Auch hier gibt es seit kurzem die Möglichkeit, die Werte einer Kennzahl mit einer Standard-Funktion zu kumulieren.

Dafür klappst Du die Eigenschaften der Kennzahl auf und klickst auf Modifikator:


Wähle im Dropdown: Akkumulierung und im Dropdown Bereich: Vollständig.


Das gleiche machst Du für die Kennzahl Budget.


In Qlik Sense rechnet die Spalte Abw. richtig:

Die Akkumulation der beiden Kennzahlen Umsatz und Budget werden also in Qlik Sense korrekt berücksichtigt, wenn die Kennzahl Abw. berechnet wird.


Das Problem mit der kumulierten Abweichung besteht also nur in QlikView.


Kumulierte Werte in QlikView mit RangeSum() berechnen

Damit die Abweichung auch bei den kumulierten Werten in QlikView richtig berechnet wird, musst Du die Kumulation manuell berechnen - also ohne die Standard-Funktion.

Dafür schaltest Du zuerst die vollständige Akkumulation für die Formeln Umsatz und Budget ab.

Wähle dafür unter Akkumulation (für beide Formeln): Keine Akkumulation.


Jetzt werden die beiden Formeln angepasst.

Fangen wir mit dem Umsatz an:

RangeSum(Above(Sum(Umsatz), 0, RowNo()))


Das gleiche machst Du für die Formel Budget:

RangeSum(Above(Sum(Budget), 0, RowNo()))


Das Ergebnis der Akkumulation ist das gleiche, aber jetzt kann die Abweichung auch auf die Akkumulation zugreifen und in die Berechnung mit einbeziehen:


Geht das auch in Qlik Sense?

Ja, Du kannst mit exakt der gleichen Formel die Akkumulation auch in Qlik Sense manuell berechnen.

Aber da Qlik Sense auch mit der Standard-Funktion die vollständige Akkumulation korrekt berechnet, wirst Du das selten brauchen.


Was passiert da in der manuellen Berechnung?

Gehen wir nochmal einen Schritt zurück und betrachten wir die Formel, mit der die Werte manuell kumuliert werden können:

RangeSum(Above(Sum(Umsatz), 0, RowNo()))


Hier werden gleich 3 weitere Funktionen in Kombination verwendet:

  • RangeSum()
  • Above()
  • RowNo()

Schauen wir uns kurz an, was diese Funktionen hier genau machen.


Werte mit RangeSum() summieren

Die Funktion RangeSum() liefert die Summe aller Werte in einem definierten Bereich innerhalb Deines Diagramms.

Dabei kannst Du entscheiden, von wo die Werte genau genommen werden sollen - ausgehend von der Zelle, in der gerade der Wert berechnet wird:

  • Von darüber mit der Above() Funktion.
  • Von darunter mit der Below() Funktion.
  • Von links mit der Before() Funktion.
  • Von rechts mit der After() Funktion.

Es gibt noch ein paar mehr Funktionen, die Du in Kombination mit RangeSum() verwenden kannst, aber diese vier werden am häufigsten benutzt.


Wir verwenden hier die Above() Funktion, weil die darüber stehenden Werte summiert werden sollen.

Dafür ist es wichtig, dass unsere Tabelle aufsteigend nach Monaten sortiert ist, damit die darüber stehenden Werte auch aus den richtigen Vormonaten kommen.

Für Januar wird nichts summiert.

Für Februar werden die Werte aus Februar und der darüber stehende Wert aus Januar summiert. Dafür sorgt die Above() Funktion.

Für März werden die Werte aus März und die darüber stehenden Werte aus Januar und Februar summiert.

So geht es dann für jeden Monat weiter.


Die Above() Funktion

Wie Du gerade erfahren hast, liefert die Above() Funktion in einem Diagramm die darüber liegenden Werte.

Wenn QlikView den Februar berechnet, kommt der davor liegende Wert aus Januar. Vorausgesetzt, die Tabelle ist nach Monaten aufsteigend sortiert.


Wenn es mehrere Werte vor dem, der gerade berechneten Zelle gibt, müssen wir QlikView (und genauso auch Qlik Sense) angeben, welche Werte genau geliefert werden sollen.

Die genaue Syntax der Funktion Above() findest Du in der QlikView Online Hilfe.

Above(Formel, Offset, Anzahl)


Unsere Formel ist die Summe der Werte aus dem Feld Umsatz: Sum(Umsatz).


Als Offset verwenden wir 0 - das bedeutet, das QlikView die Werte ab der ersten Zeile liefern soll.

  • 0 = Ab der ersten Zeile.
  • 1 = Ab der zweiten Zeile.
  • usw.

Die Anzahl gibt an, wie viele der Werte geliefert werden sollen:

  • 1 = Ein Wert.
  • 2 = Zwei Werte.
  • usw.

Wenn ich in der dritten Zeile der Tabelle bin, also beim Monat März, soll die Above() Funktion 3 Werte liefern: Januar, Februar und März.

Also den Wert des aktuellen Monats (März) und die zwei darüber angezeigten Werte (Januar und Februar).

Für eine vollständige Akkumulation brauchen wir also immer alle Werte (aufsteigend bis zum jeweiligen Monat) und da kommt die Funktion RowNo() ins Spiel.


RowNo() liefert die aktuelle Zeilennummer.

Wenn wir in der dritten Zeile sind, also beim Monat März, liefert sie 3. So bekommen wir in jeder Zeile eine vollständige Akkumulation.


Wie rechnet also RangeSum() in unserer Tabelle?

Da wir nie genau wissen, wie viele Zeilen unsere Tabelle je nach Selektion haben wird, verwenden wir eine Kombination mehrerer Funktionen, damit die vollständige Akkumulation immer korrekt berechnet wird.

Hier nochmal unsere Formel:

RangeSum(Above(Sum(Umsatz), 0, RowNo()))


QlikView und Qlik Sense lösen Formeln ja immer von innen nach außen auf - wie es die mathematischen Gesetze vorschreiben.

Nehmen wir an, QlikView berechnet gerade den März 2019 und die Tabelle ist nach Monaten aufsteigend sortiert:


QlikView rechnet von innen nach außen - also zuerst: Sum(Umsatz).

Dann kommt die Above() Funktion mit zwei Parametern:

  • Offset = 0.
    • Liefere alle Werte darüber ab der ersten Zeile (0 steht für die erste Zeile).
  • Anzahl = RowNo().
    • RowNo() liefert die Zeilennummer - also für den März den Wert 3, weil der März in der dritten Zeile steht.

Zusätzlich zum Umsatz für den März 2019, wird also der Umsatz der zwei Zeilen darüber geliefert und mit der RangeSum() Funktion zum März-Umsatz hinzu addiert.


Fazit

Wenn Du einfach nur Werte in einer vollständigen Akkumulation anzeigen möchtest, funktioniert das mit den Standard-Funktionen in QlikView und Qlik Sense vollkommen korrekt.

Wenn Du aber weitere Berechnungen auf Basis dieser (mit der Standard-Funktion) kumulierten Werte machen möchtest, funktioniert das in QlikView leider nicht. In Qlik Sense dagegen völlig problemlos.


Es gibt aber in QlikView einen Workaround, der einwandfrei funktioniert:

Du schaltest die Standard-Funktion für die vollständige Akkumulation ab und kumulierst die Werte stattdessen mit der RangeSum() Funktion in Kombination mit der Above() Funktion.

Das Ergebnis ist genau das gleiche.