QlikView + Qlik Sense Blog von Heldendaten

Qlik Sense hat im Sept 2020 Release native k-means Clustering Funktionen erhalten. Man benötigt dafür also nicht mehr zwingend eine Advanced Analytics Connection nach R/Python.  

Da die Funktion auf Engine-Level existiert, war die Wahrscheinlichkeit recht hoch, dass auch das nächste QlikView Feature Release May 2021 diese Funktionen erhält. In den Release Notes und What's New wird es ausgeschwiegen, aber in der Hilfe findet man auch die entsprechenden QlikView Funktionen.

In der Qlik Sense Hilfe befindet sich ein recht ausführliches Beispiel mit einem Scatterplot. In QlikView nicht.

Da die kMeans Funktionen nur eine Zahl (die Clusterklasse) zurück liefern, ist sie nicht ganz so intuitiv zu benutzen wie vielleicht erwartet. Um eine Punktfärbung mit bestimmten Wunschfarben zu erreichen, muss man ein paar Dinge wissen:

 

kMeans  mit 5 Cluster  der Artikelname Frachtkosten zu Umsatz

Ein alter Trick um in Qlik an Farben zu kommen ist die color() Funktion. In QlikView waren das die Default-Farben die in jedem Chart zu definieren waren. Die Funktion gibt es auch in Qlik Sense, wobei ich nicht weiß wo man die Defaultfarben overrulen könnte. Entsprechend nutzt das Beispiel oberhalb

color(
KMeans2D(5,sum(Frachtkosten),sum(Anzahl*Einzelpreis))+1
)-pow(2,30)

Der Trick mit pow(2,30) ist hier beschrieben. Er macht die Punkte ein wenig transparent um ihre Dichte anzudeuten. In der tabellarischen Darstellung kann man die Farbe ebenfalls als Hintergrundfarbe für die gewünschten Spalten nutzen.

Kniffliger wird es, wenn man  für jeden Cluster eine eigene Farben definieren möchte. Da kommt die pick() Funktion zur Hilfe. Nachdem kMeans2D eine Zahl liefert, kann ich es als Input für pick() nutzen.

pick(
KMeans2D(5,sum(Frachtkosten),sum(Anzahl*Einzelpreis))+1,
red(),lightred(),DarkGray(),green(),LightBlue()
) - pow(2,30)

Der oberste Cluster wird dank pick() immer lightblue() sein.

Und was ist nun mit QlikView May 2021 (QlikView 12.60)? Dort funktionieren diese Formeln genauso.

In QlikView kann man sogar bei der Auswahl der Cluster in einer Listbox die Farbe mitgeben. Die Listbox selbst muss mit aggr () definiert sein. Im Beispiel kann man zwischen Land und Artikelnamen hin und herschalten - deswegen eine Variable vDim.

=aggr('$(vDim)gruppe ' &    

(5- ( KMeans2D(5,sum({1}Frachtkosten),sum({1}Anzahl*Einzelpreis))))

,$(vDim))

Als Expression dann die Formel als "Traffic Light Gauge" darstellen. Dann kann man dort über die Segments-Farben das pick() nachbauen.

In der Listbox die Clusterfarben hinterlegen

Beispiel für QlikSense und QlikView findet sich hier.

Eine der Fragen die wir in letzter Zeit wiederholt bekommen haben: Wie kann ich in Qlik alle Materialnummern auswählen die ich zB in einem Excel gespeichert habe. Soviel sei vorab verraten: Alle Werte einzeln anklicken ist nicht die beste Antwort!

Massenselektion in Qlik Sense

Die normale Such-Lupe in einem Filter ist auf 5000 Zeichen limitiert. Selbst wenn der Endanwender also die Qlik Syntax mit 

(A1|A2|A3)

hinbekommen würde, ist bei vielen oder langen Materialnummer sehr schnell das Limit mit der Suche erreicht.

Diese Suche funktioniert, aber bei 5000 Zeichen ist Schluss.

Besser ist, man macht eine "Variable Input" auf eine Variable vSearchListe. Inputboxen können längere Textketten aufnehmen. Wenn man mehrere Zeilen von Excel (oder sonst wo) nach Qlik kopiert, erhält man normalerweise Leerzeichen zwischen den Werten. Entsprechend muss man aus den Einträgen 

A1 A2 A3

in der Inputbox wieder die Qlik Syntax 

(A1|A2|A3)

