Year to Date Vergleich - auf der Landkarte

Ein Year to Date Vorjahresvergleiche in einer Tabelle? Das bekommt jeder versierte Qlik Entwickler mit ein wenig Set Analysis hin. Aber auf eine Landkarte? Da wird es kniffliger!



Das Kartenobjekt hat seit geraumer Zeit die Diagrammebene zur Darstellung von Kreis- und Balkendiagrammen auf Landkarten. Das Problem ist: was soll man hier als "Balken-Dimension" wählen wenn man zwei Balken für den Vorjahresvergleich zeichnen möchte? 

Mit den richtigen Kalenderfeldern vielleicht eine Dimension Jahr. Wer mutig ist versucht hier hingegen eine Valuelist() als synthetische Dimension.

Die Valuelist definiert welche Balken es geben soll





Die Funktion Valuelist() gibt es schon immer, hat ihre Macken, ist syntaktisch nicht sehr elegant - aber zumindest kann sie nun auch selbst ein Masteritem sein und mit Masteritems rechnen. Sogar Bibliotheksfarben kann sie annehmen, um die Balken auf der Karte wie gewünscht zu färben.

Aktuelles Jahr soll blau, Vorjahr soll grau sein.

Die eigentliche Kennzahlformel bezieht sich dann auf die Valuelist. Wir verwenden als die bestehenden Masteritems für den YTD-Vergleich wieder.
=if (
  valuelist('YTD Vorjahr','YTD Aktuelles Jahr') = 'YTD Vorjahr', 
  [YTD Vorjahr],
  [YTD Aktuelles Jahr]
  )

Daneben noch eine normale Tabelle mit den YTD-Kennzahlen, dann sieht das Ergebnis folgendermaßen aus.




Richtig spannend wird es, wenn man nicht nur 2 Formeln, sondern beliebige KPIs auf die Landkarte bringen möchte. Da spielt die Valuelist() nun ihre ganze Stärke als synthetische Dimension aus. Man erfindet einfach die gewünschten Ausprägungen.

In unserem Beispiel wollen wir nicht nur YTD-Kennzahlen sondern auch das aktuelle Monat (und Vergleichsmonat des Vorjahres) als Balken andrucken. Die Definition der Valuelist wird also länger.






Das Leerzeichen zwischen YTD Kennzahlen und den Monatsvergleich sorgt für einen optischen Abstand zwischen den Balken. Via Bibliotheksfarben können wir zudem Kennzahlen farblich gruppieren.  Unsere neuen Kennzahlen sollen grün sein. Der Platzhalter ist weiß.

Jedem Eintrag in der Valuelist eine Farbe geben




Achtung beim Sortieren der Valuelist in der Diagrammebene! Damit die Balken in der angegebenen Reihenfolge kommen, muss man die Dimension ohne Sortierung definieren.



Die Kennzahl rechnet nun für jeden Valuelist-Eintrag die gewünschte Kennzahl. Damit haben wir nun 4 Balken.

=if(
	valuelist('YTD Vorjahr','YTD Aktuelles Jahr',' ', 'Aktuelles Monat', 'Vorjahr Monat') = 'YTD Vorjahr',
    	[YTD Vorjahr],
if(
	valuelist('YTD Vorjahr','YTD Aktuelles Jahr',' ', 'Aktuelles Monat', 'Vorjahr Monat') = 'YTD Aktuelles Jahr',
    [YTD Aktuelles Jahr],
if(
	valuelist('YTD Vorjahr','YTD Aktuelles Jahr',' ', 'Aktuelles Monat', 'Vorjahr Monat') = ' ',
    0,
if(
	valuelist('YTD Vorjahr','YTD Aktuelles Jahr',' ', 'Aktuelles Monat', 'Vorjahr Monat') = 'Aktuelles Monat',
    [Aktueller Monat],
if(
	valuelist('YTD Vorjahr','YTD Aktuelles Jahr',' ', 'Aktuelles Monat', 'Vorjahr Monat') = 'Vorjahr Monat',
    [Aktueller Monat Vorjahr])))))
    
    


Kleiner Tipp am Rande: manchmal ziehe ich mit sqrt() die Wurzel aus der Kennzahlformel. Dann sind hohe Balken nicht mehr ganz so hoch. Zwar sind dann die absoluten Zahlen falsch - aber mehr als ein Indikator sind die Balken auf der Landkarte sowieso nicht.

Wer dann einen richtigen Tooltip haben will, muss diesen natürlich manuell überschreiben.




Der Download der .qvf findet sich hier.


Qlik: Wähle alle Materialnummern aus Excelsheet mit einem Klick

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.

Qlik Sense Klammern Suche
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!





