Abfragetabellen und Formeln in Zoho Analytics mit SQL: wann Sie was einsetzen

Für Abfragetabellen und Formeln in Zoho Analytics, mit SQL oder ohne, gilt eine einfache Regel. Eine Abfragetabelle nutzen Sie, wenn Sie Daten aus mehreren Tabellen neu zusammenstellen, filtern und verdichten müssen. Eine Formel nutzen Sie, wenn Sie eine Kennzahl pro Datensatz oder pro Berichtsgruppe berechnen. Reine Verknüpfungen zweier Tabellen erledigen Nachschlagespalten mit Auto-Join.

Eine Abfragetabelle ist eine Datensicht in Zoho Analytics, die Daten aus einer oder mehreren Tabellen eines Arbeitsbereichs mit einer SQL-SELECT-Abfrage zusammenführt. Der Arbeitsbereich (im englischen Original „Workspace“) ist der Container, in dem Ihre Tabellen, Berichte und Dashboards liegen. Eine Abfragetabelle ändert keine Daten. Sie liest nur und stellt das Ergebnis als neue Tabelle bereit.

Formeln rechnen dagegen innerhalb einer bestehenden Tabelle oder eines Berichts. Zoho unterscheidet laut seiner Hilfe drei Formeltypen: Formelspalte, Aggregatformel und Berichtsformel. Welcher Typ passt, hängt davon ab, ob die Kennzahl pro Zeile oder über viele Zeilen gerechnet wird.

Dieser Beitrag ist der zweite von drei Beiträgen zu Zoho Analytics. Der erste zeigt, wie Sie Zoho-CRM-Daten verbinden und ein erstes Dashboard aufbauen. Dieser zweite Beitrag behandelt Abfragetabellen und Formeln mit vollständigem Code, den Sie in Ihren Arbeitsbereich übernehmen können.

Zoho Analytics bietet vier Werkzeuge, um aus Rohdaten Kennzahlen zu machen. Sie unterscheiden sich darin, wo das Ergebnis liegt und wie weit es sich wiederverwenden lässt. Die Abfragetabelle ist oben definiert. Die drei Formeltypen beschreibt Zoho auf der Hilfeseite Kennzahlen mit Formeln erstellen.

Formelspalte

Eine Formelspalte ist eine neue, berechnete Spalte in einer Datentabelle. Zoho speichert das Ergebnis als zusätzliche Spalte, und Sie verwenden sie in jedem Bericht wie jede andere Spalte. Sie kombinieren dabei logische, statistische, Datums- und Textfunktionen mit den Operatoren +, -, / und *.

Aggregatformel

Eine Aggregatformel ist eine Kennzahl, die immer einen Zahlenwert liefert. Zoho berechnet sie für jeden Datensatz oder jede Gruppe des Berichts, in dem sie steht. Sie wird nicht als Spalte in die Basistabelle geschrieben, sondern bleibt der Tabelle zugeordnet, auf der sie angelegt wurde. Sie steht in Diagrammen, Pivot-Tabellen und Übersichtsansichten zur Verfügung.

Berichtsformel

Eine Berichtsformel ist eine Formel, die Sie direkt in einem Bericht anlegen. Sie erlaubt die Grundrechenarten und verschachtelte WENN-Bedingungen über die Spalten dieses Berichts. Sie gilt nur in dem Bericht, in dem sie entstanden ist, und lässt sich nicht in andere Berichte übernehmen.

Die folgende Tabelle ordnet die fünf Möglichkeiten in Zoho Analytics nach Rechenebene, Speicherort und typischem Einsatz. Nachschlagespalten mit Auto-Join sind aufgeführt, weil sie für reine Verknüpfungen die schlankere Wahl sind. Eine Nachschlagespalte ist eine Spalte, die zwei Tabellen über einen gemeinsamen Wert verbindet.

Werkzeug	Rechnet auf Ebene	Ergebnis liegt	Typischer Einsatz
Nachschlagespalte mit Auto-Join	Verknüpfung, keine Berechnung	In der Tabellenbeziehung	Deals und Konten gemeinsam in einem Bericht zeigen
Abfragetabelle (SQL)	Beliebig, per SELECT	Als eigene Tabelle	Filtern, gruppieren und zusammenführen, bevor ein Bericht entsteht
Formelspalte	Pro Zeile	Als neue Spalte der Tabelle	Jahr oder Quartal aus einem Datum, Textbereinigung
Aggregatformel	Pro Berichtsgruppe	Der Tabelle zugeordnet, keine Spalte	Gewonnener Betrag, Abschlussquote, Summen seit Jahresbeginn
Berichtsformel	Über Spalten eines Berichts	Nur im einen Bericht	Einmalige Rechnung, die kein anderer Bericht braucht

