Profit & Loss in Qlik

Profit & Loss Darstellungen haben in Qlik eine lange Historie. Beginnend in QlikView mit einer hübsch formatierten Tabelle bis zur Vizlib Finance oder IBCS Hichert-konformen TrueChart Darstellung in Qlik Sense soll dieser Blogpost die möglichen Visualisierungen beleuchten.

Eine P&L Darstellung ist keine Pivottabelle. Das wird jedem schnell klar der sich mit diesem Thema beschäftigt. Summenzeilen sind keine reinen Aggregationen, sondern ergeben sich aus bestimmten Rechenregeln, manche Zeilen sollen unterschiedlich formatiert sein, und am liebsten würden Endanwender jede Zelle kommentieren können.

Die Historie

Jahrelang war sicherlich der Nachbau  eines Dashboards von Stephen Few die beliebteste Finance Demo in QlikView.

Finance Controlling QlikView Demo nach Stephen Few

Das Dashboard war im Hintergrund der blanke QlikView-Wahnsinn und bestand aus mehreren Dutzenden Objekten. Und genaugenommen umschipperte es das Thema einer konkreten "Profit and Loss"-Darstellung ganz geschickt.

So viele Objekte sind es, wenn man es sich im Entwurfsmodus ansieht



Außerdem gab es für QlikView im Qlik Design Blog eine Vorlage für P&L, die weiterhin seine Gültigkeit hat. Wer dem Beispiel folgt, merkt aber, dass eine ganz normale QlikView Straight Table benutzt wird.

Profit & Loss Statement in QlikView
Profit & Loss Statement in QlikView

Der gleiche Ansatz funktioniert in allen Qlik Sense Versionen, ebenfalls mit einer normalen Tabelle.

Profit & Loss Statement in Qlik Sense
Mit dem Qlik Sense Februar 2019 Release wurde dann die öffentliche P&L Pivot Extension in das Qlik Visualization Bundle aufgenommen, welche eine freiere Formatierung der P&L Darstellung erlaubt.

P&L Pivot als Teil des Qlik Visualization Bundles



Die vollwertigsten Lösungen inklusive Kommentarfunktion direkt in Qlik sind jedoch sicherlich die kommerziellen Extensions Vizlib Finance und das IBCS Hichert-konforme TRUECHART4QLIKSENSE (bzw TRUECHART4QLIKVIEW).

Vizlib Finance Extension

Die Finance Extension wurde ursprünglich von Climber für Qlik Sense entwickelt, und dann von Vizlib übernommen. Die aktuelle Version hat nun eine Zellen-basierte Kommentar-Funktion, die sogar in den Excel-Export übernommen wird.

Vizlib Finance Extension

Der Entwickler kann pro Kennzahl erlauben ob Kommentare erlaubt sind. Dem Endanwender werden bestehende Kommentare durch kleine Kreise mit der Anzahl der Kommentare symbolisiert.

Kommentare auf Zellenebene

Die Eingabe der Kommentare erfolgt direkt im Browser.


Via "Export to Excel with Comments" werden die Kommentare mit exportiert.

Vizlib Finance mit Export nach Excel


TRUECHART

TRUECHART ist die IBCS-konforme Extension für Qlik schlechthin. Schon seit QlikView 10 gibt es diese Extension für den AJAX Client, und stellt auch dort bereits viele Hichert Konzepte zur Verfügung. In Qlik Sense  ist das Arbeiten mit Extensions natürlich weitaus komfortabler, weil direkt im Browser gearbeitet werden kann.

Am Screenshot unterhalb sieht man, dass nicht mehr viel von der "normalen" Qlik Sense Darstellung übrig bleibt. Selbst die Filter kann man sehr kompakt über die TRUECHART Menu Bar einstellen.

TRUECHART P&L Darstellung mit Kommentaren
Auf der Webseite findet sich aktuell auch ein interessantes COVID -19 Dashboard. Hier sieht man, dass die Darstellungen weit über P&L hinaus gehen, und ein gesamtes Dashboard mit TRUECHART gestaltet werden kann.







Die verfügbaren Demoapplikationen für QlikView und Qlik Sense finden sich unter diesem Link.

Karten Features im Qlik Sense Geo Objekt

Das Coronavirus zeigt im Internet gerade wieder wie aufschlussreich man mit Geodaten Zusammenhänge darstellen kann. Auch in der Qlik Community gibt es einige Dashboards die sich dem Thema Coronavirus widmen.

Coronavirus COVID-19 | Qlik Community Response
Coronavirus COVID-19 | Qlik Community Response

Ich will mich in diesem Blogpost ein paar generellen Features widmen, die das Qlik Sense Geo Objekt in den letzten Versionen erhalten hat.


Web Map Service (WMC) Hintergründe

