Add sense for Excel – import Qlik Sense data to your Excel spreadsheets

I’m happy to announce that Add sense for Excel is now available in Microsoft App Source.

Add Sense excel
Plugin for Excel online and Excel2016. Allows you to quickly import your Qlik Sense data into an Excel spreadsheet

 

Add Sense is an add-in for Excel Online, Excel 2016 and Excel 2016 for Mac that allows you to quickly import datasets from your Qlik Sense apps into your spreadsheets.

  • self service import, just select and qlick – no programming required
  • multiple dataset, from multiple apps can be inserted into the same spreadsheets
  • data is organized in Excel tables, where you can work with them like any other Excel table, add formulas, create visualizations etc
  • the add-in saves metadata about the dataset, like what app it was from, when the app was loaded, what selections where used

The add-in can be downloaded from Microsoft AppSource here.

Setting up your Qlik Sense server is really easy, instructions can be found here.

 

 

Adding alternate state support to your Qlik Sense extension

One of the more advanced feature in QlikView is alternate state. It alllows you to have several different selections active at the same time. With set analysis expressions you can then combine your selection sets to gain valuable insights.

While alternate states are not exposed in the built-in Qlik Sense client, you can easily add them to your custom extension with just a few lines of code. Let’s see how.

Step 1: add a property panel field

The qStateName property already exists (check the Qix Engine API), so a first step is to map it to the property panel. Lets create a new section under “Addons”:

This assumes a HyperCube, $ is the default state which is always there. This is already enough for alternate state to work in your extension. If you enter a valid alternate state name in the property panel your extension will be connected to it. Problem is there has to be an alternate state in the app, and you need to know it’s name. Lets do something about that.

Step 2: add a listbox with existing alternate states.

Lets convert our property panel to a dropdown list, where the app developer can choose what state the object should be connected to. We do this by setting the component to ‘dropdown’ and adding a function to return the options:

Alternate states ar listed in the app layout structure, so we need to get the app layout and format the states in the format the property panel dropdown wants. The default state ‘$’ is not in the list, so we need to add that ourselves. For this to work you need to have the qlik module available in your extension.

Now you will have a dropdown list in your extension property panel, something like this:

Still doesn’t look much, does it… But it does it’s job. Now we just need a way to create those alternate states.

Step 3:Creating the alternate states

Well, actually you need to do this first. But there are different ways to achieve this:

  • there are extensions for this
  • you could do it in Engine API explorer, since its really a one-time thing
  • or, you could add it to the property panel.

The property panel is not really meant for this, since we are not updating the object properties, but the app properties, but still it’s pretty easy to do. You can add something like this to the property panel:

It’s really not more complicated than that. You will get an inputfield in the propertypanel. If the user writes anything in that field, an alternate state will be created. It will then show up in the listbox, so you can use it in your extension.

Conclusion

It’s really not at all difficult to add alternate state support to your extension. Probably you should do something to show the user that this chart belongs to an alternate state: you can use the header for that, or add some styling or an icon for charts that are in another state. I’ll leave that for you.

Accessing system data and variables in a Qlik Sense extension

Sometimes when you are building a Qlik Sense extension you need to get access to system data. This is espescially true if you are trying to build something a bit more generic. Perhaps you need a list of fields, or dimensions, or measures. Or you need to work with variables.

If you are new to Qlik Sense development you might look in the API documentation for methods to get the data you need. Don’t do that!! Qlik Sense extension model is based on the idea that you use one Generic Object, described in the extensions initialProperties, and modified by the user in the property panel, and possibly programmatically (but that’s really advanced). So while using the API methods to get additonal data is the approach to use in mashups or Web apps that access Qlik data, you should avoid using them in a masup.

Why you shouldn’t

If you do use these api calls in your extension, you will get problems:

  • you might easily get a memory leak, or a ‘Generic Object leak’, where you create lots of Generic Objects
  • you can easily lose contral of all callback functions running when the generic objects are revalidated
  • if the user makes a snapshot, the API calls will access the latest version of the data, not the one in the snapshot and possibly give the wrong data
  • if the user tries to export your extension to PDF or Excel it might break, since the service responsible for those export does not have access to live data, only to the snapshot

