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.


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



This will give you the following Bar Chart


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.


Let’s go to the Data load editor


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


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


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.


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.


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


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


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’


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






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.