Die wichtigste Unterscheidung ist die Rechenebene. Eine Quote wie die Abschlussquote ergibt pro Zeile keinen Sinn, sie braucht eine Aggregatformel. Ein Quartal aus einem Abschlussdatum ist dagegen eine Eigenschaft jeder einzelnen Zeile und gehört in eine Formelspalte.

Zu den Aggregatfunktionen gehören unter anderem sumif, countif, count, distinctcount sowie ytd, qtd und mtd für Werte seit Jahres-, Quartals- und Monatsbeginn. Sie alle verdichten die Zeilen, die ein Bericht gerade zeigt, zu einem Wert.

Abfragetabellen in Zoho Analytics akzeptieren nur SELECT-Abfragen, also lesende SQL-Befehle. Zoho nimmt Abfragen in acht Dialekten an: ANSI, Oracle, SQL Server, IBM DB2, MySQL, Sybase, Informix und PostgreSQL. Für die beste Abdeckung empfiehlt Zoho ANSI-SQL. In Zohos Beispielen stehen Tabellen- und Spaltennamen in doppelten Anführungszeichen, Textwerte in einfachen.

Die Hilfeseite zu Abfragetabellen in Zoho Analytics nennt folgende Grenzen:

• Nur Left Join, Right Join und Inner Join sind erlaubt.
• Korrelierte Unterabfragen, also Unterabfragen in der WHERE-Klausel, sind nicht möglich.
• Eine Abfrage darf höchstens drei nicht rekursive Common Table Expressions enthalten.
• PIVOT und UNPIVOT lassen sich nicht mit Common Table Expressions kombinieren.
• Unterabfragen in Common Table Expressions und umgekehrt sind ausgeschlossen.
• Auf einer bestehenden Abfragetabelle bauen Sie höchstens drei weitere Ebenen auf.

Eine Common Table Expression (CTE) ist ein temporäres Zwischenergebnis innerhalb einer Abfrage, das Sie mehrfach in derselben Abfrage verwenden. Sie ersetzt oft eine zweite Abfragetabelle und spart damit eine Ebene.

Einige MySQL-Datumsfunktionen fehlen. DATE_ADD, DATE_SUB, TIMESTAMPADD und TIMESTAMPDIFF unterstützt Zoho Analytics derzeit nicht. Bei ADDDATE muss der Abstand eine Zahl sein, kein Ausdruck wie INTERVAL 31 DAY. Wer SQL aus einem anderen System übernimmt, sollte diese Stellen vorab ersetzen.

Eine Abfragetabelle für den gewonnenen Umsatz je Branche und Quartal verbindet Deals mit Konten und verdichtet die Beträge. Zoho setzt solche Abfragetabellen in seiner eigenen Lösung für CRM-Auswertungen ein. Die dortige Abfrage „Potential Conversion by Month“ filtert zum Beispiel ebenfalls auf die Phase „Closed Won“.

Prüfen Sie vor dem Ausführen zwei Dinge in Ihrem Arbeitsbereich. Erstens: Heißt die Tabelle bei Ihnen „Deals“ oder „Potentials“? Zohos eigenes Formelbeispiel verwendet „Potentials“. Zweitens: Enthält die Spalte „Account Name“ die ID des Kontos oder seinen Namen? Wenn die Abfrage einen Fehler meldet, korrigieren Sie zuerst diese Namen.

Der folgende Code liefert pro Branche, Jahr und Quartal die Summe der gewonnenen Deals. Fügen Sie ihn in den SQL-Editor einer neuen Abfragetabelle in Ihrem Arbeitsbereich ein.

SELECT "Accounts"."Industry" AS "Industry",
       YEAR("Deals"."Closing Date") AS "Year",
       QUARTER("Deals"."Closing Date") AS "Quarter",
       SUM("Deals"."Amount") AS "Won Revenue"
FROM "Deals"
INNER JOIN "Accounts" ON "Deals"."Account Name" = "Accounts"."Id"
WHERE "Deals"."Stage" = 'Closed Won'
GROUP BY "Accounts"."Industry",
         YEAR("Deals"."Closing Date"),
         QUARTER("Deals"."Closing Date")

Drei Zeilen passen Sie typischerweise an. Die Zeile mit INNER JOIN legt fest, über welche Spalten Deals und Konten zusammenfinden. Enthält „Account Name“ bei Ihnen den Namen statt der ID, muss die rechte Seite auf die Namensspalte der Konten zeigen. Die WHERE-Zeile nennt die Phase exakt so, wie sie in Ihren Daten steht. Schreibweise und Sprache müssen übereinstimmen.

