Author: Chris Casby

Using Extensions and Custom Objects to Supplement your Qlik Sense Experience

 

by Chris Casby

Hello everybody! I am back after a not-so-restful hiatus! I know, I know, it’s been a few months, maybe more. But you know how life can get in the way of things. It’s been a bit of a bumpy ride getting accustomed to my new living and working situations, but here I am! I know that you have been anxiously awaiting yet another post filled with alliterative advocacy of all aspects of awful writing, but I promise, that was it. Now that we’ve said our hellos, let’s get to the meat of today’s post: EXTENSIONS!
 
Today, I am going to introduce you to a simple extension for Qlik Sense and how to add it to your toolbox to get the most out of your app, and in this case the user! This particular extension allows us to add an Input prompt to our UI and define a dynamic variable with it. When the user types in the prompt box, the entered value defines a variable that we can then use in other chart objects within our UI.
 

First, click this link:
Input Box which updates a Variable in Qlik Sense created by Sangram
 
It takes us to a Qlik Branch offering by Qlik user Sangram Reddy. At the link above, you will see a quick description of the extension and multiple comments with questions that Sangram happily answers and actually goes to great length to help users with issues they are encountering. If you want to skip that, just click this link for the actual extension.
 

Sangram’s Share Folder
 

The link will take you to a OneDrive that Sangram had made available to the public. Click on the icon labeled ‘SR_Input_Box’. It should take you to a screen that looks similar to this:
 

 

Make sure that you click all the checkmarks in the top right of each file and click download at the top, or right-click on your mouse and scroll down to download. You should see this:
(NOTE: Yours will, in all likelihood, not be called this. It is a dynamic and generic naming convention. Yours should read OneDrive – date you downloaded this.zip.)
 

 
Make sure the ‘Save File’ radial button is selected and click ‘OK’. Open your download folder in your Windows Explorer. It should look something like this:

 

 

Notice the green highlight in the image. If your screen looks like the above image, you know that we’ve opened the Download folder and the text highlighted in green is our path. We will be referencing the path again, so just make a mental note of where it is.
With all Qlik extensions, we need to make sure that it ends up in the correct folder so that Qlik can find it. We want to make sure the OneDrive-2017-04-19.zip file is appropriately relocated to the Extentions folder in our Qlik folder. Highlight the zip file by clicking on it. Right-click and scroll down to ‘Cut’. On the right hand side of your Windows Explorer window, you should see the various locations and folders on your computer or network. Find where your Qlik documents are stored and open that folder. Mine are stored in my Documents folder under This PC. Double-click the ‘Qlik’ folder, then ‘Sense’ and then ‘Extensions’. Once you’re in the Extensions folder, right-click in an open space and scroll down to ‘Paste’. Your zip file should appear. Your screen should look like this:
(NOTE: Notice the path highlighted in green.)
 

 

Now, right-click on the ZIP folder labeled OneDrive-2017-04-19.zip and scroll down to ‘Extract All’ and select it. Your screen should look something like this, with the exception of the file name:
 

 
DO NOT CLICK ‘EXTRACT’! I just wanted to make sure we were still on the same page. We will be extracting the file, but we want to name the folder something other than OneDrive-2017-04-17 or whatever date your file has as its name. Because we want to know what extention it is, delete the OneDrive zip file name and Type ‘InputBoxSR’. That will be the new folder name and make it easier for us to identify should we need to find it in the future. Your screen should look like this:
 

 

Click ‘Extract’.
Here I have both folders opened and cascading to show where your extension is located and the files that the extension is comprised of.
 

 

Now let’s put our new extension to work! If you have Qlik Sense open already, close it and re-open it. Open an existing application or create a new one. Click the ‘Edit’ button at the top. In the assets panel on the left of your screen, you should see a little jigsaw puzzle piece under the ‘Charts’ icon. This is the ‘Custom Objects’ icon. If you click it, you will see a dropdown menu appear that should look something like this:
 

 

For the purpose of this example, I wanted to keep things fairly simple. We will construct two chart objects. The first will be an input box, using the extension we just installed, that will prompt the user to give us a value. The value the user puts in will define a variable. The second chart object we create will use the variable that was defined in the input box.
To construct the first object, simply click the ‘Custom Objects’ icon, scroll down and double-click ‘Input_Box_SR’. You should see your input window appear in your UI workspace as shown here.
 

 

You should see the Update Variable Name window in the properties menu on the right side of your screen. In the image, I’ve already populated the variable name as vInput. Feel free to use whatever variable name you are comfortable with. Below that you should see the default(when empty) window. I’ve entered ‘0’. The value in this window is what will be applied to the variable if the input box is not populated by the user.
The second chart object will be a just a text box displaying the variable that we defined with the input box, vInput. (Notice, the chart object properties panel indicates that the named variable will be updated. You will need to create a new variable, in this case called vInput, and give it no value. The value will be updated with the user input value.)
This is what your screen should look like.
 

 
Notice the expression. We’ve used the dollar sign expansion and the vInput variable to be calculated for this text box.
When we go into the UI, if we enter a numeric value into the input box, the text box should populate with that value. Let’s check it out.
 

 