machen.

Dafür benötigen wir einen Button mit der Aktion "Wähle Sie Werte aus, die dem Suchkriterium entsprechen". Der dynamische Wert ist: 

='('& replace(vSearchListe,'$(vTrennzeichen)','|') &')'

Interessanterweise scheint die Aktion keine Limitierung auf 5000 Zeichen zu haben. Somit kann man richtig viele Werte auf einmal selektieren.

Das Trennzeichen könnte natürlich auch ein Beistrich oder ein Strichpunkt sein, wenn man zum Beispiel Nummern aus einer .csv Datei kopiert. Deswegen halten wir das Trennzeichen in der Variable vTrennzeichen dynamisch.

Die Search Beispiel .qvf findet sich hier. Gleicher Trick funktioniert übrigens auch in QlikView!

<add key="ServiceFailureAlertEmailAddresses" value="qlikviewadmins@heldendaten.net"/>
<add key="ServiceFailureAlertEmailSubject" value="PROD QlikView Server: [SERVICE-NAME] on URL [SERVICE-URL] is down."/>
<add key="ServiceFailureAlertEmailBody" value="PROD QlikView Server: [SERVICE-NAME] on [SERVICE-URL] is down. Go to http://yourserver:4780/QMC/ServiceStatusOverview.htm for details!"/>

<add key="UseSSLForSMTP" value="true"/>

Zeile 5-7 ergänzen. UseSSLForSMTP müsste schon in der .config zu finden sein.

Service Failure Email: Der QlikView Webserver (QVWS) ist down

QvsStatus.aspx vom QVWS sagt "Up" wenn QVS gut läuft

QVS hat zuerst nur 1.8 GB WorkingSet Low. Aber auch 24 GB sind zu wenig RAM für seine .qvws.

Eine Aussage wieviel Arbeitsspeicher der Publisher mit seinen Task-Executions (qvb.exe) gerne hätte, kann man mit dem QVS Log nicht treffen. Wenn Sie Ressourcen-Knappheit im Nachgang analysieren, und den Memory-Verbrauch des Publisher mit einbezieht möchten, dann ist es besser auf das Event 2004 im Windows System Event Log zu sehen.

Der Wunsch nach Dateneingabe und Planung in Qlik, kenn ich, seitdem ich 2008 bei Qlik gestartet hatte.  In unserer Kommunikation war damals die Abgrenzung klar: das "View" in "QlikView" besagt ja schon, dass wir nur Lesend zugreifen. Diese Art der Antwort klappt mit dem "Sense" in Qlik Sense nicht mehr - und dankenswerter Weise gibt es mittlerweile auch etablierte Lösungen, um diese Anforderungen in Qlik abzubilden.

Dateneingabe und Planung in Qlik Sense

Die Historie

In QlikView war das Gewagteste was man anbieten konnte das Konzept der Simulation. Am Ende jeder Vertriebsdemo war in etwa folgender Reiter zu sehen:

Simulation in QlikView

Im QlikView Skript kann man Inputfelder definieren, die nicht der normalen Qlik Symbol-Table Komprimierung unterliegen. Dadurch kann der Planwert auf jeder Zelle vom Endanwender geändert werden. Mit den speziellen Oberflächen-Formeln inputsum, inputavg, etc. wird dann die Dateneingabe auf Gesamtsummen und die Verteilung auf die Details gesteuert. Damit kann ich etwa - wie im .gif oberhalb - sagen, dass ich insgesamt 4000 Umsatz mache, und davon 1500 in Sportswear. Die anderen Werte passen sich automatisch an.

Problematisch bei der QlikView Inputfield-Lösung ist aber, dass diese Usereingaben nur in Lesezeichen abgelegt werden können. Diese Lesezeichen landen in den binären .qvw.shared Files. Sie sind also in der Qlik-Welt "gefangen" und können nicht automatisiert in ein Vorsystem übernommen werden, oder als Input für weitere Qlik Analysen dienen. Außerdem wurden .qvw.shared Files bei vielen Inputfield-Werten und Lesezeichen gerne sehr groß, was diesem Feature eine zusätzliche technische Grenze setzte.

Über Lesezeichen kann sich ein User seine Simulationen in QlikView "ablegen"

