Sense Excel

Change log


Version Date Autor Note
1.02 20.07.15 TRvD Corrected version
1.04 11.08.15 TRvD Installation remarks
1.06 10.11.15 TRvD Sense Excel version 2.1.2+10
1.08 14.12.15 TRvD Sense Excel version 2.1.2+15
1.11 05.08.16 TRvD Sense Excel version 2.1.2+46
1.12 08.09.16 TRvD Sense Excel version 2.1.2+70

1.1 Installation notes


If previous installations of Sense Excel exist on your system please read the following installation advice.

  1. Close MS Excel and make sure it is not running in the background.

  2. Extract the new downloaded version of Sense Excel in the existing installation folder and overwrite (all) the old installation data.

  3. Start MS Excel, go to the Qlik Sense tab and check the “Auto load” option (if not checked already) under the about menu. This will start Sense Excel when Microsoft® Excel is started.

  4. Check to see that the version number equals the downloaded and installed version of Sense Excel. This can be confirmed by clicking on the question mark in the about section of the Qlik Sense tab.


1.2 Disclaimer


This document will cover how to get Sense Excel up and running on your system as well as explain what is to be expected and how it can be used. This document was written in order to provide quick answers to the most pressing questions and as such will be updated as often as possible. Please keep in mind that not all options or possibilities of Sense Excel are mentioned within this document

AKQUINET SENSE EXCEL LICENSE AGREEMENT
IMPORTANT: BY ACCEPTING, DOWNLOADING OR USING THIS SOFTWARE, YOU ACCEPT AND AGREE TO THE TERMS OF THIS AKQUINET SENSE EXCEL LICENSE AGREEMENT (“DLA”) AS MAY BE UPDATED FROM TIME TO TIME AND PUBLISHED AT WWW.AKQUINET.COM. BY ACCEPTING THESE TERMS, OR USING THE SOFTWARE AS AN EMPLOYEE, CONTRACTOR OR AGENT ON BEHALF OF A CORPORATE OR OTHER ENTITY, YOU (“USER”) REPRESENT AND WARRANT THAT YOU HAVE AUTHORITY TO BIND SUCH ENTITY TO THESE TERMS. DIRECT COMPETITORS AND THEIR EMPLOYEES AND AGENTS MAY NOT ACCESS THE SOFTWARE WITHOUT PRIOR WRITTEN CONSENT OF AKQUINET. THE SOFTWARE MAY NOT BE USED FOR PURPOSES OF BENCHMARKING, COLLECTING AND PUBLISHING SOFTWARE PERFORMANCE DATA OR ANALYSIS, OR ANY OTHER COMPETITIVE PURPOSES.

NOTICE: THE SOFTWARE CONTAINS FUNCTIONALITY INTENDED TO LIMIT THE DURATION OF ITS USE AND IS INTENDED TO COLLECT CERTAIN USAGE METRICS. THE INSTALLATION OF THIS SOFTWARE WILL INSTALL FILES NECESSARY TO OPERATE THE SOFTWARE ONTO THE USER’S COMPUTER AND OTHER SYSTEM FILES MAY BE INSTALLED OR UPDATED. AS WITH ALL INSTALLATIONS, BACK UP OF THE USER’S HARD DRIVE IS RECOMMENDED BEFORE INSTALLING THE SOFTWARE.