Looks like it works! This should be useful in building dashboards where you need input from the user to identify what data to display. You can reference the variable you define in the Input box in your expressions. As our simple chart object illustrates, as you change the value in the input box, expressions calculated with the defined variable should change, as well!
I hope you found this useful, either as a simple introduction to extensions or as a new tool for you to use in your visualizations! Until next time!

 
If you missed my first blog, you can check it out here.
 

Adding Maps to your Qlik Sense documents using Free Geo data

by Chris Casby

We all work in IT. Or in BI with BOs (Business Intelligence and Business Objects, respectively). We all report to a CTO who generates ROI justification reports for your much needed UI/UX requests and submits them to over-worked/over-stressed/over-clocked CFOs who have yet to approve your T&Es from last month. All the while, we, as willowy wanderers weave our way, without wane, through the worldwide web of acronyms and entitled titles to secure our spot on the most sacred of script: the payroll.

But aside from our never ending aspirations of ascension and acquisition of accolades and admiration amongst our peers and superiors alike, we find a tiny ray of light: the quest for knowledge, to know what to know for the sake of knowing. To become a more well–versed version of ourselves compared to yesterday’s model. And maybe apply it.

My apologies. Let me introduce myself. My name is Chris Casby. But most of my friends call me Casby. I’ve found that most people I have encountered in my life know anywhere between six and 500 different guys or girls named Chris, so I learned in high school that Casby was probably just easier if I wanted to be remembered. I can get into my hobbies and favorite authors and all that but really, that’s not why you are here.

I began my Qlik quest about two years ago. At that time, I had found myself stagnating in a soul sucking job in financial corporate America. It paid the bills, but not in any way that I was particularly proud of. So I stepped out of my comfort zone to explore the world of business objects and data visualizations, hoping against hope that I’d possibly be able to apply some of the little knowledge I’d acquired in my 5 years in college and my BS BS in Mass Communications with a concentration on Creative Advertising. And one thing that I can say with 100% confidence is that 100% confidence is rarely enough to get you by in the tech world. It may get you in the door, but at some point, people actually expect you to know what you are talking about, which was a novel concept for me, having worked for a credit card company for just shy of 4 years.

But in the past two years I’ve learned a few things, mostly from people who know more than me. Not least of which is that there are lots of people in the world that know more than me. And many of them just want to help people to know more. These are people who have invested their blood, sweat and tears into acquiring wells of knowledge, and are offering those wells to anyone willing to accept them. As I continue to learn, I’ll hopefully impart some of what I’ve learned to someone who is greener than myself.

So when I was first tasked with building a map chart for a dashboard, I had no idea what to do. But I knew exactly where to turn: Google!! However, I quickly realized that the Qlik Community was where I should have clicked right off the bat, as the first twenty links Google produced contained ten or fifteen links to the Qlik Community.

I was tasked with mapping Vendor information. In this scenario, my client used Qlik Sense as their Qlik platform of choice. I had access to the vendor addresses, which included their zip codes. But I found that I needed the latitude and longitude to utilize the Map chart. Latitude and Longitude information was not part of my data set so I was off to find it! After researching several options, I landed on the free database offered by GeoNames. Their geographical database contains latitude and longitude information for all countries and is available for download for FREE! Bonus that the data appears to be refreshed daily. The data I used can be found here.

GeoNames Database

Because I had access to the Vendors’ addresses and zip codes, I knew that I wanted to link the zip code with the latitude and longitude information in my data model. (This required a bit of clean up in the vendors table, but I will go into that in a different post.)
Here is how my table appears in the Table viewer. You can see what fields I loaded from the source data. There is more information available, but I only needed these fields for my task, so it’s best practice to avoid loading superfluous data as it may slow down processes.

Now that we had the latitude and longitude information for the client sites, we could use the GeoMakePoint function to generate coordinates. This is how it looked in my script.

Once we have these coordinates, we use that as the dimension in our Map object.


Here we are selecting the Map object from our different Chart options.
In the image of our script above, you see that we used the GeoMakePoint function to group the latitude and longitude into one value and named it Location. So in the image below, we are using it as our dimension.

Once we’ve selected our dimension we are prompted to choose a field as the identifier for the points. This image is that prompt and we are selecting place name, as that is how it was loaded from the database.

And this is the finished product! We put a text box along the top of the map with an expression showing us the number of records. This image has no filters selected, so we know that there are 594,584 records. And they are shown as points on the map. Along the right, we added a table of two list boxes to further filter the depiction of records on your map: One for country, and one for city.
As filters are selected, the number in the text box will decrease, as will the number of points on the map, representing your selections!

As we discussed earlier, there are a few ways to get a similar effect in the Qlik platforms. My hope is to contribute regularly to this blog and explore some of the different ways to make data visualizations more fun and readily available to everyone of all skill levels. And maybe help a few people along the way.

If you’re still here, thanks for reading! Feel free to comment below or email any thoughts to chris@foreshadow.com. I hope I’ll be blogging again at you soon!