Beachten Sie die Wirkung des Inner Join. Deals ohne zugeordnetes Konto fallen aus dem Ergebnis heraus. Wenn Sie diese Deals sehen wollen, ist ein Left Join die richtige Wahl.

Aggregatformeln berechnen in Zoho Analytics einen Wert über alle Zeilen einer Berichtsgruppe. Das macht sie zur richtigen Wahl für Summen mit Bedingung und für Quoten. Beide Beispiele legen Sie als Aggregatformel auf der Tabelle Ihrer Deals an, nicht als Formelspalte.

Das erste Beispiel stammt unverändert aus Zohos Hilfe und summiert die Beträge aller gewonnenen Verkaufschancen. Tragen Sie es als neue Aggregatformel ein.

sumif("Potentials"."Stage" = 'Closed Won', "Potentials"."Amount")

Heißt Ihre Tabelle „Deals“, ersetzen Sie beide Vorkommen von „Potentials“. Die Phasenbezeichnung passen Sie wie in der Abfragetabelle an Ihre Daten an.

Das zweite Beispiel berechnet die Abschlussquote in Prozent aus den dokumentierten Funktionen. Es teilt die gewonnenen durch alle abgeschlossenen Deals. Legen Sie es ebenfalls als Aggregatformel an.

countif("Deals"."Stage" = 'Closed Won')
  / countif("Deals"."Stage" in ('Closed Won','Closed Lost')) * 100

Die Liste in Klammern bestimmt den Nenner. Offene Deals gehören nicht hinein, sonst sinkt die Quote künstlich. Bei countif ist der zweite Ausdruck laut Zoho optional und wird ohne Angabe als Nullwert behandelt.

Weil Zoho die Formel pro Berichtsgruppe rechnet, zeigt eine Pivot-Tabelle nach Vertriebsmitarbeiter automatisch die Quote je Person. Für eine Gesamtquote neben den Einzelwerten gibt es Groupby-Verschiebungen wie Fixed Groupby. Mit Ignore_Filters steuern Sie über die Codes 0, 1 und 2, welche Filter die Formel übergeht.

Eine Formelspalte berechnet in Zoho Analytics einen Wert für jede einzelne Zeile und speichert ihn als neue Spalte. Jahr und Quartal eines Abschlussdatums sind klassische Fälle. Jeder Deal hat genau ein Abschlussjahr, also gehört die Rechnung auf die Zeilenebene.

Die beiden folgenden Ausdrücke legen Sie als zwei getrennte Formelspalten in der Tabelle Ihrer Deals an. Der erste liefert das Jahr, der zweite das Quartal.

year("Deals"."Closing Date")
quarter("Deals"."Closing Date")

Ändern müssen Sie meist nur den Tabellen- und Spaltennamen, falls Ihr Abschlussdatum anders heißt. Danach stehen beide Spalten in jedem Bericht als Gruppierung bereit, ohne dass Sie eine Abfragetabelle brauchen.

Die SQL-Funktion QUARTER in einer Abfragetabelle und die Funktion quarter() in einer Formelspalte stammen aus zwei verschiedenen Funktionsbibliotheken. Prüfen Sie in der Vorschau, in welchem Format jede ihr Ergebnis ausgibt, bevor Sie beide Quellen in einem Bericht mischen. Unterschiedliche Formate führen sonst zu Quartalen, die nicht zusammenpassen.

Für Zeitabstände bietet die Formelbibliothek weitere Funktionen. dateandtimediff rechnet in den Einheiten SECOND bis YEAR, darunter QUARTER. months_between liefert Monate als Dezimalzahl und setzt dabei 31 Tage als einen Monat an. Das ist für grobe Laufzeiten brauchbar, für taggenaue Fristen nicht.

Die meisten fehlerhaften Berichte in Zoho Analytics entstehen durch das falsche Werkzeug oder eine unsaubere Verknüpfung. Bei Svennis prüfen wir vor jeder neuen Abfragetabelle, ob eine Nachschlagespalte mit Auto-Join dieselbe Frage beantwortet. Wenn Zahlen bei Kunden nicht stimmen, finden wir die Ursache häufig in einer Verknüpfung über den Kontonamen statt über die ID.

Abfragetabelle nur zum Verknüpfen

Eine Abfragetabelle, die nur zwei Tabellen verbindet, ist unnötig. Auto-Join verknüpft Tabellen beim Erstellen eines Berichts automatisch, sobald eine Nachschlagespalte sie verbindet. Die Nachschlagespalte legen Sie im Importassistenten, im Tabellendesigner oder im Berichtseditor an.

Unbeachteter Standard-Join

