This is not a huge release – it has basically only one new feature.
You can now refresh your datasets when the Qlik Sense app is reloaded.
This will replace the existing dataset in the spreadsheet with new values but keep formatting. The same selections you used originally will be used, and the same calculations.
if the app has been updated after you insert a dataset in your Excel spreadsheet, you will see the text ‘Outdated’ in the list of inserted tables in the add-in
the menu that is now available in the table list contains a choice to refresh the data
if you select this alternative, the addin will apply the same selections originally used when extracting data, and then extract the same data, replacing the old dataset
the text ‘Outdated’ will no longer show
While this is a small change it is pretty important and could save you a lot of work. No longer will you need to make a new export of an Excel file and paste it into your spreadsheet. The add-in takes care of that for you.
Good to know
only datasets you explicitly select to refresh will change. You can keep the old state for some if you wish.
you can make a copy of your spreadsheet (copy ‘Sales Aug’ to ‘Sales Sept’ for example) and refresh the copy but keep the original unchanged
Excel will not overwrite cells for you, so if your dataset might be larger this time you should not have any content below it.
You’ll find more info, setup instructions etc here.
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.
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.
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.
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.