Ein Web Map Service (WMS) ist eine Schnittstelle zum Abrufen von Auszügen aus Landkarten über das World Wide Web.  Spielt man das Beispiel aus der Qlik Hilfe durch, kann man sich sehr einfach eine Qlik Karte erzeugen, die Wassertemperatur und Niederschlagsmenge anzeigt. Wenn notwendig, kann auf zusätzlichen Ebenen eigentlich Daten aus dem Qlik Datenmodell angezeigt werden. In diesem Beispiel wären das die Hauptstädte der Bundesstaaten als rote Punkte.

WMS WassertemperaturNiederschlagsmengen kombiniert mit einem Qlik Sense Punktlayer
WMS Wassertemperatur & Niederschlagsmengen kombiniert mit einem Qlik Sense Punktlayer


Die Stadt Wien etwa stellt über die WMS URL https://data.wien.gv.at/daten/geo unzählige Informationen bereit, die als Hintergrund des Qlik Sense Karten Objekts genutzt werden können. Hier ein Beispiel das alle Krankenhäuser, Polizeistationen und Apotheken anzeigt.

WMS Service der Stadt Wien mit Krankenhäuern, Polizeistationen und Apotheken
WMS Service der Stadt Wien mit Krankenhäuern, Polizeistationen und Apotheken



Im Qlik Sense Edit-Modus kann auf der rechten Seite via "WMS-Einrichtung" gewählt werden, welche Informationen gerade angezeigt werden sollen.

WMS Service - Ebenen wählen
WMS Service - Ebenen wählen


Die Stadt Wien stellt so mehr als 100 Geoinformationen zu Verfügung, die Sie beliebig kombinieren können. Sie suchen einen Campingplatz in Wien in einem Biosphären-Park oder an einem stehenden Gewässer?  Kein Problem, einfach die Layer in Qlik Sense auswählen, und die Daten werden angezeigt.

Campingplätze in Wien's Biospährenpark
Campingplätze in Wiens Biosphärenpark


Bild als Hintergrund

Steht einem kein Webservice zu Verfügung, hat man im einfachsten Fall nur eine starre Bilddatei als Hintergrundkarte. Das bringt uns zu einem Feature, dass wir im Blog schon 2014 für QlikView vorgestellt hatten. Damals war es die Kabinenbelegung auf einem Kreuzfahrtschiff, in Qlik Sense wäre das Beispiel Supportmitarbeiter in einem Großraumbüro.

In der Qlik Hilfe ist dieses Bild als Sitzplätze im Großraumbüro verlinkt: https://help.qlik.com/en-US/sense/Subsystems/Hub/Content/Resources/Images/ui_map-image-background-example.png

Auf dem Bild überlegt man sich ein Koordinaten System, dass man im Qlik Script zu den Supportmitarbeitern mappt.

Will ich etwa den Mitarbeiter "Nehru" vom kleinen Besprechungstisch auf den großen Tisch setzen,

so muss man seine "Koordinaten" im Script anpassen.




Karte mit Graphikebenen

Seit einigen Versionen kann Qlik Sense neben den klassischen Punkt- und Gebietsebenen auch noch verschiedene Graphikebenen wie Kreisdiagramme, Balkendiagramme, Density Maps und Linienlayer.

Verschiedene Ebenen im Geo Objekt
Bei Kreisdiagrammen steht einem eine zusätzliche Dimension zur Verfügung, nach der man den eigentlichen Geo-Datenpunkt aufreißen kann. Im Beispiel unterhalb wird pro Postleitzahl noch zusätzlich das Feld Kategorie aufgerissen.

Die Postleitzahl aufgerissen nach Category. Der Tooltip zeigt absulte und relative Werte
Die Postleitzahl aufgerissen nach Category. Der Tooltip zeigt absolute und relative Werte

Einstellungen auf der Diagrammebene

Eigenes Symbol als Punktebene

Zu guter Letzt noch ein Feature aus dem aktuellen Qlik Sense Feb 2020 Release. In der Punktebene ist es nun möglich auch eine eigene Graphik als Punkt zu definieren.

Die französische Flagge als Bild Symbol auf der Punktebene
Die französische Flagge als Bild Symbol auf der Punktebene


Die Beispiele von oberhalb finden Sie hier. Die Demo zu Qlik Feb 2020 finden Sie direkt auf demo.qlik.com,

Volltextsuche & Wordcloud in Qlik

Qlik bietet eine Volltextsuche über alle Felder. Aber kann man Qlik auch nutzen, um zum Beispiel alle Supporttickets oder Kundenfeedbacks nach Keywords zu durchsuchen und die Häufigkeit in einer Wordcloud darstellen? Der Artikel unterhalb beschreibt wie es funktioniert.

Word Cloud Qlik
Wordcloud in Qlik 