Auto-Join arbeitet standardmäßig mit einem Left Join. Der Bericht enthält dann alle Zeilen der untergeordneten Tabelle, auch ohne passenden Eintrag in der übergeordneten. Die Join-Art ändern Sie im Diagrammdesigner über das Symbol „View Relationships“.

Quoten als Formelspalte oder Durchschnitt

Eine Quote gehört in eine Aggregatformel. Ein Durchschnitt über Einzelquoten gewichtet kleine und große Gruppen gleich und liefert ein anderes Ergebnis als die echte Gesamtquote.

UNION statt UNION ALL

UNION wendet implizit ein Distinct an und entfernt identische Zeilen. Zoho empfiehlt deshalb UNION ALL. Zwei gleich hohe Rechnungen am selben Tag gehen sonst stillschweigend verloren.

Leere Werte und tiefe Ketten

Min und Max liefern null, wenn eine Spalte keine Werte enthält. Ketten aus Abfragetabellen enden nach drei Ebenen. Löschen Sie eine Spalte einer Abfragetabelle, bricht Zoho den Vorgang ab, wenn abhängige Ansichten existieren.

Deutsche Unternehmen stoßen in Zoho Analytics auf vier Voreinstellungen, die nicht zum hiesigen Alltag passen. Jede davon kann Quartale, Wochen oder Textlängen verfälschen, ohne dass eine Fehlermeldung erscheint.

Abweichendes Geschäftsjahr

Die Funktionen ytd, qtd und mtd kennen den Parameter fiscal_start_Month mit Werten von 1 für Januar bis 12. Er ist nur dann Pflicht, wenn im Arbeitsbereich ein abweichender Beginn des Geschäftsjahres eingestellt ist. Kommen Ihre Umsätze aus Zoho Books, sollten Quartale in Analytics mit derselben Geschäftsjahresdefinition rechnen wie Ihre Buchhaltung. Das erspart Rückfragen, wenn Zahlen über die Übergabe an DATEV beim Steuerberater landen.

Zeitzone und Sommerzeit

Funktionen wie today(), now() und modified_time() liefern Werte immer in GMT. Für die deutsche Ortszeit nutzen Sie convert_tz(). Nur Zeitzonenkennungen berücksichtigen die Sommerzeit automatisch, feste Versätze und Abkürzungen nicht.

Wochenbeginn und Werktage

Die SQL-Funktion WEEK geht standardmäßig von einem Wochenbeginn am Sonntag aus. Für Kalenderwochen ab Montag übergeben Sie einen MODE als zweites Argument. Die Funktionen business_days und business_hours werten Samstag und Sonntag als Wochenende, sofern Sie nichts anderes angeben.

Umlaute in Textfeldern

LENGTH zählt Bytes, und Mehrbytezeichen wie ä oder ß zählen dabei mehrfach. CHAR_LENGTH zählt jedes Zeichen einmal. Für Längenprüfungen deutscher Texte ist CHAR_LENGTH die richtige Funktion.

Der schnellste Nutzen entsteht, wenn Sie Ihre vorhandenen Berichte mit der Entscheidungstabelle abgleichen. Gehen Sie dafür in dieser Reihenfolge vor:

1. Listen Sie alle Abfragetabellen auf und markieren Sie jene, die nur verknüpfen. Ersetzen Sie diese durch Nachschlagespalten mit Auto-Join.
2. Prüfen Sie jede Verknüpfung zwischen Deals und Konten: ID oder Name, Deals oder Potentials.
3. Verschieben Sie Quoten und bedingte Summen in Aggregatformeln.
4. Legen Sie Jahr, Quartal und ähnliche Zeilenwerte als Formelspalten an.
5. Kontrollieren Sie Geschäftsjahr, Zeitzone und Wochenbeginn im Arbeitsbereich.
6. Ersetzen Sie UNION durch UNION ALL, wo identische Zeilen legitim sind.

Testen Sie jede Änderung zuerst an einem Bericht, dessen Ergebnis Sie aus einer anderen Quelle kennen, etwa der Umsatzsumme eines abgeschlossenen Quartals. Stimmt dieser Wert, übertragen Sie das Muster auf die übrigen Berichte.

Wenn Sie Zoho Analytics neu aufsetzen oder ein gewachsenes Berichtswesen ordnen wollen, beschreibt die Seite zur Einführung von Zoho Analytics, wie Svennis dabei vorgeht.

• Zoho Analytics Help: Query Tables
• Zoho Analytics Help: Joining Tables
• Zoho Analytics Help: Creating Your Own Metrics with Formulas
• Zoho Analytics Help: Aggregate Functions
• Zoho Analytics Help: Formula Column In-built Functions
• Zoho Analytics API: Supported SQL
• Zoho Analytics Help: Zoho CRM Solution Queries