Zu Hauptinhalt springen

Verschachtelte Aggregierung

In einer Diagrammformel muss jeder Feldname von genau einer Aggregierungsfunktion eingeschlossen sein. Wenn Sie Aggregierungen verschachteln müssen, können Sie Aggr() verwenden, um eine zweite Aggregierungsebene hinzuzufügen. Aggr() enthält eine Aggregierungsfunktion als Argument.

Immer eine Aggregieruingsebene in einer Funktion

Eine typische App setzt sich beispielsweise wie folgt zusammen:

  • eine Million Datensätze in den Daten
  • hundert Zeilen in einer Pivottabelle
  • ein KPI in einem Messzeiger oder einem Textfeld

Obwohl sie in verschiedenen Größenordnungen vorliegen, können alle drei Zahlen dennoch alle Daten darstellen. Es handelt sich einfach um verschiedene Aggregierungsebenen.

Aggregierungsfunktionen verwenden die Werte vieler Datensätze als Eingabe und verdichten diese zu einem einzelnen Wert, der als Zusammenfassung aller Datensätze verstanden werden kann. Hierbei gilt eine Einschränkung: Sie können keine Aggregierungsfunktion innerhalb einer anderen Aggregierungsfunktion verwenden. Normalerweise muss jede Feldreferenz in genau einer Aggregierungsfunktion eingeschlossen sein.

Die folgenden Formeln funktionieren:

  • Sum(Sales)
  • Sum(Sales)/Count(Order Number)

Die folgende Formel funktioniert nicht, da es sich um eine verschachtelte Aggregierung handelt:

  • Count(Sum(Sales))

Die Lösung dieses Problems ist die Funktion Aggr(). Trotz ihres Namens handelt es sich nicht um eine Aggregierungsfunktion. Es ist eine „Viele-zu-viele“-Funktion ähnlich einer Matrix in der Mathematik. Damit wird eine Tabelle mit N Datensätzen in eine Tabelle mit M Datensätzen konvertiert. Sie gibt eine Reihe von Werten zurück. Die Funktion kann auch als virtuelles Tabellendiagramm mit einer Kennzahl und einer oder mehreren Dimensionen betrachtet werden.

Tipp: Verwenden Sie die Funktion Aggr() in berechneten Dimensionen, wenn Sie verschachtelte Diagrammaggregierungen auf mehreren Ebenen erstellen möchten.

Verwenden von Aggr() für verschachtelte Aggregierungen

Aggr() berechnet eine Reihe von Werten für die Formel, aggregiert über die angegebene Dimension bzw. Dimensionen. Beispielsweise den maximalen Umsatzwert pro Kunde oder Region. In erweiterten Aggregierungen ist die Funktion Aggr() in einer weiteren Aggregierungsfunktion eingeschlossen. Dazu wird die Reihe von Ergebnissen der Funktion Aggr() als Eingabe für die Aggregierung verwendet, in der sie verschachtelt ist.

Wenn die Aggr()-Anweisung verwendet wird, ergibt sie eine virtuelle Tabelle mit einer Formel, gruppiert nach einer oder mehreren Dimensionen. Das Ergebnis dieser virtuellen Tabelle kann dann anhand einer äußeren Aggregierungsfunktion weiter aggregiert werden.

Berechnen des höchsten durchschnittlichen Bestellwerts

Im folgenden Beispiel wird eine einfache Aggr()-Anweisung in einer Diagrammformel verwendet.

Wir möchten unsere Gesamtmetriken auf regionaler Ebene sehen, aber auch zwei komplexere Formeln zeigen können:

  • Größter durchschnittlicher Bestellwert nach Manager innerhalb jeder Region.
  • Manager, der für diesen höchsten durchschnittlichen Bestellwert verantwortlich ist.

Der durchschnittliche Bestellwert für jede Region lässt sich leicht anhand einer Standardformel Sum(Sales)/Count([Order Number]) berechnen.

In der App finden Sie auf dem Arbeitsblatt Nested Aggregations eine Tabelle mit dem Titel Aggr() function.

Gehen Sie folgendermaßen vor:

  1. Wählen Sie die verfügbare Tabelle Aggr() function aus.
    Das Eigenschaftsfenster öffnet sich.
  2. Klicken Sie auf Spalte hinzufügen und wählen Sie Kennzahl aus.
  3. Klicken Sie auf das Symbol 3.
    Der Formel-Editor wird geöffnet.
  4. Geben Sie Folgendes ein: Sum(Sales)/Count([Order Number])
  5. Klicken Sie auf Übernehmen.

