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