The rest of the disclaimer can be read at Qlik2Go (https://support.qlik2go.net/projects/sense-excel/wiki) under "Terms of use".
There you can also register and download Sense Excel.

Who are we?‌


We offer our services as part of the akquinet AG (a stock corporation) with headquarter in Hamburg, Germany. With approximately 650+ employees, akquinet offers a wide range of IT and BI services, which cover hosting services and Enterprise Resource Planning (ERP) implementation projects using Microsoft and SAP software as well as developing custom build ERP solutions in JAVA. The Business Intelligence office located in Jena, Germany is responsible for developing the Sense Excel Add-In. We hope you will benefit as much from Sense Excel as we do.

What do we do?


The BI department uses Qliktech software such as QlikView and Qlik Sense in order to provide the customer with a BI tool that enables them to discover answers to questions that could not be asked in near real-time before. Our goal is to enable insights and discovering answers to the most pressing business critical questions. In a fast moving business, it is of utmost importance to have constant access to reliable, current and complete information from the company databases.

How do we do it?


Through live-demonstrations, "Seeing is believing"-workshops, webinars, training courses, in-house consulting and remote support-sessions we offer our customers as much support as they need when implementing Business Intelligence software tools.

Why do we do what we do?


We believe that all employees and management should have easy access to the information that is business critical to their job when and where they need it. Because Qlik Sense is one of the best BI tools on the market that corresponds to what we believe, we use it to empower those who believe what we believe - our customers!

Why have we developed Sense Excel?


We developed this Add-In for Excel because it further enables our customers to get the most accurate, current and complete set of data from a single source of truth such as Qlik Sense and enables them to use and edit that data within their (existing) Excel reports.

Inspired by Simon Sinek: The Golden Circle (TED Talks)

Abbreviations


The following abbreviations are used in this document:

BI = Business Intelligence
ERP = Enterprise Resource Planning
Excel = Microsoft Office Excel 2010/13/16 & Office 365 (local)
IT = Information Technology
MS = Microsoft
QS = Qlik Sense SE = Sense Excel

What can you expect from Sense Excel?


In this chapter, we will explain what can be expected of Sense Excel and especially what cannot. In order to have the right expectations of Sense Excel it is necessary to first list what Sense Excel isn’t made for and thus cannot do. Sense Excel...

  • …cannot import a complete or part of a QS Dashboard into Excel
  • …cannot import any visualization objects from QS into Excel
  • …cannot enable Excel to go further than the maximum amount of rows that Excel offers (approximately 1,048,576 in MS Excel 2013)
  • …does not change Excel in any way other than extending the reporting capabilities of Excel

However, Sense Excel is a real asset when it comes to providing MS Excel with accurate data form a single source, which has the correct data from all the different databases. This information is then aggregated in Qlik Sense and made available to MS Excel using the Sense Excel Add-In. This empowers Qlik Sense and Excel users to generate dynamic reports and ensures that they can quickly provide solutions to the business critical questions that management needs to answer. You can expect Sense Excel to…

  • …automatically update the report after connecting to the corresponding Qlik Sense app (.qvf-file)
  • …provide the same UI (user interface) elements as provided in Qlik Sense for ease of use
  • …provide all the dimensions, measures, bookmarks etc. that are available in the connected QS app
  • …allow editing of the QS application script as well as reloading the App in Excel (≥ version 2.0.1.10)
  • …keep adding sensible features

In many companies, it is common practise for a report to be created in Excel. However, this also creates many coordination efforts in order to get the right amounts in all those reports. With Sense Excel, you can merge the advantages of using Qlik Sense to create a single source of correct information with the advantages of Microsoft Excel as a reporting tool.

In chapter 6 we define two ways in which a report can be built in Excel using dimensions, measures, bookmarks and filter-settings form QS. The first way is by creating a function for every cell within the (existing) Excel report that needs an amount or string to be displayed. There are different functions that can be used for such operations. The second way is by adding complete tables form QS via the “Table”- button (5.3 nr. 4) and choosing the fields that are relevant to your query or report.

We hope Sense Excel will enhance your Qlik Sense and Excel experience and provide you with consistent, reliable and near real-time data for your Excel reports.

5.1 Requirements


Please make sure the following requirements are met:

  • Either a current Qlik Sense Desktop or a Qlik Sense Server software should be installed
  • Microsoft Excel 2010, 2013, 2016 or Office 365 local edition (older versions cannot be supported) should be installed (preferably in 64-Bit version, but 32-Bit is also supported)
  • A Sense Excel version (e.g. version 2.1.2+70) should be downloaded from either Qlik2Go or the Qlik Market (5.2.1)

5.2.1 Download the software


Via Qlik2Go:

  • 1: Register at Qlik2Go (image 1) ( https://support.qlik2go.net/account/register )
  • 2: Activate the account by clicking on the link within the confirmation e-mail which is sent after registration
  • 3: Within easy redmine click on Projects >> Sense Excel >> Wiki (for the install guide and terms of use) or >> Files >> "akquinetsense- excel-x.x.x+x.zip" (image2) and choose a version.

or Via Qlik Market:

5.2.2 Unpack


Unpack (unzip) the files in a location of your choosing.

5.2.3 Confirm requirements


  • 1: Confirm that your system now meets the requirements as mentioned in chapter 5.1 Requirements
  • 2: In addition please find out if your Microsoft Office edition is a 32-Bit or a 64-Bit version (in MS Excel under FILE >> ACCOUNT >> "About Microsoft Excel Preview". Behind the MSO number, it says 32-Bit or 64-Bit)

5.2.4 Installation


  • 1: Depending on whether you have a 32 or 64-Bit version of Excel choose the corresponding .xll-install file from the installation folder and double click it.
  • 2: Excel should now start automatically and add the Qlik Sense tab to Excel
  • 3: Please close & restart MS Excel in order to get started.

5.2.5 Qlik Sense tab


  • 1: Go to the Qlik Sense tab in Excel and click on the downward arrow beneath "About" and click on "Auto load" (image 6) if not checked already. This will start Sense Excel every time you open MS Excel
  • 2: Please take the time to read our terms of use (click on About) and confirm that the version you are using corresponds to the version listed in the Info window

This completes the installation of Sense Excel for Qlik Sense Desktop.

5.2.6 Qlik Sense Server installation


WARNING:: only the paid version of Sense Excel can be connected to a Qlik Sense Enterprise Site (server) installation. EXCEPTION: all current Qlik Sense Partners can also use Sense Excel on their Qlik Sense server. INFO: a limited 30-day server test license is available for testing Sense Excel with existing Qlik Sense customers.

When connecting Sense Excel to a Qlik Sense Server the following steps should be taken in addition to the abovementioned steps (5.2.1 – 5.2.5).

1: Open the Qlik Management Console (QMC) using one of the following options

  • On the server/computer go to:
    Start >> Programs >> Qlik Sense >> Qlik Management Console

Or

  • use any updated browser and type: https://”your_sense_server_ name”/qmc
  • If you still have the previous session opened in your browser, you can access the QMC through the Login in the middle of the page.

2: A Security rule should be added to the Qlik Sense Server.
Within the QMC >> Security rules >> Create New >> Enter the following parameters

Name: LicenseReadAll
Resource filter: License_*
Condition: !user.IsAnonymous()
Context: Only in QMC
Actions: Read

Press Apply (if available) and confirm the rule has been added.

3: In Excel and within the Qlik Sense tab click on the field called “Qlik Sense Desktop” and enter the connection string (URL) to your own Qlik Sense server

  • This should first be tried via a https://your.server.url/ -connection using HTTPS. If this does not work due to a browser security issue this means that you do not have a valid security certificate (companies can buy such certificates online)
  • If that does not work, then try connecting through http://your.server.url/ this should work fine

4: If you need a license to connect to Sense Excel (due to using the server version and not having a Partner-, Trial, – or Qlik (internal) license number) a dialog will appear where you can enter your Sense Excel License (3 - 4 rows). A Sense Excel License can be obtained from your Sense Excel partner or akquinet directly (if no partner is available). Please find contact data below.

5: Regardless to say that Qlik Sense services should be running on the server whilst connecting to the Qlik Sense server.

6 Explaining the User Interface


In order to get the most out of your Sense Excel experience it is necessary to understand the UI (User Interface) used in Sense Excel. The following picture shows the “Qlik Sense” tab as well as the features that make out Sense Excel.

  1. Connection:
    • a) Connection string: This enables you to connect to your QS Desktop or QS Server (URL)
    • b) Log in | Log out : Logging in will either start QS Desktop in the background or and connect to the QS hub. You can also disconnect if you no longer wish to be connected to QS
  2. Open hub: When connected (1), you can choose you application, which will enable you to access the dimensions and measures that are associated with that application.
  3. Data model and Master items: The features “Load data”, “Data load editor” are available in version 2.0.1.10 and above. The “Dimensions” and “Measures” are not yet available.
  4. Data: When using the “Table”-button a new window will appear, which allows you to choose (double click the field or select and press “Add”) the dimensions and measures you wish to import from your Qlik Sense application (chosen in 2) to Excel as tables
  5. Bookmarks:
    • a) Create new bookmark: This option allows saving your current filters in order to use them more than once and within the next session.
    • b) Bookmarks: After creating a bookmark, you can access them through clicking and selecting the bookmark you are looking for. Bookmarks created in Sense Excel are not re-imported in QS.
  6. Toolbars: By clicking on the “Selections tool” field you will show or hide the Sense filter bar options (8 & 9).

  7. About:
    • a) Clicking on the question mark brings up an additional window with the Sense Excel version and disclaimer information.

    • b) Using the “Support” option will open an email, which has a log file attached. This can be sent to our support-team.
    • c) A click on the small arrow below “Info” enables you to select the “Auto load”-option, which makes sure the Qlik Sense tab is shown when starting Excel (please keep it checked!). The second option enables you to change the language settings for Sense Excel.
  8. These pictograms allow you to manipulate your selections e.g. “Step back”, “Step forward” and “Clear all selections. Furthermore, when a filter is set, you can manipulate that selection by clicking on it followed by “…” and then choosing one of the options that are available.
  9. Search box and Selections tool:
    • a) Search box: This feature is not yet available. We hope to implement it soon.
    • b) Selections tool: Clicking on this button will open the “Global selector”, which, like in Qlik Sense, will gives you access to all the tables and their content as if they were list box objects. The options, as displayed on image 15, are also available when in global selectormode (dark background). To close the global selector click on a free space above the dark background.
  10. This concludes the brief introduction of the SE UI. If there are any questions that have not been answered or should be described in more detail, please feel free to post your feedback (Link ).