Das Qlik auch die Häufigkeit von Millionen Wörter darstellen kann habe ich schon 2015 hier unter Qlik Sense Smart Data Compression beschrieben.

Aber wie bekommt man aus einem längeren Text die einzelnen Wörter extrahiert, um etwa zu zählen wie oft ein Wort in Supportcases vorkommt?

Die Inputdaten für das Beispiel



Hier ist die Subfield Funktion von Hilfe.

//load data
[Data]:
LOAD
 [Supportcase],
        //replace line breaks with blanks
 replace([Text],chr(10),' ') as Text 
    
 FROM [lib://data/supportcases.xlsx]
(ooxml, embedded labels, table is Tabelle1);

//Split into Words
TempAllWords:
Load
[Supportcase],
trim(subfield([Text],' ')) as Words
resident [Data];


Nach dem Splitten der Wörter kann man noch Satzzeichen entfernen. Zusätzlich kann man ungewünschte Füllwörter wie Pronomen, Artikel usw. entfernen, indem man ein Excel mit den irrelevanten Wörtern pflegt.

AllWords:
NoConcatenate
load 
[Supportcase],
upper(Words) as Words,
applymap('IrrelevantWords',upper(Words),'relevant') as Relevanz
where len (Words) > 0;
load
[Supportcase],

(if(match(right(Words,1),'.',',','!','?',';') > 0, left(Words,len(Words)-1),Words)) as Words  //cleanup 
resident TempAllWords;


Was dieser Ansatz nicht macht, sind Volltextsuchen-Features wie Stemming  um alle Wörter auf Ihren Wortstamm zu reduzieren. Hier könnte man die Server Side Extensions von QlikView und Qlik Sense nutzen, und die Wörter noch durch ein Python Skript für Stemming laufen lassen.

Die Wordcloud Darstellung selbst kommt mit dem Qlik Sense Visualization Bundle. Bitte auf die Sortierung und die Design|Orientation achten.

Qlik Sense Word Cloud nach Häufigkeit sortieren
Word Cloud nach Häufigkeit sortieren


Qlik Sense Word Cloud Orientations
Wordcloud Orientations auf 3


Als weiterführendes Thema kann man Sentiment Analyse betreiben. Viele Kunden haben automatisch die Qlik Sense WebConnectors lizensiert. Dort gibt es zwei Konnectoren die hier interessant sein können. Bei Gelegenheit werde ich dazu einen eigenen Blogpost schreiben.


Das komplette Beispiel zum Download findet sich hier

Qlik Sense Variance Waterfall für Bestände

Ab Qlik Sense Sept 2019 kommt im "Visualization Bundle" das Variance Waterfall Chart mitinstalliert. Die Darstellung ist viel dynamischer als das normale Wasserfalldiagramm, weil man nicht für jeden Balken eine eigene Expression schreiben muss.

Für normale YTD-Darstellungen in einer Vertriebsapplikation ist das Chart recht Straight Forward. Das Variance Waterfall Chart nutzt die gleichen Formeln wie das normale Wasserfalldiagramm, und läuft zusätzlich über das "Monat"-Feld. Im Beispiel unterhalb sieht man so auf einem Blick, dass die Monate Jänner, Februar und März noch besser als das Vorjahr waren, bevor sich dann in den Folgemonaten der Umsatz YTD schlechter entwickelt hat.

Qlik Sense Wasserfall Variance
Mit den YTD und YTD Vorjahr Formel zeigt das Variance Waterfall die Umsatzenwicklung nach Monaten aufgerissen!

Zuletzt hatten wir jedoch eine Anforderung mit diesem Charttyp die Veränderung von Beständen darzustellen. Beispiele dafür wären: Lagerbestände, Entwicklung Anzahl Kunden pro Monat, usw.

Für diese Anforderung scheint das Chart nicht unbedingt geschaffen, weil es das Konzept von Start-Bestand und End-Bestand nicht wirklich kennt, und es keine Differenz zum Vormonat bildet. Man sieht das ganz gut, wenn man sich den Javascript Code der Extension von Qlik  ansieht.

Wieder hilft uns aber ein Workaround den wir schon für die Sparklines im Climber KPI Objekt  (mittlerweile in das kommerzielle Vizlib aufgegangen) vorgestellt hatte. Mittels dimensionality(), below() und firstsortedvalue() kann man auch diese Anforderung darstellen. Die Formeln sind nur ein wenig komplizierter. Zum Test gibt es die Werte rechts als Tabelle.

Über zwei Jahre entwickelt sich der Lagerstand von 700.000 auf 670.000



STARTFORMEL:

rangesum(FirstSortedValue(distinct   aggr(nodistinct sum(Bestand),Monat), Monat,1),-0)



ENDFORMEL:
if (dimensionality() = 0,
 //Höhe Balken Zielmonat
 rangesum(FirstSortedValue( distinct   aggr(nodistinct sum(Bestand),Monat), -Monat,1))
,
 //Wert des Vormonats
    alt(
       below(
        rangesum(FirstSortedValue( distinct   aggr(nodistinct sum(Bestand),Monat), -Monat,1),-0)
        ,1)

     ,
 
        //im letzten Monate keinen Unterschied zeichnen
        rangesum(FirstSortedValue( distinct   aggr(nodistinct sum(Bestand ),Monat), -Monat,1))
    )

 )



Als Dimension nutzen wir ein eigenes Feld "MonatWasserfall" welches um ein Monat zum tatsächlichen Datum verschoben ist. Damit ist die Beschriftung dann schlüssiger.


Weil Bestände oft sehr hoch, und die Veränderungen verhältnismäßig klein sind können, haben wir auch noch die Funktion eingebaut, dass sich ein Anwender selbst den Skala-Start definieren kann. Gibt er in das Inputfeld 400.000 ein, sieht das Chart folgendermaßen aus, weil die Y-Achse nicht mehr bei 0 beginnt.


Variance Waterfall mit Y-Achse Start auf 400.000


Gibt der User gar keinen Wert ein, dann wählen wir mit der Formel =if(len(vSkala)=0, min (aggr(sum(Bestand),Datum)) * 0.95 ,vSkala) den Skalenwert dynamisch und geben ihn als Fußnote aus.



Dynamischer Y-Achse Startpunkt


Alternativ zur vorgestellten Lösung könnte ein As-Of-Ansatz im Datenmodell funktionieren wo immer das Vormonat "mitselektiert" wird. Das bleibt zu testen. Wer für unsere Lösung herausfindet wie man den letzten roten Balken im Jänner 2021 mit dem Wert 0 loswerden kann, darf sich gerne melden :-)

