Qlik Sense: Sorting by Expression

This can be a very frustrating problem for many Qlik users.


I have a list of the days of the week, but as you can see they are not in order. Unfortunately, sorting them alphabetically will not help your situation either. The solution is to Sort by Expression

Go into to edit mode and click on the object in question (in this case a Filter). Now click on Sorting in the right side bar and unclick Auto Sorting


Uncheck Sort numerically and Sort alphabetically, and check Sort by expression


Click on the Expression box and the formula window opens.


The formula we are going to use is the Match() formula:

Syntax: match(field, condition 1, condition 2, etc)


Hit apply and check your Filter box.



R: Creating a Word Cloud

Word Clouds are great visualization techniques for dealing with text analytics. The idea behind them is they display the most common words in a corpus of text. The more often a word is used, the larger and darker it is.


Making a word cloud in R is relatively easy. The tm and wordcloud libraries from R’s CRAN repository is used to create one.


If you do not have either of these loaded on your machine, you will have to use the following commands


Now in order to make a word cloud, you first need a collection of words. In our example I am going to use a text file I created from the Wikipedia page on R.

You can download the text file here: rwiki

Now let’s load the data file.

text <- readLines("rWiki.txt")
> head(text)
[1] "R is a programming language and software environment 
[2] "The R language is widely used among statisticians and 
[3] "Polls, surveys of data miners, and studies of scholarly 
[4] "R is a GNU package.[9] The source code for the R 
[5] "General Public License, and pre-compiled binary versions
[6] "R is an implementation of the S programming language "

Notice each line in the text file is an individual element in the vector –  text

Now we need to move the text into a tm element called a Corpus. First we need to convert the vector text into a VectorSource.

wc <- VectorSource(text)
wc <- Corpus(wc)

Now we need to pre-process the data. Let’s start by removing punctuation from the corpus.

wc <- tm_map(wc, removePunctuation)

Next we need to set all the letters to lower case. This is because R differentiates upper and lower case letters. So “Program” and “program” would treated as 2 different words. To change that, we set everything to lowercase.

wc <- tm_map(wc, content_transformer(tolower))

Next we will remove stopwords. Stopwords are commonly used words that provide no value to the evaluation of the text. Examples of stopwords are: the, a, an, and, if, or, not, with ….

wc <- tm_map(wc, removeWords, stopwords("english"))

Finally, let’s strip away the whitespace

wc <- tm_map(wc, stripWhitespace)

Now let us make our first word cloud

The syntax is as follows – wordcloud( words = corpus, scale = physical size, max.word = number of words in cloud)

wordcloud(words = wc, scale=c(4,0.5), max.words=50)


Now we have a word cloud, let’s add some more elements to it.

random.order = False brings the most popular words to the center

wordcloud(words = wc, scale=c(4,0.5), max.words=50,random.order=FALSE)


To add a little more rotation to your word cloud use rot.per

