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