If you absolutely must use these calls, you should at least turn export to PDF and Excel of and not allow snapshots of your extension.

What you should do instead

Luckily there are alternatives. These API calls all create Generic Objects, but since the Generic Object is a very flexible structure, you can actually configure the Generic Object behind your extension to provide the data you need. Here is a little table of what you could use:

You needDo not use API callInstead add to initialProperties
List of fieldsapp.getList("FieldList")qFieldListDef
List of measuresapp.getList("MeasureList")qMeasureListDef
List of dimensionsapp.getList("DimensionList")qDimensionListDef
List of variablesapp.getList("VariableList")qVariableListDef
Variable valueapp.variable.getContent(..)qStringExpression or
qValueExpression

You find a working example of this in my syslist extension just don’t use it, it’s meant as examples of how to get the data and doesn’t really do anything useful. But do grab the initialProperties part you need for your extension.

It looks like this:

When should you use these API calls

Well, no rule without an exception. While you should avoid using these calls in the API for the rendering part of your extension, you should use them in your property panel, if you for example need to provide a list of fields to the user. And in a mashup, they are definitely very useful. And the API has other calls, which you might want to use, but that vill mainly be when the user does something, like clicking a button etc.

Qlik Sense variable extension – new version

Example of Variable use included in the extension

A new version of my Qlik Sense Extension Variable is now available.

No new functionality, but some minor internal changes:

  • revised build, CSS is no longer a separate file, but bundled in the javascript file
  • reverted back to using the Capabilities API
  • some new examples of use

This release is part of the preparation for the certification of the extension. Read more about that here. This extension is however still not certified.

Exporting Qlik Sense extensions to Powerpoint and PDF

Update 2020-06-27: Qlik sense has changed since I originally wrote this, up to date version of using a Promise can be found here:

Printing Qlik Sense extensions

One of the great new features in Qlik Sense is snapshots and storytelling. It allows you to create a snapshot of your visualizations, with the current selection state, and then include the snapshot in a story. Behind the scenes Qlik Sense also uses snapshot for export to PDF, PowerPoint and image. This makes it even more important for you to make your extension work well with snapshots. Sometimes this is however not easy…

The basics

To turn snapshots on, you need to include a few lines in your extension:

This will give you a context menu in the client with alternatives to create snapshot and export the visualization. That might be enough, but it might not be enough. To verify it you should:

  • create a snapshot of your extension, include it in a story and verify that it works
  • export your extension to PDF and/or PowerPoint (possibly also image)
  • and export the story to Powerpoint/PDF

You visualization is actually used in three contexts, that are somewhat different:

  1. the normal analyse mode, with live data, affected by changes in selection state
  2. the snapshot/story mode with a snapshot of data and the selection state as it was when the snapshot was created
  3. the export mode, which is also based on a snapshot, but run in a separate service on the server

To make it actually work in all, you might need to do some more work.

Make Qlik Sense wait for your extension

One problem, mainly in export, is to let Qlik Sense know when your extension actually is ready with the rendering. You might get just an empty area in your powerpoint, or just the header but no content. This might be because the image is taken before your extension is ready.

The mechanism to fix this is a javascript concept called a Promise. Your paint method should return a Promise, that resolves when the rendering is ready. Qlik Sense will then wait until the Promise is resolved and then grab the image.

This is described where briefly in Qlik Sense help under ‘Set up “finished rendering” notification, like this:

This will return a Promise, that immediately resolves. This might not be good enough, since you might want to wait for something until you resolve. In my case, I used highcarts and highcharts has a callback function that will be called when rendering is ready. The final solution is like this:

So, I create a deferred object before calling the Highcharts rendering method. I can then return the promise that belongs to the deferred, but I do not resolve it until Highcharts tells me that rendering is complete, actually I had to add an additional delay of 1 second to make sure. There is currently no documentation of qlik.Promise in Qlik Sense help, but you can look in the angularjs documentation.

Turn animations off

If it’s still not working, you should take a look at animations. Animations in analyse mode are good (if used the right way…) and might help the user discover insights in their data, but in export they should be turned off.

In Highcharts there are actually two flags you should turn off to diable animations. It might be enough to turn off just one of them, but why not both:

The isSnapshot flag which I use to determine if we are in analyse mode or in snapshot (story or export) is undocumented, so this solution might break in future Qlik Sense versions, but in September 2017 it works.

Using enigmajs in your Qlik Sense extension

Sometimes in your extension you need to acces the QIX Engine API. You might need to make or clear selections, apply a bookmark or something else. Traditionally you would use the Capabilities APIs for this. You would load the qlik module, get hold of the app with the help of the currApp() method, and call whatever function you needed.

But there is another way. You could do it the way the built-in client does, and use enigmajs instead. Enigmajs creates a javascript wrapper around the QIX api, a wrapper that includes all methods defined in the version of QIX your system is using. Qlik actually has open-sourced enigmajs, you can read more, and download it from the Github repository.

Enigmajs features

Important features of enigmajs:

  • it includes the complete QIX API for the actual version of QIX, while the capabilities API just includes a subset
  • it only includes the QIX Engine API, while the capabilities APIs wraps some client-side functionality too
  • methods calls return a Promise, that resolves when there is a response available
  • it handles the invalidation of objects as the selection state changes so it works well from inside an extension

Unlike the Capabilities APIs, enigmajs does not come with a lot of dependencies on requirejs, angularjs and Qlik Sense client code. This makes it well suited for use in envorinments where you use other frameworks, like react or future frameworks we have not heard of yet. Of course if you’re using it inside an extension, you already have all of these dependencies, so that is not so important.

Note that Qlik flags enigmajs as experimental in the current release. I would be very surprised if it goes away any time soon, but details in the implementation might change, you need to be aware of that.

Finding enigmajs in an extension

Qlik Sense creates a server side Generic Object for each instance of your extension (for all built-in charts too, actually). Around that Generic Object will be a javascript wrapper that exposes all the methods available. In the extension you can find it at:

this.backendApi.model.enigmaModel

It will expose all the methods defined in the Generic Object QIX engine API. As you can see, there are a lot of methods. It also has an app property, that exposes QIX Engine API app object. And the app has a global property, that is a wrapper around QIX Engine Global. This gives you access to the complete QIX Engine API.

An example

I have converted my Variable extension for Qlik Sense to use enigmajs instead of the Capabilities API, as versions before 4.0 did. The key call of the extension is after the conversion like this:

function setVariableValue(ext, name, value) {
        return ext.backendApi.model.enigmaModel.app.getVariableByName(name).then(function (v) {
            return v.setStringValue(value);
        });
    }
The parameter ext is a reference to the extension itself. Since methods return a Promise, I can use the then method to run an additional command after the first command resolves. The getVariableByName reurn an enigmajs wrapper aroud a QIX Engine Variable

Conclusion

If you need access to QIX Engine API from within your extension, enigmajs can help you with that. Unlike the Capabilities API it provides wrappers for all methods. But you should be aware that it only wraps engine api, not client-side functionality. It however works nicely within the client and invalidation and refresh of objects will work as expected.

Snapshots and Extensions in Qlik Sense

One of the most popular new features in Qlik Sense is snapshots and storytelling. When you discover something interesting in your Qlik Sense app you can take a snapshot of a visualization, including its selections and data, and then build a story where you use it. And you can recreate the selections from the snapshot and drill deeper into your data or look at it from another angle.

In many cases this works automatically for your extension too, but sometimes it doesn’t. In recent versions of Qlik Sense new features, like printing and export to PDF are also built on the snapshot feature, which makes it even more important for it to work. So here is a description of how it works and what you need to do to make your extension work in snapshots.

How does snapshots work?

You create a snapshot by clicking the camera icon in the visualization context menu.  Qlik Sense will then make a copy of the data used for the rendering, known as the layout. This structure is then included in a new bookmark, that is created in Qix engine. Note that it’s not the HTML that is saved, nor is it a bitmap of the actual visualization on the screen. It’s the data.

So when the user wants to look at the snapshot, the data is loaded from the bookmark, Qlik Sense then checks what extension was used to visualize it, loads the extension, creates an object using the visualization, and calls the paint method of the visualization. But the layout parameter will not contain live data from the current selection state, it will contain data from the snapshot.

