Many Qlik users also use Excel. Typically they want to build spreadsheets with Qlik Sense data, add som calculations, possibly some non-Qlik data or several datasets. Up until now the way to do so has been to export your dataset inexcel format, open it in Excel and continue working from there. If you need another dataset, you make a new export and copy-and-paste the cells into your spreadsheet. If the Qlik Sense app is reloaded with new data, you start all over again, or use copy-and-paste to bring the new data in.

The recently introduced Excel add-in Add Sense makes another way of working possible. Instead of exporting to a new Excel file for each dataset, you can work from within Excel, place the cursor where you want the data, select from a listbox and click Insert, and it is inserted for you. When you want another dataset, from the same or another app, repeat the process.

While it is not difficult to get started with Add Sense for Excel there are som possibilities that you might miss, so here comes a walk-through.

Server setup

Qlik Sense security model means that you can contriol both which users are allowed to access the data and from what servers you are allowed access. Since Add Sense is loaded from its own server, you need to allow upper88.com access as described here.

Connect to server and app

The first thing you need to is to connect to a Qlik Sense server and an app. The server name is just about the only thing you actually have to type in in the add-in, everything else can be selected from dropdown. The server name would be the same server you use for working with Qlik Sense, possibly with a suffix (called virtual proxy in Qlik Sense terminology). you can use the link to Qlik Sense hub, but remove the hub part. Once connected you get the list of apps you have access to. You only have to connect a spreadsheet once to an app, since the add-in saves connection data in the spreadsheet. This also mean that if you copy the spreadsheet, you will copy connection data too, so the copy will have a configuration.

Importing datasets

The core of the add-in is of course the import of Qlik Sense data sets. In the import tab you find the visualizations defined in the app. Only visualizations with tabular data (called hypercubes in Qlik Sense) will be displayed, and only those that have a title.

Some tips:

  • its good practice to define usable datasets for Excel as Master visualizations in the app. They won’t show in the app (if you don’t add them to a sheet) but will be easily accessible in Excel
  • number formatting is transfered to Excel, so make sure to have a good number format
  • the import tab is the best tab for new users, with limited Qlik Sense experience, so preparing good datasets can help them in using the data

Filtering the data

Next to the app dropdown you find a button with an icon like this:

When you click on it the app will open in Qlik Sense (in a new browser tab or window). This window will use the same Qlik Sense session as your Excel add-in. So selections ma

de in Qlik Sense will affect the datasets you import into Excel. In the add-in panel, current selections are displayed below a small comand bar with some buttons to handle selection state:

There is also a Filters tab, which gives you some possibilities to filter your data, enough for simple selections, but using Qlik Sense itself gives you far more possibilities. A good way of working is to use Qlik Sense for exploration of your data, and when you find something interesting import it into your spreadsheet. Note that when selections change, already imported datasets will not be affected. This allows you to easily compare different datasets, like sales by product for different regions, or different months.

Checking what your tables show

One of our goals when we started with the add-in was to make it easy to see what selections lies behind the datasets and when the data was from. We solved this by saving some metadata for

the dataset. You find that in the Tables tab:

As you can see the list of tables contains the title, selections made, where in the spreadsheet the table is and when the data was reloaded. If you move a table in your spreadsheet the metadata will follow (but the sheet location might temporarily be outdated). If you copy the table however the metadata will not follow, it’s better to import it again from Qlik Sense.

Some references

The add-in is available from Microsoft AppSource: https://appsource.microsoft.com/en-us/product/office/WA104381706

You need to have Excel 2016, Excel Online (part of Office365) or Excel 2016 for Mac to use it. A free 30 day trial is available.

More information on the add-in is here: https://upper88.com/addsenseexcel.html

Microsoft information on how to use add-ins in Excel is here.

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.