Bundesländer & Bezirke - Automatisch in Qlik Sense

In der öffentlichen Wahrnehmung sind Kartendarstellungen wohl die "großen Gewinner" des Jahres 2020. Im Jahr 2021 geht es nahtlos so weiter, denn es wurde gerade ein neues Impfdashboard vom Gesundheitsministerium gelauncht. Die Daten dazu wurden auch auf data.gv.at veröffentlicht.  Ein guter Zeitpunkt zu prüfen, wie Qlik Sense diese Geodaten automatisch in Gebietsebenen interpretiert.

Impfdashboard in Qlik Sense mit data.gv.at Daten




Das Impfdaten .csv von data.gv.at enthält die Bundesländer-Namen als Text. 





Qlik versucht für Kartendarstellungen diese Feldinhalten automatisch mit geonames.org abzugleichen. Das wird nicht online gemacht, sondern Qlik hat einen internen Lookup der mitinstalliert wird.

Bundesländer Österreich auf geonames.org


Das funktioniert bei den 9 Bundesländern in Österreich problemlos. Um Qlik bei der Flächenfärbung in der Gebietsebene zu unterstützen, sollte man auch immer die Einstellung Standort/Standorttyp erzwingen. Im Fall von Bundesländern ist das "Verwaltungsgebiet (Ebene 1)". Ein wenig Hintergrund zu dieser Einstellung findet sich in der Qlik Community am Beispiel der Stadt Springfield.




Für Bezirke (Verwaltungsgebiet (Ebene 2)) geht man ähnlich vor. Auch hier liefert data.gv.at etwa die "Anzahl Fälle pro Bezirk" für Corona-Daten. Leider passt die Schreibweise der Bezirksnamen hier nicht ganz so gut zu den Bezirksnamen auf geonames.org. Nach dem initialen Laden der Daten bekommen wir eine lückenhafte Gebietskarte in Qlik Sense.


Um das Problem zu lösen, muss man die Schreibweise von data.gv.at auf die Schreibweisen bei geonames.org anpassen. Statt Graz(Stadt) würde sich Qlik Sense etwa Graz Stadt erwarten.

Geonames.org über Feature Class sagen was man sucht



Zur Korrektur der Bezirksnamen muss man (einmalig) ein Mapping der falschen Bezirksnamen durchführen. Wir haben hierfür ein Excel erzeugt.

Mappe alle Einträge die auf der Karte noch lückenhaft waren.




Dieses Excel nutzen wir dann um ein neues Feld "BezirkClean" zu erzeugen.

GeoNames:
Mapping
LOAD
    Bezirk,
    Laut_GeoNames_org
