Goodbye Heldendaten - Hello BE-terna!
This blog is discontinued at this location.
We currently plan to migrate the most popular blog posts to BE-terna. Please stay tuned for details!
K-Means Clustering in QlikView und Qlik Sense
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.
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.
 |
| 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.
Abonnieren
Posts (Atom)