7 Using the Sense Excel demo report


In order to enable the user of Sense Excel to understand, create or edit existing Excel reports we have added an example, which shows how a report can be built. The .xlsx-file is available in the Wiki section (image 2) on our Qlik2Go platform as mentioned in chapter 5.2.1.

To use the Sense Excel Executive Dashboard Report, please follow the following steps:

  • 1: Start MS Excel and make sure the QS tab is visible (5.2.4).
  • 2: Connect to the desktop client or server (as mentioned above) via the "Log-in" button on the QS tab (5.3 nr. 1)
  • 3: Open the SenseExcel_ExecutiveDashboard_Report.xlsx-file.

Figure 1: Sense Excel Executive Dashboard Report

  • 4: When opened go to the QS tab >> Log in >> click on “hub” >> click on “Executive Dashboard” (image 12). Now the Excel report is connected to the Qlik Sense .qvf-file. Any changes made to the data within the original .qvf-file (in QS) will be automatically transferred to the corresponding fields in the excel report.
  • 5: Use the global selector (top right on the selections toolbar ( )), choose some filters and see how it automatically updates the figures in the Excel report in the background. Close the global selector by clicking above the black background.
  • 6: View the filters that were just set in the Sense selections toolbar (image 11 nr. 8 & 9) and click on them to change or edit them
  • 7: To see the familiar Qlik syntax click on any “ACTUAL” field within the Excel Demo report. You will now see an example of the syntax, which is used to get the data from QS into Excel. The four different functions that can be used are listed and explained in chapter 6.
  • 8: This report also features at least two bookmarks, which can be used to see saved selections from this or previous sessions. New bookmarks can be created effortlessly.