wordcloud(words = wc, scale=c(4,0.5), max.words=50,random.order=FALSE,

Finally, lets add some color. We are going to use brewer.pal.  The syntax is brewer.pal(number of colors, color mix)

cp <- brewer.pal(7,"YlOrRd")
wordcloud(words = wc, scale=c(4,0.5), max.words=50,random.order=FALSE,
 rot.per=0.25, colors=cp)





QLIK: Build a Dashboard Part 2 – Formulas and Set Analysis

This is part two of my Qlik Sense tutorial on how to build a dashboard. In this part I will be covering some more advanced topics, namely formulas and set analysis.

If you want to play along, but have not taken part 1 yet, here is a link to part 1: QLIK: Build a Dashboard Part 1


Let’s start with formulas. Open up your Dashboard and hit the edit button to get to the editor


Now click on the Sum(Scheduled) KPI we built. Next, on the right side bar, click Data > the arrow by Sum(Scheduled)


Now click on the orange fx – this will launch the expression editor


This is the expression editor. It is currently summing up the values of the Scheduled column. Scheduled is a binary with 1 meaning scheduled inspection and 0 not scheduled. So summing up this column will tell me how many scheduled inspections I have in my data set.


Let’s alter the expression a little. Add ‘/100‘ to the end. This will divide our expression by 100


Here is the result


Now, let go back into our expression editor. Notice the menus on the right. The aggregation menu tells us the options available for aggregation. Let’s use Count


Delete the current expression and start typing Count(WorkOrderKey), you should notice a helper box popping up asking if this is the field you are looking for.


Notice the colors in your expression. Blue is a key word while gold is a field name from your data


Hit apply, now you have a count of all work orders in your data


Got back to the expression editor one more time, and type the following:

Count(WorkOrderKey) – Sum(Scheduled)

This will give us the total work orders – number of scheduled work orders. The result being total unscheduled work orders.


Set Analysis

Set analysis is Qlik’s way of localizing filters. If I were to apply a filter of Scheduled = True to my dashboard, all charts and graphs in my dashboard would be filtered to only show Scheduled work orders. But what if I didn’t want that. What if I only want to filter my bar chart by Scheduled work orders? That is where set analysis comes in.

The syntax for set analysis is {<filtering expression>}

Bar Chart Part 1

Lets do one, and you will better understand. Click on the bar chart > Measures>fx to open expression editor


Now inside the brackets of the expression add the following: {$<Scheduled={1}>}

Your final expression should look like: Count({$<Scheduled={1}>}WorkOrderKey)

  • Count( – count number of elements inside brackets
  • {  – start of set analysis
  • $ – this means I  am going to filter on the data currently on the dashboard. So if I have Hospital A checked on my filter list, I am only going to look at data from Hospital A. If I wanted to use all data, regardless of active filters on the page, I would replace my $ with a 1
  • <> – our set analysis lives inside the carrots
  • Scheduled = {1} – filter my data to only show rows with Scheduled = 1 ** note that I have placed 1 inside {}. This is a syntax requirement. The argument must be in {}
  • } – close out set analysis
  • WorkOrderKey – the element I am going to Count
  • ) – close Count

End result. Only counting Scheduled Work Orders


Bar Chart Part 2 – date filtering

Now we are going to add one more element, and I promise, this will be the most difficult thing we tackle here. And it actually looks a lot worse than it actually is.

Go back into your expression editor and type this


Looks horrible right? Well, let’s break it down.


Everything in blue was covered in the last example, so we can get rid of that.

Now we just have this.


autoCalendar is a feature Qlik adds to your data so you can easily work with parts of a date field. RequestedOn.autoCalendar.Month is simply the month from the field RequestedOn. So 9/28/15 = September.  To simplify the explanation, I will be replacing RequestedOn.autoCalendar.Month with Month

Month = {“>=$(=date(max(Month)-2))”}

  • Month =  — filter by Month
  • { — remember 1 had to be in {} for schedule. The argument must be in {}
  • “>=  — >= means greater than or equal to. We need the ” because otherwise Qlik would think > meant end of set analysis
  • $ – again this means only filter active data currently on the dashboard
  • (=date(max(Month) – (=date(Max()) – these are built in functions that find the max value in the data set from field given – Month and return the value as a date value not a string
  • -2 — go back 2 month from max date

So, all the big ridiculously looking expression says is – Count all scheduled work orders in the data set that have a Requested On date >= to October    ( December – 2 Months)

Still on the bar chart, now click Add under Measures


Now click fx to enter expression editor


The the following:

Count({$<Scheduled={1}, Closed={0},RequestedOn.autoCalendar.Month={“>=$(=date(max(RequestedOn.autoCalendar.Month)-2))”}>}WorkOrderKey)

I have highlighted the only new code added. Closed = {0} means the work order is still open. So I have added a second measure that only counts open scheduled work orders

So now if you look at your graph, you have 2 bars per month.The blue is our first expression and pink is our second. So we now have all Scheduled Work Order and Open Scheduled work orders for each month for easy comparision


Gauge – How many pumps are in the shop?

Grab gauge from the left bar and drag it to the bottom of your sheet.


Click Add Dimension > Fx to go to expression editor


Now type the following:

count({$<ClassCode={‘PUMPINFU’}, Closed = {0}, Scheduled = {0}>}WorkOrderKey)/count(distinct{$<ClassCode={‘PUMPINFU’}>}EquipmentKey)

Again, don’t let it intimidate you, let’s break it down.

First, notice that what we have here is 2 counting expressions – one divided by the other

count({$<ClassCode={‘PUMPINFU’}, Closed = {0}, Scheduled = {0}>}WorkOrderKey)/count(distinct{$<ClassCode={‘PUMPINFU’}>}EquipmentKey)

The first expression simply states – Count WorkOrderKeys of all rows where Closed is 0 and Scheduled is 0 and the ClassCode = PUMPINFU – this means count all rows with open unscheduled work orders where the piece of equipment was an infusion pump.  (** PUMPINFU is just the class code we use in our database **)

The second expression says count all rows contain infusion pumps. I added the distinct key word so that any individual piece of equipment that appears more than once in my list won’t be counted twice by accident.

So, my expression is – Number of Open Infusion Pump Work Orders / Number of Infusion Pumps

Now, lets make it look nice.

On the right bar, set Number formatting to Number


Chance Formatting to % with 1 decimal place


Now click Appearance, set our max Range Limit to 0.3 (30%), and click use segments


Click Add limit, use scroll bar or just type in 0.1


Hit Add limit again, set it for 0.2


Click on any of your boxes now and you will get color options


Now I did tradition green, yellow, red, but you can use any color combination you want.


You now have all the information you need to finish the dashboard on your own – which is really the best way to learn. Go back, look at my examples. The set analysis really is the hardest part to worry about for this dashboard. If you get stuck, Qlik has a great user community that can answer most questions. Or, you can leave a question in the comments or email me at Analytics4all@yahoo.com






QLIK: Build a Dashboard Part 1


In this two part lesson, we are going to build the above dashboard using QLIK Sense. If you don’t already have QLIK Sense, you can download a free trial here: Download

You can download the data set I am working with here: WO

Create new app

Once you have QLIK Sense up and running, click Create new app


Name you new app. I am calling mine DASH



Click Open App

Next click Add data


Now select Excel files – I created my real dashboard using the SQL connector, however it is easier to load the Excel files. So I am using Excel in this lesson. I will show how to load SQL into QLIK in another lesson.


Navigate to the folder you saved the downloaded files to and select WO.xlsx


Note – you can uncheck columns you do not want to use from your data, but I have already removed all unwanted columns for this exercise.


click Load data and finish on the bottom


Once data loads, click Edit the sheet


Now we have a blank sheet to work with


Build the Dashboard

Click on Bar chart and drag it into the upper right corner

You will now be asked for a Dimension and a Measure. If you are not familiar with these terms in data, a dimension is like a category while a measure is something you can add, count, etc.


For Dimension, we will select RequestedOn > Month


For Measure, select WorkOrderKey

qlikDP10.jpgNow select Count


Now resize your bar chart by dragging it by the corner.


Next, drag a KPI over from the options in the left column. Place it to the right of your Bar Chart

As a measure, select Scheduled > SUM


Now select Filter pane from the left column and place it next to your KPI. For dimension, select FacilityCode

qlikdp15.jpg Now, let’s drag over a Line Chart. Place it below your bar chart. Set Dimension to RequestedON> Date. Set your measure to WorkOrderKey> Count


Now, let’s add a Pie Chart to the right of our line chart. Set Dimension to DepartmentCode and Measure to WorkOrderKey > Count


Now lets clean it up. Click in the upper right corners of your charts that say Click to add title. Give your charts some titles.



Once you have done that, click the Done button in the upper right corner.


Now play with your new dashboard.


In the next lesson, I will show how to add more versatility to your dashboard through used of formulas and Set Analysis.


QLIK: QLIK Sense Dashboard

This article here is more of a show and tell than a how to lesson. I wanted to demonstrate a really cool product that I have been demoing for work called QLIK Sense. I work for a Clinical Engineering Department at a multi-site hospital system. While the main thrust of my job is data related, the department as a whole is responsible for the maintenance of medical equipment, everything from IV pumps to MRI machines. Anyone who has ever worked into a busy maintenance/service environment knows how difficult it can be to optimize labor so that proper resources are allocated to the right jobs.

One great tool that can help is an operations dashboard. This gives everyone in the shop a single point of reference for what is happening at any given time. Using QLIK Sense, I was able quickly to connect my work order database and within less than an hour, I had the following dashboard created.


Closer Look at the Elements

Across the top I have, Scheduled Inspections (Total vs Still Open), Repairs Calls Opened Today, Repair Calls Closed Today, and Open High Risk Equipment Repair Calls


Next I have a trend line showing work orders created vs closed over time. Next to that, I have a pie chart showing what departments the majority of my repair calls are originating from.


On the bottom, I have a gauge that shows the number of IV Pumps in the shop for repair vs the total number of pumps in Inventory. The goal is to keep that number below 20%. So as the needle starts edging towards red, we know to move resources onto pumps.

Finally, I have open repair work orders by employee.


Cool Features

QLIK Sense provides more than just a dashboard. It is totally interactive. You can use basic filters like the one below:


Or you can simple click on any element with the dashboard to filter the data.

Notice that after selecting Monitor Technicians from the Department Pie Chart, my Repair Work Orders by Employee chart has automatically filtered down to only the employees with work orders open in that department.


With the trend line graph, I can use the “lasso” to select a range I want to filter by.


Of course you can simply “fly over” element in the dashboard to get information as well.


Now I have an effective BI tool

I know a lot of people out there are still living in the world of static reporting. Getting near real time information up in front of the decision makers can allow them to make near real time decisions. The days of  basing decisions off of a static report run daily or even (gasp.. ) weekly are over. We need to start deploying tools like this dashboard so we can manage less by what we think is happening and more by what we know is happening.


R: ggplot using facets

In this example I am going to teach you to use facets to add another level to your ggplot data visualizations. In this example we will be working with the ChickWeight data set from R Datasets

First, let’s check out the data by running head()



We have 4 columns in our data, weight, Time, Chick, and Diet.

Now, let’s build our ggplot model. Below, we are setting our model to a variable “p1”. We then set ChickWeight as our data set and the weight column to our x axis. Remember, nothing will happen when running the line below.

p1 <- ggplot(data=ChickWeight, aes(x=weight))

Now lets graph our plot as a histogram – we will set our binwidth to 10 – if any of this confusing you, go back and check out my earlier lessons on ggplot and ggplot histograms

p1 + geom_histogram(binwidth = 10)


Let’s now add some color. I am going to set our “fill” to be Diet

p1 + geom_histogram(binwidth = 10, aes(fill=Diet))


Now, using facets, I am going to take it one step further and separate this into 4 histograms, one for each diet.

 p1 + geom_histogram(binwidth = 10, aes(fill=Diet)) + facet_grid(Diet~.)


The Code

#-- Look at data


#-- build ggplot model
p1 <- ggplot(data=ChickWeight, aes(x=weight))

#--plot ggplot as histogram
p1 + geom_histogram(binwidth = 10)

#-- set fill color to Diet
p1 + geom_histogram(binwidth = 10, aes(fill=Diet))

#-- create four histograms - one for each Diet
p1 + geom_histogram(binwidth = 10, aes(fill=Diet)) + facet_grid(Diet~.)




R: ggplot – Histograms

Let’s make a Histogram using ggplot

First step, import the ggplot2 library


Now let’s look at our data. In this example I am using chickwts from R Data




As you can see above, this data contains 2 columns weight ( numeric) and feed(Factor with 6 levels)


In a histogram, we don’t need to worry about assigning a Y axis, the Y axis is the frequency count of our X variable.

Let’s set our data and x axis in ggplot. Let’s also assign it to variable.

pl <- ggplot(data=chickwts, aes(x=weight))

Now we can use the assigned variable in conjunction with our geom()

pl + geom_histogram(binwidth=10)

In our geom, I set a binwidth. This tells us how wide to make our bars. Setting binwidth to 10 makes each bar 10 units wide.


Let’s give it some color

pl + geom_histogram(binwidth=10, fill="blue")


Now, let’s set our colors based on our 2nd column “feed”. Note how a legend is automatically generated.

pl + geom_histogram(binwidth=10, aes(fill=feed))


For the finishing touch, let’s add some black bordering around our boxes.

pl + geom_histogram(binwidth=10, aes(fill=feed), color="black")


The Code



#-- look at structure

#set data and x axis value
pl <- ggplot(data=chickwts, aes(x=weight))

#create histogram, binwidth 10
pl + geom_histogram(binwidth=10)

#change bars to blue
pl + geom_histogram(binwidth=10, fill="blue")

#set color of bars by feed column values 
pl + geom_histogram(binwidth=10, aes(fill=feed))

#add black border around boxes
pl + geom_histogram(binwidth=10, aes(fill=feed), color="black")

Qlik Sense: A first Impression

So I am trying out Qlik Sense as a possible BI option for work. I have some limited experience (through grad school) working with another one of their products Qlik View. I will say right from the get go, Qlik Sense is much more user friendly.

Opening it up, I get the following splash screen


Qlik then asks if I would like to Create a New App.qlikSense1.jpg

I click Create A New APP and I get a window to name it.


It says New app created, and asks if I want to open my app


Now I am brought to a page to add my data. I have two options, one a more GUI driven method, and the other that will allow me to create my data model using Qlik’s script (very similar to SQL).

I am going to go with the GUI method for now.


I choose Excel files


Next I select a file QlikTEst.xlsx that I put together for this test drive.


My file appears in preview mode. Notice I am able to select both sheets to load and unselect columns I don’t want.


I click Prepare Data at the bottom and I am brought to this screen. Notice the red triangle warning on the bottom of the screen. It says I have possible associations.


I click the warning and I now have a recommendation for an association to make between sheets. My Equipmentkey column matches up(53%) on both sheets.


Just double click the Recommendations box and your pages are now joined


Now hit Load data


Qlik now brings you to a work sheet.


I want to create a bar chart, so I drag one in from the menu column.


Now I pick my Month as my dimension


And I choose WorkOrderNo as my measure


Now I choose an Aggregate of Count()


And I have a bar chart.


Now, let’s add a filter – drag it in from the menu column


Select FacilityCode as my dimension


I can adjust the filter size by clicking on these points


Now hit done at the top of the screen


And I now have a live interactive bar chart.


My Thoughts on Qlik Sense

Qlik Sense is insanely easy to get up an running. You really don’t need to know much more than how to drag and drop. However, if you are like me and enjoy getting under the hood, Qlik does allow you custom code your data models and formulas.

My only complaint, and this could be because I am new and don’t know a work around, is having to hit Done to leave the edit screen before I can test the interactivity of my charts.


Python: Line Graph

Let’s build on what we learned in : Python: Intro to Graphs

First, import pyplot from matplotlib

Remember %matplotlib inline let’s you see your graphs in the jupyter notebooks


Line 1

Note I am using a list comprehension to fill the x axis.


Now, let us do some formatting:

  • ‘r:’    –   red dotted line
  • label = ‘up’   –  adds label to the line
  • py.legend(loc=9)  – adds a legend to the chart


Line 2

For line 2, I want to create a reverse curve:

  • d = u  – list u is copied to d
  • d.reverse() – reverses values in d


Plot the line.

  • ‘g-.’ – green dashed line


Combine the two lines


Line 3

Here I use another list comprehension  and zip to make our last like. This adds each element of list u and d in order.

  • ‘b-‘ – blue solid line


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Python content: Python

Python: Intro to Graphs

Visualizations are big part of analytics. You will need to produce visually engaging graphics for presentations, reports, and dashboards. You will also make graphs for your own use in data discovery and analysis. As bonus, unlike data cleaning, data viz can be pretty fun.

Matplotlab and Pyplot

Matplotlab is a module you can import into Python that will help you to build some basic graphs and charts. Pyplot is part of Matplotlab and the part we will be using in the following example.

**If you are using the Anaconda Python distribution, Matplotlab is already installed. If not, you may need to download it from another source.

Line Graph


  • %matplotlib inline – this code allows you to view your graphs inside jupyter notebooks
  • from matplotlib import pyplot as plt – here we import pyplot from matplotlib into our program (note, we only want pyplot not all the functions in matplotlib).Adding “as plt” gives us a shorter alias to work with
  • age and height lines – fill our lists with age and height information for an individual
  • plt.plot(age, height, color = ‘blue’) – here we tell Python to plot age against height and to color our line blue
  • plt.show() – prints out our graph


Bar Chart

For this example, we will make a bar charting showing ages of 4 people.


  • You should understand the first few lines from the first example
  • count_names = [i for i,_ in enumerate(name)]  – since the name list is a list of strings, we cannot really graph that onto a chart. We need a way to convert these strings into numbers.

Wait? What does for i,_ mean? Let’s jump to the next code sample


While you don’t see it when making the list, a Python list is technically a list of tuples (index number, element). So if instead of i,_ we asked for both elements in the tuple, (i,j) we would get the following.


So by iterating by for i,_ we only return the first element in the tuple (the index)

** notice we are using a list comprehension. If you are unfamiliar with list comprehensions, check out my earlier post: Python: List Comprehension

Let’s clean up our bar chart a little now.

  • plt.ylabel(‘Age’) – label the y-axis Age
  • plt.title(‘Age of People’) – give the graph a title
  • plt.xticks([i+0.5 for i,_ in enumerate(name)], name) – this label function is using a list comprehension to first chose the position on the X-axis, and name provides the person’s name for the label.


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT

Follow this link for more Python content: Python