You have been running Qlik Sense for a while in production, with multiple apps, some extensions and mashups. Or, you are just starting to work on a Qlik Sense site, where you don’t really know what has happened before. And perhaps the extensions you are using in development are missing or they seem to be of an older version. How do you find out what is on the server?

Whenever you are running Qlik Sense, whether it’s the standard built-in client or a mashup, the client will call the server to find out what extensions (including mashups) are available. This call has the format:

https://[server]/[proxy]/qrs/extension/schema (+ xrfkey in server installation)

Dump of the extension list, taken from Chrome developer console

The reply you get is in JSON format, and contains all extensions available with their id (the qext filename) plus the contents of the qext file. It’s quite readable and when you look at it in the developer console you can expand the lines you are interested in.

This will give you the extension version number, provided you keep a version number in the qext file, which of course you do.

 

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.

 

 

Building a panel

The property panel is a key part of your Qlik Sense extension. The aibility to set extension properties is what makes your extension both reusable and flexible. At the same time your options are much more limited than in the rendering part of the extension. And since this is Qlik Sense specific all of it, there is not so much information and examples available on the internet. So here are some patterns and techniques I find useful in extension development.

1. Use expressions always

Strings in the property panel can allow Qlik Sense expression by setting “expression: ‘optional’ “. Use that – always. This allows the app developer to use expressions for the property and thereby make the property dynamic. It also allows the app developer to reference a variable and use a variable extension to allow the end user to switch values for the property (horizontal/vertical for example).

It allows the app developer to use an expression, but doesn’t force her. Still a fixed string can be used, and probably will be used. And the good thing thing is you don’t have to bother about that in your extension code, you get this flexibility for free.

2. Dropdown with custom alternative

Sometimes you have just a few possible values for a property, or you want to help the app developer with a list of common values for a property. The dropdown component is a good choice for that. But you risk loosing the flexibility of the expression: the dropdown will only make it possible to select a value at design time, no possibility to make it dynamic or affected by a variable. Or the list is really not a complete list, there are alternatives for the more advanced app developer.

In those scenarios you can combine a dropdown with a separate field that allows expressions like this:

  • create a dropdown with your alternatives
  • add a ‘custom’ alternative to the dropdown
  • add a field with expression: ‘optional’ for the custom value
  • give the field a show function, that returns true if the selected value is ‘custom’

In your extension code you will need to check for custom values, something like this:

var width = layout.width === ‘custom’ ? layout.customwidth : layout.width;

3. Add properties to dimensions and measures

Sometimes you need a property for every dimension or measure. In that case you can extend the built-in dimensions and measures objects by adding new properties. You do this simply by setting the items property to the properties you want to add, and Qlik Sense will merge the default properties with the ones you hav defined. An example:

Note that the ref should begin with ‘qDef.’. That will mean it will be part of the measures property. To the app developer your roperty will look just like the built-in ones. You can do the same thing with dimensions.

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.

A few weeks ago I wrote a post about a pattern I have used to make Qlik Sense apps more flexible and allow the user to choose what visualizations to show.  Feedback I got on this(from Rob Wunderlich) is that there is a performance problem with pick(), that Qlik Sense does not optimize pick calls quite as well as you might think, that it might evaluate function calls where the values are not actually used.

I vaguely remember this from Master Summit for Qlik  where I believe it was mentioned. At the same time I do know that Qlik from time to time makes improvements in the optimization, so it might not be true anymore. Anyhow, this is something you could easily verfify. And I’ve got a tool to make at least a first assessment of this, the Developer Tools extension. I originally made this to help finding id’s for visualizations, but later has added some timing features to it, that can give us a rough idea about performance in your visualizations.

Setup visualizations

To do this I made a copy of the example used in my previous post. You find it here. In it I made a new sheet and copied one of the visualizations using pick() into it. I chose the pie chart (not because I like pie charts, but because I wanted one with some data in it). I then made a copy of it, and in the copy removed all pick() calls, together with alternatives 2 and 3 in that call, leaving only the first one, which is the expression that will actually be used of the three in pick(). And I add the DevTool extension to the same sheet.

Run the test