8 Syntax for Sense Excel

8.1 Available Sense Excel syntax commands


8.1.1 SenseConnected


SenseConnected is the command with which the connection to Qlik Sense can be tested. Either it returns true (WAHR), in which case there is a connection to QS or false (FALSCH) in which case there is no connection to QS. The Syntax is used in Excel in the following manner; =SenseConnected()

8.1.2 SenseFilter


SenseFilter is a function that allows the use of two parameters. It can be used as a filter box and allows use of curtain wildcard characters such as “ * “. The first parameter can be a fieldname (e.g. Year) in double quotation marks (“), then a semicolon (;), followed by the second parameter (e.g. 2015) which is the filter to be used (without quotation marks) e.g. =SenseFilter(“Year”; 2015)

This function is added to any empty cell within Excel and causes the imported tables (5.3 nr. 4) with the according fieldname (parameter 1) to be filtered by the second parameter (in our example 2015).

8.1.3 SenseVariable


The next Sense command is SenseVariable, which can use up to two parameters. The first parameter is the name of the variable, which is available in Qlik Sense. The second parameter describes the content of the variable. So e.g. =SenseVariable(“A”, “Bee”) will place the content “Bee” in all places where variable “A” is implemented using the =SenseVariable() function.

This function is added to any empty cell within Excel and causes the imported tables (5.3 nr. 4) with the according fieldname (parameter 1) to be filtered by the second parameter (in our example 2015).

Example 1:
In Qlik Sense (script)
In MS Excel: one parameter
Note: ("A")
Example 2:
In Qlik Sense (script)
In MS Excel: one parameter
Note: "Bah"

8.1.4 SenseEV


SenseEV stands for Sense EValuate and enables excel to receive all kinds of functions such as set analysis and formulae from Qlik Sense and use them in Excel. To understand the syntax the following example of a set analysis combined with excel elements has been added.

