Qlik Sense: Sorting by Expression

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

2016-12-29_10-49-34.jpg

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

2016-12-29_10-51-26.jpg

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

2016-12-29_10-51-59.jpg

Click on the Expression box and the formula window opens.

2016-12-29_10-52-30.jpg

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

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

2016-12-29_10-53-26.jpg

Hit apply and check your Filter box.

2016-12-29_10-53-39.jpg

 

QLIK Sense: If Then Conditional Logic

One place Qlik really shines in my opinion is in their data modeling. While Qlik Sense brought user friendliness to the mix by allowing automatic data model creation, it still allows almost unlimited customization to those looking to peek under the hood.

In today’s example, I am going show you how to utilize a simple IF THEN statement in the data model to make building your visualizations much easier.

You can download the practice data set here: qlikalarms

This data set is made up data modeled off of cardiac patient alarms. Let’s look at our data:

As you can see, we have 5 columns. Count is just a distinct number for each row, day = day of the week, alarm trim down1 = the alarm condition, Alarm Class = severity of alarm – Red is the most severe with INOP being the least, timeday = time of day the event occurred.

2016-12-28_09-54-14.jpg

The Task

The task at hand here is simple enough. We want to analyze alarms on the weekend versus weekday.

Loading the data in Qlik is easy enough. If you have any questions on how to do it, refer to my earlier tutorial on building a Dashboard: QLIK: Build a Dashboard Part 1

Once your data is loaded:

Go to the App Overview. Select your new sheet, hit Edit and grab a Bar Chart from the left utility bar. We are going to set the Dimension to Alarm Class and the Measure to Count of alarm trim down1

2016-12-28_09-42-49

2016-12-28_10-19-45

This will give you the following Bar Chart

2016-12-28_09-43-38

Now grab a Filter Pane and drag it onto the sheet. Set its dimension to day.

Now by selecting days from the filter pane you can effectively compare weekdays to weekends. However, it involves a lot of unnecessary clicking for the end user. Let’s try a better method.

2016-12-28_10-10-15.jpg

Let’s go to the Data load editor

2016-12-28_09-25-05

click on the Auto-generated section in the left pane.

2016-12-28_09-25-24.jpg

Next click on the Unlock box in the upper right corner. You will be met with a warning window. Just click Okay.

2016-12-28_09-25-39

Let’s take a look at the Load script. This script was auto-generated by Qlik when you uploaded the Excel file. Note that it looks similar to an SQL script. We are going to LOAD the columns listed below FROM the Excel workbook.

2016-12-28_12-17-24

What we are going to do next is add a new line to the loading script. This line will be an IF THEN statement.

The syntax is as a follows: if ( conditional statement, THEN, ELSE) as [NAME FOR NEW COLUMN]

In our example I am stating if day is equal to ‘Saturday’ or ‘Sunday’ then 1 else 0 and I am naming this new column Weekend

*** note the , at the end of [timeday]. Make sure you add that there. Qlik will throw an error if the correct syntax is not used.

2016-12-28_12-18-21.jpg

Now select Load data. If successful, go back to your App Overview > edit sheet. If not successful, check your syntax!!

2016-12-28_12-25-20.jpg

Let’s replace the day filter pane with a Weekend filter pane

2016-12-28_09-44-17

Now you can compare weekdays to weekends with just a single click. 1 for weekends and 0 for weekdays.

This still is not ideal. The goal of a good BI solution is usability. The end user should be able to dig into their data without having to spend too much time trying to decipher what built.

Let’s correct this. Go back to data load editor. Let’s change our IF THEN statement to read if day = Saturday or Sunday, then ‘Weekend’, else ‘Weekday’

2016-12-28_12-17-45

Click Load data and go back to your sheet. Notice the filter pane now shows Weekday and Weekend as your options.

2016-12-28_09-45-31

 

 

 

 

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

Formulas

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

qlikd2p1.jpg

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

qlikd2p2.jpg

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

qlikd2p3.jpg

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.

qlikd2p4.jpg

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

qlikd2p5.jpg

Here is the result

qlikd2p6.jpg

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

qlikd2p7.jpg

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.

qlikd2p8.jpg

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

qlikd2p9.jpg

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

qlikd2p10

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.

qlikd2p11.jpg

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

qlikd2p12

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

qlikd2p13.jpg

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

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

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

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

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

Now we just have this.

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

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

qlikd2p15.jpg

Now click fx to enter expression editor

qlikd2p16.jpg

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

qlikd2p17.jpg

Gauge – How many pumps are in the shop?

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

qlikd2p18.jpg

Click Add Dimension > Fx to go to expression editor

qlikd2p19.jpg

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

qlikd2p20

Chance Formatting to % with 1 decimal place

qlikd2p21.jpg

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

qlikd2p22.jpg

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

qlikd2p24

Hit Add limit again, set it for 0.2

qlikd2p25.jpg

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

qlikd2p26.jpg

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

qlikd2p27.jpg

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

qlikDash

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

qlikDP1

Name you new app. I am calling mine DASH

qlikDP2.jpg

 

Click Open App

Next click Add data

qlikSense4

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.

qlikSense5

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

qlikDP3

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.

qlikDP4

click Load data and finish on the bottom

qlikDP5

Once data loads, click Edit the sheet

qlikDP6.jpg

Now we have a blank sheet to work with

qlikDP7

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.

qlikDP8.jpg

For Dimension, we will select RequestedOn > Month

qlikDP9

For Measure, select WorkOrderKey

qlikDP10.jpgNow select Count

qlikDP11

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

qlikDP12.jpg

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

qlikDP14

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

qlikdp16

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

qlikdp17.jpg

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.

qlikdp20

Now play with your new dashboard.

qlikdp21.jpg

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.

qlikDash.jpg

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

qlikDash8.jpg

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.

qlikDash9.jpg

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.

qlikDash10.jpg

Cool Features

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

qlikDash11.jpg

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.

qlikDash3

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

qlikDash1.jpg

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

qlikDash5.jpg

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.

 

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

qlikSense.jpg

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.

qlikSense2.jpg

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

qlikSense3.jpg

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.

qlikSense4.jpg

I choose Excel files

qlikSense5

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

qlikSense6.jpg

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

qlikSens7.jpg

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.

qlikSens8.jpg

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.

qlikSens9

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

qlikSens11

Now hit Load data

qlikSens12.jpg

Qlik now brings you to a work sheet.

qlikSens13.jpg

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

qlikSens14.jpg

Now I pick my Month as my dimension

qlikSens15.jpg

And I choose WorkOrderNo as my measure

qlikSens16.jpg

Now I choose an Aggregate of Count()

qlikSens17.jpg

And I have a bar chart.

qlikSens18.jpg

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

qlikSens20

Select FacilityCode as my dimension

qlikSens21.jpg

I can adjust the filter size by clicking on these points

qlikSens22.jpg

Now hit done at the top of the screen

qlikSens23.jpg

And I now have a live interactive bar chart.

qlikSens24.jpg

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.