When all is ready, I switch into analyze mode and click  on the DevTool button, which creates popups for the two charts. I then make some selections, use back and forward, and I get the following result:

The left chart is with pick(), the right one without. The most interesting figure is the one at the bottom right, the maximum calculation time. As you can see there is a significant difference: 147 ms compared to 57 ms.  Seems like Rob is wright: there is (still) a performance problem with pick(), even though in my test case calculation time is so small that users will probably not notice.

Conclusion

This is a very rough test. It’s main advantage is that you can do it quickly. Since there is such a clear difference between the two alternative the result that pick() is considerably slower can be trusted. And the fact that the no-pick alternative actually takes some time (57 and not 5 msek) means that there is some calculation going on, just taking the result from the cache would be faster.

 

 

One of the main uses of the Variable extension for Qlik Sense is to allow users to quickly with a click on a button switch dimensions or measures in a chart. This has been described in Qlik Community here and here.

But perhaps you want to change more on the click of a button. You need to change not only a dimension or a measure, but several. And a title, or a subtitle or whatever. In that case you can use the Qlik pick function:

pick(n, expr1[ , expr2,...exprN])
n is an integer between 1 and N.
pick( N, 'A','B',4, 6 ) 
   returns 'B' if N = 2
   returns 4 if N = 3

 Variable and Pick()

The combination of Pick() and a variable can be very powerful. It also has another advantage. Sometimes the expressions you need to switch between are fairly complicated. Setting them up in the property panel of the variable extension can be difficult, and it’s not really the right place for Qlik expressions. Using the pick function helps you keep your expressions out of the property panel and instead where they belong, in the chart setup or in a measure or dimension definition.

To illustrate this I’ve made a small example, available in the Github repository here. To try it you of course need to install the actual extension. If you don’t have that already, you’ll find it here. I have called it Switch dashboard, because what it does is that it in the same area of the sheets allows the user to switch between three different setups, Margin, Sales and Budget (the actual data and the charts are taken from the Consumer Sales demo).

Initially when you open the app it looks like this:

Click on the Sales button and it will change to:

Just about everything has changed: another KPI is displayed, dimension in the middle (donut!) chart, measure in both charts, titles. And if you click the Budget button, you will see a third setup, I’ll leave that to you to verify.

Setup

To set this up you use classic Qlik features, like script statements and expressions, using the pick function. But you could also use Qlik Sense dimensions and measures. Lets see how its done:

Step 1: define the variable and set default value in the script.

First we define the variable we will use. Using standard Qlik naming conventions I’ve called it vDashboard and added it to the script:

SET vDashboard = 1;

Remember that pick numbering starts with 1, so do not use zero. The plan is that 1 should be our first dashboard, Margin, 2 should be Sales and 3 should be Budget.

Step 2:  set up a chart dimension

Now we use the variable, together with the pick function to define dimensions. In the donut chart I’ve defined the dimension like this:

=pick(vDashboard, [Product Group Desc], [state_name], [Sales Rep Name])

This will mean that we use different dimensions depending on the value of the vDashboard variable.

Step 3: Define measures

But we want to make the measures switchable too. But since we will use the same measure in several places, I have chosen another method for measures. I have defined two master measures, called (not too much creativity here, I’m afraid) ‘Dashboard KPI 1’ and ‘Dashboard KPI 2’. Definition of those looks like this:

pick(vDashboard,
Sum([Sales Margin Amount])/Sum([Sales Amount]),
(sum([YTD Sales Amount])-sum([LY YTD Sales Amount])*0.2)/sum([LY YTD Sales Amount]),
Sum ([Budget Amount])/Sum ([Actual Amount]))

I then use those measures in the charts.

Expressions in Qlik can be a lot more complicated, so it’s an advantage to be able to use the expression editor and get the syntax correct for this. Also I have been consistent in the formatting, so that each pick alternative is on its own line.

Step 4: Labels etc

Finally we want to make sure that chart titles reflect the content. Luckily we can use pick() in the title definition too:

=pick(vDashboard,'Margin Amount Over Time','Sales Over Time','Budget Amount Over Time')