=SenseEV(" // This starts the connection to QS and calculates…
Sum({< // …the Sum of [Sales Quantity]*[Sales Price] and…
[Product Group Desc]={'"&$E8&"'}, // …is limited to where PGD = the value from E8…
[Fiscal Quarter]={'"&SUBSTITUTE (L$4;"Q";"")&"'} // …and the Q from [Fiscal Quarter] is erased.
>} // The blue texts are Excel components and the…
[Sales Quantity]*[Sales Price]) // underlined text is the set analysis (filter).
")

In order to use Sense Excel effectively it is very useful to understand the Qlik Sense syntax and the set analysis options that Qlik Sense offers. This allows for specific formulae to be entered in any one cell on the sheet. Just like in Excel you can drag the formulae down (right lower corner of any cell) to the cells below.

8.2 Using the “Table”-import button


Another way to get the data you need into Excel is through use of the “Table” function as shown in image 17 and 18a. The following steps will import the selected tables and allow you to filter them using the global selector button ( ) in order to create the report you need.

Step 1: Click on the “Table”-button to open the “Add Sense Table” menu as shown below.

Step 2: Select and add the first two “Dimensions & Fields” and the first two “Measures” followed by a click on “ok”. This will add the selected information and create four tables on your work sheet. As image 19 below visualizes.

Step 3: When the imported tables are to long (which is often the case), further filtering is needed in order to get to a manageable amount of data for the report you need to build. In this situation, the use of the global selector (), as portrayed in image 20 is advised. The global selector gives you access to all the dimensions of the application and offers a search field for each dimension (red arrow on image 20) to find the required entries.

Step 4: Upon clicking on the resulting entry (after searching) it is added to the “Current selections” as a filter (image 21). The global selector is closed via a click above the dark grey area. After closing the global selector, the filter is displayed in the Sense filter bar (red arrow on image 22). The filter options as displayed in image 15 can be used to further manipulate the selected entries.

If any questions arise or you wish to give us feedback, please use our support platform at http://support.qlik2go.net There you can also find the most current version of Sense Excel (Files), download example reports and our terms of use.

8.3 Using the “Formula”-function in the “Add Sense Table”-Dialog


You can use sum(), count() etc. to aggregate the fields within the data model that have not been defined as master elements in the QS-App your connected with. This means that all fields (texts and amounts) of the data model that are within the “Dimensions & Fields” list and below the “-----------“-line (see image) can be used in conjunction with the “Formula”-function.

Furthermore, all dimensions (above the “-----------“-line (red arrow points to it)) are master items in the QSApp and will not respond to the “Formula”-function. All “Measures” below (see image) are master item measures and cannot be used with the “Formula”-function

Figure 2: Formula function in the Add Sense Table Dialog

8.4 Using the “Data load editor”-feature


With the introduction of Sense Excel version 2.0.1.10 and above comes the ability to edit, save and reload the Qlik Sense applications you built all within MS Excel. This feature allows for more flexibility when programming an interactive Sense Excel report in Excel and opens up new possibilities.

Important note:
In order to use the data load editor and the “load data” (reload) option, you need to be able to reload the application you wish to edit. Because the data which is loaded in the Qlik Sense example applications (e.g. Executive Dashboard) that come with Qlik Sense, the cannot be used for testing and understanding these features.

Using the data load editor (image 23) you are able to change the script of an application and add to or remove part of the syntax. This feature can be limited to administrators and report developers in order to provide adequate security measures and reporting consistency.

Figure 3: Data load editor activated

After editing the script, just click the “Load data”-button and accept saving the new syntax. Click load data again to run the script in the background.

Note: You will not see a reload dialog, but the results are adapted automatically after reloading.

9 Connection Options


As mentioned above you can use the “Connection String”-option to connect Sense Excel to either the Qlik Sense Desktop or Qlik Sense server via a http:// or https:// server address (without “/hub”!).

With Sense Excel versions with or above 2.1.2+74 it is possible to better manage connections to https:// sites, which sometimes have untrusted certificates. In the JSON-Configuration-file of Sense Excel, which can be found at C:\Users\-user-name-\AppData\Roaming\akquinet\SenseExcel you can change the “IgnoreCertError“-setting to either true or false. Setting it to “true” means it will not halt on https:// certification errors and ignore any errors that might occur in order to allow working with Sense Excel even when a certificate error is present. Setting it to false (which is usually the default) will allow the https://- chain to remain intact and provide the most secure setting.

10 Ten steps to your first report in Sense Excel


  1. Connect to Qlik Sense & select an App
  2. Select the Table function and choose some Dimensions & Measures
  3. Add a column to the table by writing the name of the according dimension or measure
  4. Refresh the data (right mouse click -> Refresh)
  5. View the updated Results
  6. Open the Selections tool
  7. Select a Bookmark
  8. Use the filter options
  9. Create an interactive Chart
  10. …or an interactive Pivot table and your first report is done.

11 Contact information


Sales information for Sense Excel:
Juliane.Tschierske@akquinet.de
0049 (0)3641 32787 25

Support:
Please register at http://support.qlik2go.net , login and open a “Bug” or “Feature request”-Ticket in order for us to provide you with support as soon as possible. We wish to provide you with the best Sense Excel experience possible and depend upon your feedback in order to improve Sense Excel.

If you are happy with Sense Excel, please tell others. If you’re not,… please tell us how to improve.