Der Download zum Qlik Sense Beispiel findet sich hier.

GetObjectDimension & GetObjectMeasure - undokumentierte Qlik Chart Funktionen

Qlik vergisst gerade gerne neue Funktionen in den Release Notes zu erwähnen. Barry Harmsen hat gerade das neue Autonumber Script Statement beschrieben. Die beiden Chart Funktionen getObjectDimension und getObjectMeasure werden zwar von der Qlik Code Completion vorgeschlagen, in der Dokumentation sind sie aber nur bei der Funktion "GetObjectField" angedeutet.

Die GetObject* Funktionen in der Qlik Auto Completion
Die GetObject* Funktionen in der Qlik Auto Completion


Seit einigen Versionen hat auch Qlik Sense die Funktionalität Spalten (Dimensionen und Kennzahlen) in Diagrammen dynamisch ein- und auszublenden.

Hier kann man sich die getObjectDimension und getObjectMeasure zu nutzen machen, um abzufragen was denn gerade der Titel der jeweiligen Spalte ist:

Ist die Spalte "Count" eingeblendet wird "Anteil der Firmen" angezeigt

Ist die Spalte "Revenue" eingeblendet wird "Anteil des Umsatzes" angezeigt

Die Funktionen können als Kennzahl genutzt werden


oder zur Definition der Hintergrund/Textfarben. Leider kann man momentan die Funktionen nicht für die Titelbeschreibung der Charts nutzen.



Die Funktionen liefern die Beschriftung der Spalten, und nicht deren Definition.  Fügt man also zb. das Feld "Region" als Spalte ein, ändert aber den Titel auf "State" so liefert GetObjectDimension(1) auch die Bezeichnung "State".


Ändert man die Spaltenreihenfolge mittels Drag&Drop ändern sich die Formelergebnisse ebenfalls nicht. Es scheint also so, als würden die beiden Funktionen auch interne Indexe zugreifen!


Mit GetObjectDimension() kann man auch auf Alternative Dimensionen zugreifen. Zum Beispiel könnte man sagen für "Kategoriename" macht man ein normales PieChart, wenn aber auf "Artikelname" geswitcht wird, nutzt man die Radius-Formel  für ein RoseChart. Damit kann man wieder einiges steuern, wo wir in QlikView die Zyklische Gruppe & getcurrentfield()-Funktion hatten.

GetObjectDimension für Alternative Dimensionen 1
PieChart mit "KategorieName" als Dimension und "Artikelname" als Alternative Dimension



GetObjectDimension für Alternative Dimensionen 2
Wenn man auf die Alternative Dimension "Artikelname" switcht, wird ein Rose Chart daraus.



GetObjectDimension in Formel nutzen
Hier die Radiusformel mit GetObjectDimension(0) um das Rose Chart zu generieren.




Die Beispiel .qvf finden Sie hier. Hier der Qlik Knowledge Base Artikel zu den Funktionen:
https://support.qlik.com/articles/000058438



heldendaten GmbH,2020