We can use this approach everywhere in the property panel wher an expression is allowed. (A note for you extension developer: make sure that as much as possible in your extension can be set up with expressions, that will increase the flexibility of your solution a lot).

Step 5 (and final): Set up the extension

Finally we need to make it possible for the user to actually change the value of the vDashboard variable, thereby switching the dashboard contents. We do this by adding the Variable extension to the sheet and setting it up like this:

As you can see this is really straightforward. We use the values 1, 2, 3 etc and labels showing what we display for the three alternative versions.

A more advanced setup is using the new dynamic values option, and have a Qlik expression returning the alternatives. That way you could have a different set of dashboards for different users. Only remember that the first alternative needs to be available for all users, since it will be the default.

Conclusion

The combination of the pick function and a variable (and the variable extension) makes really powerful solutions possible. And note that this is classic Qlik skills that are needed: Qlik expressions and the well-known pick function. You do not need to start with web development for this.

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.

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.

About a year ago, I took a first look at Qlik Sense on demand app generation for a customer. Their problem was not really using big data, but rather that the data the needed for some analysis was simply too big to handle in a Qlik Sense ( or QlikView) app. They had to limit the data to just a shorter time period to make it run at all. Our conclusion at the time was that On Demand App Generation might be the right the right tool, but we had to prioritize other projects and did not complete the project.

Now, about a year later, we have the time to look into it again. And while I think the core concepts and functionality is the same, it has had a facelift, and feels much more like a product ready for production.

Improved user interface

The on demand app generation feature consists of a master or selection app and multiple on-demand apps. The user uses the the selection app, which contains aggregated data, to select a subset of the total data. He then generates an on-demand app, with detailed information about that subset.

The interface for this has been improved a lot. Previously keeping track of your on-demand apps was difficult, now you can find them in the selection app.

All your generated apps are listed in the popup for the selection app, together with information about when they where created and the selections they are based on in the selection app. You can also manage your generated app here, delete it when you are ready with it, reload it with the same selections but new data and open it.

Loading data into the on demand app

In many respects developing on demand apps is just like developing any Qlik Sense app. That’s part of the strength of the feature – you can use your Qlik Sense knowledge for on demand apps too, visualizations are the same, much of the load script will also be the same. What’s new is the connection between the selection app and the on demand app: how do you get the users selections into the ODA, and how should you write the load script.

When we worked with this a year ago, we used Qlik Sense varibles and the script snippets published in Qlik Sense help around using the variables to generate script statements. These snippets helps you generate selection queries in the form of SQL SELECT’s to fetch the data. Our use case is somewhat different: our problem is that we have internally generated data that is simply to big for one app, but the data is not in an SQL database (even if the original source is a database), but in QVD files. This makes it possible to use a much easier method: we can have the selected values inserted into inline tables, and then use them in ordinary LOAD statements.

Using an inline table with the selected values

The first step is to create the inline table:

selected_item_tab:
LOAD * INLINE [
 SELECTED_ITEM
 $(odso_ITEMID){"quote": "", "delimiter": ""} 
];

Item ID’s for all selected and optional items will then be injected into the script. By using prefix ‘odso’ we allow the user to make selections on any level in the ITEM dimension, like product groups etc.

You can then use the inline table when you load your actual data:

LOAD
  ITEM_ID,
  ...
FROM .....qvd (qvd)
WHERE EXISTS(SELECTED_ITEM,ITEM_ID);

This will filter the data so that only selected items are included. You can also combine several dimensions, like time or geography to further reduce the data. No variables or string parsing is needed. So far this seems to work well.

Getting used to the generated apps

Perhaps the most difficult part of the On Demand app generation remains. The generated apps will only select a subset of the data, and in some cases it won’t be obvious what the subset is. Qlik users are not really used to this. Selections will be a two-step process, where the actual analysis will work as we are used too, but the initial selection cannot be immediately changed. This is a new way of working for users. Also totals calculated in the generated app might be useful for reference, it must always be remembered that they are just for a subset. We will try to make the initial selections clearly visible to the user of the generated app, and perhaps we should also try to bring over some summarys etc for the complete data, but still users will have to think differently. That might be the real challenge with this.

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.