Integration von vollwertige Planungslösungen wie Corperate Planning, Jedox, Tagetik mit Qlik gab es schon früh, existieren bis heute und werden immer eine Berechtigung haben. Einige Anbieter hatten sich auch schon früh im QlikView AJAX-Client mittels Extensions an die Dateneingabe direkt in QlikView gewagt, doch ist das Extension Konzept dort doch recht limitiert.  Ein schöner Überblick über die Anbieter findet sich in diesem Blogpost für Writeback von Rob Wunderlich. Eine alte Extension von meinem ehemaligen Kollegen Stefan Walter schwirrt bis heute in Github herum. Das Schaubild zeigt sehr anschaulich wie Writeback-Lösungen technisch funktionieren.

Writeback in Qlik

Die Möglichkeiten in Qlik Sense

Dateneingabe & Datenerfassung

Bezüglich Dateneingabe & Datenerfassung gibt es in Qlik Sense mehrere kommerzielle Extensions die man in Betracht ziehen kann. Typische Anforderungen in diesem Umfeld sind:

- Ein Lagerarbeiter soll direkt in seinem Qlik-Dashboard ein Formular ausfüllen, um einen Bestellauftrag abzuschließen. Für diese Anforderungen würde sich neben write.bi auch Vizlib Writeback Table anbieten. 

- Section Access Berechtigungen für Qlik sollten eigentlich schon im Vorsystem vorliegen und in Qlik wiederverwendet werden. Wenn nicht kann man write.bi auch nutzen um die Section Access Tabelle zu pflegen. 

- Collaboration: User können Kommentare & Anmerkungen zu Kennzahlen am Dashboard oder in einer Profit&Loss Darstellung schreiben. Hier bietet Vizlib Collaboration und Vizlib Finance Lösungen. 

Diese Extensions schreiben in eine SQL-Datenbank oder zu einem REST-Webservice zurück. Startet man einen Qlik-Reload oder nutzt man Qlik Sense Direct Views hat man eine direkte Feedbackschleife.

In dem folgenden Beispiel sieht man eine Write! Edit Tabelle in der bestimmte Felder wie "Confirmed", "Sales" und "Quantity" für bestehende Order IDs vom Qlik-User geändert werden können. Man kann aber auch gleich ganz neue Orders erfassen und dabei automatisch auf Qlik Sense Kunden und Artikelinformationen zugreifen. 

Write! Edit Tabelle und Formular im Einsatz

Im Beispiel erzeugt der Qlik-User eine neue Order 77777. Man sieht wie sich die Kennzahl "Total Sales" im Qlik Dashboard automatisch aktualisiert. Es handelt sich dabei aber wohl um eine Falscheingabe durch den Anwender. Wählt man nun im Qlik Sense Filter die neue Order 77777, kann man diese auch in einer Formularansicht korrigieren. Da Qlik Sense im Hintergrund die Daten automatisch aktualisiert, ist nun die Kennzahl "Total Sales" auch wieder korrekt.

Write! Plan von write.bi

Frontend

Der Summen- (bzw. Durchschnitts-)Algorithmus ermöglicht zwei Verteilungen: Top-down und Bottom-up:

Top-down Verteilung: Die Gesamtsumme des höheren Knotens wird gleichmäßig auf die unterliegenden Knoten der Hierarchie verteilt

Bottom-up Verteilung: wird eine Zahl in einer unteren Ebene neu eingetragen oder verändert, wird diese nach oben aufsummiert. Somit ändert sich hier die Gesamtsumme.

Im Video unterhalb ein Beispiel dazu. Ausgehend von einem Budget "Bad Year" für das Jahr 2019 wollen wir den Oktober anpassen. Zuerst ändern wir die Gesamtsumme auf 2 Millionen, um dann für die Region Ost den Wert auf 600.000 anzupassen. Dieses Budget speichern wir. Man sieht wie sich das Qlik Sense Linienchart oben links anpasst, und nun den Oktober mit 1.93 Millionen anzeigt. Ausgehend von diesem Budget können wir mit Write! Plan auch ein neues Budget "New Budget for 2021" erzeugen, welches wir auf das Jahr 2021 legen. Man sieht wie diese neue Budget-Variante nach dem Speichern automatisch in der Qlik Sense Listbox auftaucht und für das Jahr 2021 verfügbar ist.

Write! Plan im Einsatz

Das Write! Plan Objekt lässt sich wie gewohnt über das rechte Design Panel administrieren.

Write! Plan Design Panel auf der rechten Seite.

Der Export des Write! Plan Objekts nach Excel ist möglich.