What this means for your extension

For snapshots to work your extension should use only the layout when rendering.  You should not try to use the capabilities APIs to access data dynamically. Most likely it will not work, and if you manage to get it working, the data you get will be live and not reflect the state when the snapshot was taken. So make sure you add the data you need to the properties structure instead.

Also you need to keep the layout clean. It is good practice to treat it as read-only, adding nice-to-have references make make it impossible to serialize the layout, and creating the snapshot might fail.

And don’t forget to test creating snapshots from your extensions. Also verify that printing and exporting to PDF works, very likely your users expect them to.

Working with Qlik Sense Engine protocol: four problems you need to solve

Qlik Sense has several APIs and a .Net SDK to help you work with the QIX Engine. They help you working with Qlik Sense without having to bother about the details. But if you need to work on a lower level, there are some  problems you need to solve.

1. Connect over Web Socket

Qlik Sense QIX engine talks Web Socket, and only web socket. While you might need to make a https call to trigger authentication, the actual communication with the engine is web socket only.

The format of the URL can be seen in the Engine API Explorer, which you will find in Qlik Sense devhub:

ws://localhost:4848/app/c%3A%5Cusers%5Cerikw%5Cdocuments%5Cqlik%5Csense%5Capps%5Cexecutive%20dashboard.qvf?

Note that the app id is included in the URL, but what you get is an open web socket, not an open document. Actually, in Qlik Sense desktop it will work without the app id in the URL. That’s because the app id in the URL is not for QIX Engine. It’s for the proxy you will go through to reach Engine, and used for load balancing, the proxy uses it to find which engine to use for this app. This also means that you should always open a new web socket when you open a new app, since you need to give the proxy a chance to select the correct engine.

Conclusion:

  • you need to use web sockets to talk to QIX Engine
  • the web socket URL should contain the app id
  • you should always open a new socket for each app

2. Match request and response

With traditional HTTP communication you make a request, wait, and get a response. Web sockets work differently. You send a message to QIX Engine, but don’t wait for the answer. You just keep on sending messages, and eventually QIX Engine will reply. You do not know the order of the responses and actually the protocol does not tell you what respone belongs to which request.  Typically it looks something like this:

This is the initial communication when Qlik Sense standard client opens an app. If you think it is hard to read (and it is) you can open the browser developer tools and look in the network tab, the image above is from Chrome.

The darker rows are requests sent from the client to QIX Engine, while the white rows are responses. As you can se the client makes a series of requests (seven) and after that comes the respones. It is not obvious which responses belong to which call, and the respones can not be understood without knowing the request.

So how do you know which respons belongs to which request? Qlik Sense uses a standard called JSON-RPC for this. This means that in each request there is a field called id that contains a unique identifier for this request (in this case just a number 1, 2, 3 etc). The reply for the call contains the same id. So if you are going to build your own protocol handler you need to save the ids and use them to connect request and reply.

But note the very first line above, it has no id. Thats because there is no request for it, it is what JSON-RPC calls a notification.

Conclusion:

  • web sockets are not like traditional HTTP in that you do not wait for a response for your request
  • instead requests and responses are connected with an id included in the packages
  • you might get messages without id, in that case they are notifications

3. Handle the handles

A key concept in the QIX engine protocol is the handle. For every object you open or create you get a number, a handle. Do not confuse that with the request id (also a number) or the object id (a string, in most cases a short or long GUID). You need to save the handle when you open or create an object and use it for subsequent calls.

Initially you have only one handle, the global handle, which is -1. When you open your app you get the app handle, in most cases 1.

An example from the list above:

Conclusion:

  • QIX engine uses something called handle to keep track of objects
  • you always need a handle in your calls to QIX Engine
  • the handle will be in the response from calls that create or open an object

4. Validate when changed

Some responses from the QIX engine contains another field, ‘change’. This field is an array of numbers. The numbers are actually handles and refers to objects that have been invalidated by the call. If the user for example makes a selection, she will pretty quickly get a response. This response means that QIX engine has received the selection.

It has also determined which objects of the ones you have opened that are affected (invalidated is often the term used) by this call. This means that the data you have for these objects is no longer valid.  It is your responsibility to fetch the new data, QIX engine will not automatically recalculate it for you, only tell you that the previous version is no longer valid.

