Over the years I’ve gathered a number of bits of code that I have reused, across both QlikView and Sense. Something that is always helpful is allowing users to pick expressions and/or dimensions in their applications. Recently I decided to pull the most useful techniques into a single app, which you can download and use. This post introduces the QI Instant Sense Application and describes a bit about how it works.
Why Create an Instant Sense Application?
In my time as a Qlik developer, I have seen many attempts at a one-size-fits-all application, where you simply load different data into the one application shell and off you go. To my mind, this is never going to deliver an application that works for the people who use it. A great application can only be created when the user is carefully consulted and involved in the design.
Something that we perform frequently at Quick Intelligence is “Seeing Is Believing” type Proof of Concepts. This is where we take a set of data from a prospect, then show them an application back within a few hours. Sense makes this achievable because of how quick charts can be built. However, there is a lot of repetition and copy and paste to create an app which ultimately will (or at least, should) be superseded. This was the main driver behind building the Instant Sense Application.
What Does The Application Do?
The idea is that the template app has twelve sheets, each with different ways of viewing the data. These follow the DAR principle (Dashboard, Analysis, Reporting) with KPIs up front, breakdown of those numbers in charts, and then finally tables of data. An overview of the various sheets is shown here.
Any data can be loaded into this template by taking a copy of the app and modifying the load script. One issue, usually, with doing this is that the objects in the app will not relate to the data that has just been loaded. This app, however, also loads in metadata from a spreadsheet, which defines which fields in the data model are used as different types of dimensions (main, trend, pivot, and geographic) and then a list of expressions that can be done of the loaded data is also included in the spreadsheet, along with how each expression should be displayed.
When the application is reloaded, the filters and charts pick up the meta-data and all of the chart objects work. More than that though, the user can then select which dimensions and measures they want to see and flex the application even after reload.
I’ve produced a short video showing both how the application is used and how the metadata spreadsheet drives how the application looks and behaves.
So, How Does it Work?
If you are a regular reader of this blog, you will know that I try to share tutorials on how to build solutions. Whilst you can just download the Instant Sense App and go, I wanted to give some detail on how it has been constructed. The truth is, though, that most of the techniques used in putting together this app have already been shared on this blog. This section includes links to where you can find that information and gives a bit of context around how those techniques have been applied.
One of the key features of the application is the ability to select Dimensions and Measures from a filter pane. When Sense was released and no Cycle Group was available I put together this blog post on creating an alternative cycle group using a filter pane:
The technique involves loading a list of dimension names and then using the min function to pick the first dimension in the selection and then return that. This can then be used as a calculated dimension in a chart, table, or even filter. The min function also has the ability to pass a parameter, so the nth minimum dimension can be found. This is what is used to give charts over six different dimensions.
The expressions are a little more tricky as there are three parts to the expression: the name, the Qlik expression, and the format specifier. It is just a case of finding which the minimum expression is (exactly the same as the dimension), then each of the parts is looked up separately and then concatenated together.
You will note from the video that there is a lot of repetition in this app, the eight KPIs on the front sheet, six charts with different dimensions on the second. These could have been done with a bit of copy and paste and editing. Changes, however, would then be a nightmare. Instead, variables are used, but rather than having a separate variable for each chart or KPI, there is a variable for each type which takes a parameter, 1 through 8 or 1 through 6, depending.
This technique of passing parameters into variables is a great timesaver. This blog post describes how you can use it in your own applications:
Combinations of these two techniques drive the charts and filter panes across pretty much all of the sheets in the app.
Another feature worth mentioning, though, is the showing and hiding of fields in the straight table object. Both the dimensions and the tables are created as described above, with variables that take parameters to return the dimension or variable. There are six dimensions and six expressions by default. The user can choose any number of each, up to six. Columns are then hidden as required, using a show condition on the column. Where this is particularly useful is on the dimensions, so with six columns you will have rows for each unique combination of values. If you pick one dimension, the other columns are hidden and the table will aggregate so that only one row per distinct value in the selected dimension is shown. This is incredibly useful for custom tables.
The final sheet is the Quick Intelligence Data Profiler. This is a set of objects that can be copied and pasted into any app that allows you to pick any field in the data model (from the $Field system field), which then shows a list box and bar chart showing frequency of values within the field. This is something I have put into pretty much all of my Sense apps since the product was released, and in QlikView before that. Most Qlik consultants will have their own go-to version of this kind of sheet. A tutorial on how to build one like mine is here:
So, now you know a bit about how it works, why not download the app and have a go yourself?
Download the App
The application can be downloaded from https://www.quickintelligence.co.uk/isa/. We do ask for an email address before you download the app, so we can notify you of upgrades. Please don’t pass the QVF onto others, but send them to the link given here. There is no charge for the app, but please credit Quick Intelligence if you use it largely unchanged.
The data in the app is from the Magic The Gathering API, just to provide some non-contentious data so you can see it work out of the box. If you are interested in loading data from open REST APIs, then the code is all in there.
If you point this app at an open data-source that is available to all, and you are happy to share your work, please get in touch. I would like to ship meta-data files and load scripts for a number of different open data sources to show the application in action.
Please feel free to get in touch, via the comments, if you have any questions.
Enjoy the app, I hope you find it really useful.
The scripts for both of these apps in included in the download, should you wish to see how the data is loaded or host the script on your own Sense instance.