Export der Write! Plan Dateneingaben nach Excel

Administration

Wichtig bei Planung ist wer planen darf, und wohin die Daten zurückgeschrieben werden. Für die Verwaltung der Planungsuser und der Daten-Endpoints kommt write.bi mit einer eigenen Weboberfläche.

Welcher User kann in Write! Plan Daten eingeben

User können via LDAP importiert werden. Damit kann man via Active-Directory Gruppen die Qlik Sense und write.bi User synchron halten.

write.bi User via LDAP synchronisieren

Wohin die Plandaten aus der Qlik Sense Oberfläche zurückgeschrieben werden, definiert man bei den Storage Connections. Typischerweise schreibt man nicht direkt in ein Quellsystem zurück, sondern nutze eine eigene Datenbank/Schema um die Write! Plan Daten abzulegen.

In welche Datenbank soll Write! Plan die Daten zurückschreiben

Einlesen der Plandaten

Im Qlik Skript kann man dann diese Plandaten dann ODBC/OLEDB wie herkömmliche Istdaten im Datenmodell ergänzen. Damit hat man eine vollständige Schleife zwischen Dateneingabe und Analyse in Qlik geschaffen.

Zugriff in Qlik Sense auf die Write! Plan Datenbank

In 12 Jahren Qlik kann ich mich nicht erinnern, dass eine neue mathematische Funktion in den Release Notes erwähnt wurde. Mit Qlik Sense Nov 2019 SR3 und dem QlikView 12.50 hat sich das geändert: Willkommen FractileExc() Funktion!

Während es in Qlik schon immer eine median() und eine fractile() Funktion, durften sich Excel User bereits in der Version 2010 über zwei Funktionen verschiedene Quantil-Funktionen QUANTIL.INKL und QUANTIL.EXCL freuen.

Zur Erinnerung: die median Funktion liefert das gleiche wie die fractile(0.5) Funktion. Der Median ist jener Wert, der die höhere Hälfte der Werte von der niedrigeren Hälfte der Werte einer Datenreihe separiert. Je nach Verteilung kann dieser Wert sehr unterschiedlich liegen, und auch von anderen Mittelwerten abweichen.

Quantile für Normalverteilung und chi-Quadrat Verteilung

Warum also eine neue Funktion? Unter dem Titel "Die statischen Excel-Funktionen QUANTIL.INKL und QUANTIL.EXKL" findet sich eine schöne Abhandlung des Bloggers Gerhard Pundt. Die dort benutzte Datenserie, wollen wir in Qlik ebenfalls als Datenquelle nutzen.

FractileExc liefert für das 25% und 75% Quantil andere Werte als die Funktion Fractile

Die Ergebnisse für das 25% und 75% Quantil unterscheiden sich also zwischen der Fractile und der FractileExc Funktion. Qlik folgt den Excel Funktionen QUANTIL.INKL und QUANTIL.EXCL und liefert die gleichen Werte wie das Beispiel im oben genannten Blogpost.

Die Erklärung was FractileExc nun für eine mathematische Funktion ist, liefert das erste Kommentar von spaghettieisblog. Es verweist auf Wolfram Mathworld, wo 8 verschiedene Berechnungsmethoden für Quantile genannt werden.




Demzufolge gilt:

Qlik	Excel	Wolfram
Fractile	QUANTIL.INKL	Q7
FractileExc	QUANTIL.EXCL	Q6 Weibull Quantil

Warum QlikView die Funtion FractileExc nun einige Monate später bekommen hat, beruht wohl auf der Tatsache, dass Qlik Sense und QlikView 12.X die gleiche Engine als Codebasis haben. Ähnlich wie bei der sortable Aggr oder autonumber using Funktion tauchen solche Engine Features mit etwas Versatz auch im folgenden QlikView Release auf.

Hier die FractileExc Funktion in QlikView 12.50

Die .qvf und .qvw findet sich hier.

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

Finance Controlling QlikView Demo nach Stephen Few

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

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 P&L Darstellung mit Kommentaren

Wordcloud in Qlik

//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];

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.

Word Cloud nach Häufigkeit sortieren

• Sentiment Connector 
• Watson Natural Language Connector

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

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.

PieChart mit "KategorieName" als Dimension und "Artikelname" als Alternative Dimension

Wenn man auf die Alternative Dimension "Artikelname" switcht, wird ein Rose Chart daraus.

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