For some calls there will be no change array, while for some selections(or clear selections) a lot of open objects will be affected, so the array will be long. The apis automatically refresh the data for you, but if you are building your own stuff you need to take care of it yourself. You do that with the getLayout call for the object.

If you do not refresh your data, QIX will not tell you again that it is invalid. You only get notified once, until, of course, you get new data and that is invalidated.

Conclusion:

  • QIX engine keeps track of which open objects are affected by a call
  • the field change contains an array of these objects
  • QIX will not sendd the new data until you asks for it
  • it is your responsibility to keep track of the state of the data you have fetched from the QIX engine

Qlik Sense Generic Object IV: The lists

A Qlik Sense Generic Object can also be used to list objects in the Qlik Sense app to which it belongs. You can use it to list things like:

  • fields
  • dimensions
  • measures
  • bookmarks
  • snapshots
  • media (that is images controlled by Qlik Sense)
  • sheets
  • master objects
  • variables
  • stories

The javascript APIs provide the app.getList method, which is a wrapper that helps you get theses lists. It will create a session object for you with the list you are requesting, get that actual data in the list and call your callback function once the data is available.

List are live – your callbacks might be called several times

Just like with other Generic Objects the data might be invalidated. If you are using the javascript APIs it will then be automatically revalidated and your callback function will be called again, so you should be prepared that your callback might be called multiple times. But unlike the Hypercube and List Object the lists are not affected by the users selections. Instead they will be invalidated when the underlying data changes:

  • if the user adds a bookmark, you will get a new bookmarklist
  • if the user adds a measure, you will get a new measurelist
  • if the user adds a dimension, you will get a new dimensionlist
  • etc….

Configure the data you get in your list

The data you get in your lists is configurable. If you use the API method, you will get a default set of fields for each item in the list, but you can set the list up to suite your needs.

As an example, if you use the api method getList(‘measure’, callback), you will get a list where records look like this:

The first and second part, qInfo and qMeta, will always be there. They contain the id to use when refering to this measure and title and description use ful in a list of measures. The third part however, qData, is configurable. The default configuration just gives you tags and title one more time, but if you instead want more data about the measure, you can use your own measure list definition like this:

Under qData you provide your own mapping of the properties you want. In this case I have just specified that I want everything under qMeasure included. You can also specify individual fields.

And get output that includes more data for the measures:

And the same goes for the other lists, you can supply your own definitions and get more data included.

Qlik Sense Generic Object III: Listboxes

SWhile the HyperCube is the main object for calculating data the ListboxObject is a way to list available values for a field. Its the object behind the listboxes in the Filter Panel. It is also the object used for the listboxes that are created when you click on a field in the selection toolbar.

An example setup from the horizontal listbox extension example, included with the Qlik Sense installation:

This contains defaults for most of the parameters, except the actual field, which the user selects in the property panel, which looks like this:

As you can see the Listbox has either a predefined measure from the library, in which case qListObjectDef.qLibraryId will contain the id of the measure (some UID string) or it has a field name, which will be in the array qListObjectDef.qDef.qFieldDefs at the first position. You don’t really have to bother about which one the user selects to use, the ListboxObject will work the same anyhow.

Frequencies and sort order

There are also some other properties in the ListboxObject that are well known to QlikView users. The frequence mode gives you some additional info on the frequency of the respective values in the ListboxObject. Look to horizontal listbox extension for an example. There is also the qSortCriterias array, which allows you to specify the sort order. The default is to have the selected values first, but you might want to for example keep months order from January to December irrespective of which month is selected.

Mashup editor – even more properties

Like with the Hypercube, you can also build a ListboxObject in the mashup editor. The tool to do this looks like this:

As you can see, you also get the option to include expressions in your listbox, usable if you want to make some calculations for each value in the list. The mashup editor will create a ListboxObject for you like this:

The expressions are added to the ListboxObject in the qExpressions array. In this example I have used predefined measures, but you can also use measures defined in the actual listbox object. The mashup editor includes a expression editor (actually the same used in the Qlik Sense client) to help you with this.