FROM [lib://data_gv_at/BezirksnamenFürQlik.xlsx]
(ooxml, embedded labels, table is Sheet1) where len(trim(Laut_GeoNames_org)) > 0;




data:
LOAD
    Bezirk,
    applymap('GeoNames',Bezirk,Bezirk) as BezirkClean,
    Anzahl,
    Anzahl_Inzidenz,
    GKZ,
    "Timestamp"
FROM [lib://data_gv_at/Bezirke.csv]
(txt, utf8, embedded labels, delimiter is ';', msq)
;

Mit diesem Feld erzeugen wir wieder eine Gebietsebene. Als Standorttyp wählen wir "Verwaltungsgebiet (Ebene 2)".



Wien kommt leider als eine große Fläche. Will man Wien in seine Bezirke unterteilen, so muss man eine eigene .kml Karte in Qlik Sense laden, wie hier im Blog beschrieben. Postleitzahlen von Österreich werden leider auch nicht automatisch zu einer Gebietsebene. Qlik Sense malt hier nur eine Punktebene. Für eine Gebietsebene würde man ebenfalls eine eigene .kml benötigen. Siehe Details zur Qlik Sense Geo Coverage in der QlikCommunity.

Die .qvf und das Excelmapping findet sich hier zum Download.


Service Failure Alert Email in "QlikView April 2020"

Kein SCOM oder Nagios um den Status der QlikView Services zu überwachen? In QlikView 12.50 gibt es eine Alternative.

Bisher zeigte die QlikView Management Console den aktuellen Service-Status der anderen QlikView Services am Reiter "Service Status Overview" an. Mit QlikView 12.50 kann man sich diese Information aktiv als Email schicken lassen. 

ServiceFailureAlert Email QlikView April 2020
Erkennt das Management Service ein "disconnected" Qlik Service, schickt es ein Email.


Die Doku dazu ist noch ausständig, aber folgende Informationen haben wir vom Qlik Support erhalten und lokal bei uns getestet:


Um diese Einstellung zu aktivieren, müssen Sie das QlikView Management Service stoppen. Unter C:\Program Files\QlikView\Management Service die QVManagementService.exe.config die folgenden XML-Tags ergänzen:

<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!"/>

Die Tags [SERVICE-NAME] und [SERVICE-URL] sind Platzhalter, und werden dynamisch für das betroffene QlikView Service in das Email eingesetzt.

Das QlikView Management Service nutzt den Mailserver, den Sie in der QMC unter "System/Mail Server" konfiguriert haben. Unter Umständen müssen Sie in der QVManagementService.exe.config auch noch das bestehende Setting von false auf true umstellen:
<add key="UseSSLForSMTP" value="true"/>
QlikView 12.50 Service Failure Alert Config
Zeile 5-7 ergänzen. UseSSLForSMTP müsste schon in der .config zu finden sein.

Wenn Sie nun das QlikView Management Service starten, ist die Überwachung aktiv. Wenn man zum Test den QVS, QVWS, DSC, QDS oder Service Dispatcher Service über services.msc stoppt, sollten Sie folgende Service Failure Email bekommen.


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

Leider bekommt man keine Email wenn das Service wieder "up" geht. Und natürlich bekommt man keine Fehlermeldung wenn das QlikView Management Service selbst betroffen ist.  Deswegen ist eine Überwachung via SCOM, Nagios, SNMP, etc. noch immer zu bevorzugen bzw. zusätzlich zu aktivieren.

Den QlikView Server  (und indirekt den QVWS) kann man auch weiterhin regelmäßig über einen http-Request auf  diese URL prüfen: http://yourserver/Qvajaxzfc/QvsStatus.aspx. Der Status Up ist gut. Alles andere deutet darauf hin, dass der Server für die Endanwender nicht erreichbar ist (keine Lizenz, Service Down).

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

Häufigster Problem mit inresponsive Qlik-Services - gerade bei Single Machine Installationen - ist meist, dass die Datenmenge von QlikView Server (QVS) und Publisher (QDS) gemeinsam den Server ins "Paging" bringen. Es wird mehr Arbeitsspeicher allokiert als auf der Maschine verfügbar.

Der QlikView Server meldet RAM-Ressourcenknappheit für sich selbst (also nur die QVS.exe) in seinem Event-Log unter Severity "Warning" als "WorkingSet" Meldungen. QVS.exe versucht zwar den internen Result-Caches zu leeren, wenn aber weiter neue Applikationen von Endanwendern angefordert werden, geht der Memoryverbrauch sowohl über das definierte WorkingSet Limit Low (default 70) als auch WorkingSet Limit High (default 90).

QlikView Working Set Warnings
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.

Hier listet Microsoft bei "Resource-Exhaustion" die Prozesse mit dem höchsten Memory-Consumption in Bytes. Das ist für gewöhnlich der QVS.exe, oder bei großen Reload-Tasks, die entsprechende qvb.exe des Publishers. 

Das Schöne ist, dass man die ProcessID des Events 2004 auch in den TaskLog.txt unter C:\ProgramData\QlikTech\DistributionService\1\Log findet kann. Damit lässt sich das Problemskript des Publishers leicht identifizieren.

QlikView Event 2004 QVB.exe Finden
Die ProcessID 7332 ist der Task "RT_Wawi_DatenmodellVertrieb" und belegt knapp 60GB RAM


Unter Qlik Sense gibt es keine qvb.exe mehr, sondern die Engine.exe übernimmt Endanwender-Anfragen als auch die Reload-Tasks. Entsprechend ist dort die Unterscheidung woher der Ressourcenbedarf kommt schwieriger. Das spricht dafür, dass man auch in Qlik Sense einen eigenen "Scheduling" Server aufsetzt, der die Beladungen übernimmt.

Qlik Sense Event 2004
Engine.exe in Qlik Sense ist QVS.exe und QVB.exe in einem gemeinsamen Prozess. 



 


Dateneingabe und Planung in Qlik mit write.bi

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
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.

Inputfield QlikView Lesezeichen
Ü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
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

Einen Schritt weiter als reine Dateneingabe geht es bei der Planungs-Extension Write! Plan von write.bi. Diese bildet die ursprünglichen Ideen der Qlik Funktionen inputsum, inputavg ab und kann für Planung direkt in Qlik Sense genutzt werden.

Frontend

Write! Plan bietet die Möglichkeit direkt in Qlik Sense neue Budgets und Forecasts zu erstellen. Die Extension stellt eine Tabelle bereit in der man für einen Zeitraum (Jahr und Perioden) über beliebige Dimensionen (etwa Company, Region, SalesRep, Customer) beplanen kann. 

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



heldendaten GmbH,2020