Tabelle mit dem durchschnittlichen Bestellwert pro Region.

Table showing average order value per region.

Tipp: Als Best Practice sollten Sie darauf achten, dass Ihre Daten richtig formatiert sind. In diesem Fall ändern wir in jeder Spalte die Bezeichnung, um die Berechnung darzustellen. In Spalten mit Geldwerten ändern wir die Zahlenformatierung zu Währung und das Zahlenformat zu $ #,##0;-$ #,##0.

Das Ziel ist es, den höchsten durchschnittlichen Bestellwert für jede Region abzurufen. Wir müssen Aggr() verwenden, um Qlik Sense anzugeben, dass der durchschnittliche Bestellwert für jede Region pro Manager abgerufen und dann der höchste dieser Werte angezeigt werden soll. Um diese durchschnittlichen Bestellwerte für jede Region pro Manager zu erhalten, müssen wir diese Dimensionen in die Aggr()-Anweisung einschließen:

Aggr(Sum(Sales)/Count([Order Number]), Region, Manager)

Mit dieser Formel erstellt Qlik Sense eine virtuelle Tabelle, die wie folgt aussieht:

Virtuelle Tabelle der Funktion Aggr(), die den durchschnittlichen Bestellwert für jede Region pro Manager zeigt.

table with AGGR function showing average order value for each region, per manager.

Nachdem Qlik Sense die individuellen durchschnittlichen Bestellwerte für Region pro Manager berechnet hat, muss der höchste dieser Werte ermittelt werden. Hierfür schließen wir die Funktion Aggr() in Max() ein:

Max(Aggr(Sum(Sales)/Count([Order Number]), Manager, Region))

Gehen Sie folgendermaßen vor:

  1. Klicken Sie auf Spalte hinzufügen und wählen Sie Kennzahl aus.
  2. Klicken Sie auf das Symbol 3.
    Der Formel-Editor wird geöffnet.
  3. Geben Sie Folgendes ein: Max(Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
  4. Klicken Sie auf Übernehmen.

Die Tabelle zeigt die Region, den durchschnittlichen Bestellwert und den höchsten durchschnittlichen Bestellwert für jede Region pro Manager.

Table showing region, average order value, and largest average order value for each region, per manager.

Sie können den höchsten durchschnittlichen Bestellwert für alle Manager auf Regionsebene sehen. Dies ist die erste unserer zwei komplexen Formeln! Die nächste Anforderung besteht darin, den Namen des Managers zu ermitteln, der für diese hohen durchschnittlichen Bestellwerte verantwortlich ist, und ihn neben den Werten selbst anzuzeigen.

Hierfür verwenden wir die gleiche Funktion Aggr() wie zuvor, aber dieses Mal zusammen mit der Funktion FirstSortedValue(). Die Funktion FirstSortedValue() weist Qlik Sense an, den Manager zurückzugeben, und zwar für die spezifische Dimension, die im zweiten Teil der Funktion angegeben ist:

FirstSortedValue(Manager,-Aggr(Sum(Sales)/Count(Order Number), Manager, Region))

Tipp: Ein kleiner, aber sehr wichtiger Teil der Formel ist das Minuszeichen vor der Aggr()-Formel. Innerhalb einer Funktion FirstSortedValue() können Sie die Sortierreihenfolge der Reihe von Daten angeben. In diesem Fall weist das Minuszeichen Qlik Sense an, von der höchsten zur niedrigsten Zahl zu sortieren.

Gehen Sie folgendermaßen vor:

  1. Klicken Sie auf Spalte hinzufügen und wählen Sie Kennzahl aus.
  2. Klicken Sie auf das Symbol Expression .
    Der Formel-Editor wird geöffnet.
  3. Geben Sie Folgendes ein: FirstSortedValue(Manager,-Aggr(Sum(Sales)/ Count([Order Number]), Manager, Region))
  4. Klicken Sie auf Übernehmen.

Die Tabelle zeigt die Region, den durchschnittlichen Bestellwert, den höchsten durchschnittlichen Bestellwert für jede Region und den für diesen Bestellwert verantwortlichen Manager.

Table showing region, average order value, largest average order value for each region, and manager responsible for that order value.

Weitere Informationen finden Sie unter Aggr.

Weitere Informationen finden Sie unter FirstSortedValue