2015

September 2015

# Dates in Set Analysis

Posted by Henric Cronström Sep 29, 2015

Several aspects of the Qlik search mechanism has been described in previous posts. There is however one that has not been covered: Search in dual fields, e.g. dates. This post will try to explain the basics.

When making searches in text fields, you can search either by using a normal search or by using a wildcard search, and when you search in numeric fields you can use a numeric search. But what about dual fields, like dates, where you have both a textual and a numeric representation?

The answer is displayed in the picture below.

Normal searches and wildcard searches are straightforward and need not be explained. Numeric searches are also possible and do pretty much what you expect them to.

You should however note that the search string in a numeric search must contain the correct formatted date. It is in most cases not possible to use the numeric value of the date. E.g. you cannot search for 42005 when you want Jan 1st 2015, even though this is the value of the date.

The same logic is used in Set Analysis, which means that a correct Set Analysis expression with a date could look like this:

Sum( {\$<Date={"<=2015-02-28"}>} Amount)

Often you want the Set Analysis expression to be dynamic, and then you need to put a dollar expansion with an aggregation function inside it. One case is that you want to compare the selected month with the preceding month. In principal, the solution is something similar to the following:

Selected (Last) month: Sum( {\$<Month={"\$(=Max(Month))"}>}  Amount )

2nd Last month:           Sum( {\$<Month={"\$(=Max(Month)-1)"}>}  Amount )

The Max(Month) will calculate the last possible month, and the dollar expansion will enter this value into the expression before the expression is parsed.

How the expression looks after the dollar expansion can be seen in the column header of a QlikView table. The above formulas have been used in the table below. Note that the dollar expansions with Max(Month) have been replaced with numbers.

So far, so good.

However, the above formulas will not work. First, if you have created the Month using the Month() function, the field is cyclic which means that December of one year has a higher numeric value than January the following year, although it comes before January. Hence, the Max() function will not respect the order of months belonging to different years.

Secondly, the Month field has a dual value. This means that the Max(Month) will return a numeric when you need the textual value (‘Dec’) in the Set analysis expression.

One solution is to use a sequential month instead, and format it the same way everywhere:

Script:

Date(MonthStart(Date),'MMM-YY') as Month,

Expressions:

Sum( {\$<Month={"\$(=Date(Max(Month),'MMM-YY'))"}>} Amount )

Here the field Month is a date - the first day of the month - but formatted with just month and year. In other words: A number that equals roughly 42000 and is formatted as ‘Jan-15’. The same formatting is applied inside the dollar expansion. Note the column headers below.

Often it is practical to put the calculation of the Set analysis condition in variables. This way, the formula is kept in one place only and the Set analysis expressions become simpler and easier to read:

Script:

Set vLastMonth=      "=Date(Max(Month),'MMM-YY')";

Date(MonthStart(Date),'MMM-YY') as Month,

Expressions:

Sum( {\$<Month={"\$(vLastMonth)"}>} Amount )

Sum( {\$<Month={"\$(v2ndLastMonth)"}>} Amount )

Note that the variable definitions start with equals signs. This way they will be recalculated at every click.

Summary: Format the dates used inside Set analysis expressions, and use variables to simplify the expressions.

HIC

Further reading related to this topic:

The Search String

Data Types in QlikView

Cyclic or Sequential?

A Primer on Set Analysis

# Qlik Dev Hub replaces Qlik Sense Workbench in Qlik Sense 2.1

Posted by Francis Kabinoff Sep 25, 2015

For Qlik Sense 2.1, the Qlik Dev Hub, accessible at https://<ServerName>/dev-hub/ , replaces the Qlik Sense Workbench, formerly accessed by  https://<ServerName>/workbench/ (this will now redirect you to the Dev Hub).  In addition to a brand new user interface layout, the Dev Hub has a couple of new features worth mentioning.

The new user interface has a sleek dark color design, and its layout more closely resembles the Qlik Sense client than its predecessor. The top nav bar has a drop-down menu that provides access to an “About” link, which displays information regarding the Qlik Dev Hub, and a “Help” link, which brings up helpful documentation in a new browser window. There’s an added search functionality also included on the top nav bar which lets you search through your mashups and extensions, which is really useful as your number of them grows. A second nav bar allows filter by “Mashups” or “Visualization extensions.” There is also a “Create new” button that lets you jump right into creating a mashup or extension.

A left-aligned nav bar allows you to choose from “Single configurator,” “Extension editor,” “Mashup editor,” or “Engine API Explorer.” The “Single Configurator” was previously not accessible from the Qlik Sense Workbench, and its inclusion in the Qlik Dev Hub is very convenient. The “Engine API Explorer” was previously known as the “Protocol tester” and has a new look to match the Dev Hub.

The “Mashup editor” has a couple new features. There are now menus on the left and right of the main work area which can be toggled to make the main work area larger.  An added feature I’m very fond of is the ability to create new files for your mashup right from the top nav bar. The “Preview” tab has been improved, and you can now interact with your mashup in addition to dragging and dropping charts, which is a nice improvement because formerly if you had off-canvas chart areas, there was no way to access them to drag and drop charts.

The Qlik Dev Hub comes with quite a few new templates for both mashups and visualization extensions. I highly suggest you try some of these out, I think you’ll be amazed at how quickly you can put together a decent mashup, even with little to no web development skills. Steps below -

1) Navigate to the Qlik Dev Hub, click on the “Create new” button, give your mashup a name, and select either “Basic single page mashup” or “Slideshow mashup” from the dropdown menu.

2) Click the “Create and edit” button and this will open up your mashup in the “Mashup editor” for you.

3) Select an app from the dropdown menu in the left nav, and start dragging and dropping charts into the chart areas.

4) Customize as much as you want by modifying the auto-generated HTML and JS files

I’m going to include a few links to documentation below. If you haven’t tried the new Qlik Dev Hub yet, you should definitely check it out.

Dev Hub at a glance

Mashup Editor

Getting started building mashups with Qlik Dev Hub

# Tips and tricks for section access in Qlik Sense (2.0+)

Posted by Fredrik Lautrup Sep 22, 2015

Almost every person I meet to talk about Qlik products and security bring up the concept of section access for discussion. I think section access is one of those things that you either love or hate, but as a company using Qlik products you can’t live without it. The great benefit of section access, in my view, is that it’s driven by the data model which makes it really powerful.

It would be great to get your comments on what you think are the strengths of section access.

As section access is a critical part of how we protect data, we carried over its capabilities from QlikView to Qlik Sense and adapted it to Qlik Sense architecture.

So what has changed?

In Qlik Sense the section access is different in that the names of the columns available have changed:

 Column Description ACCESS Can be USER or ADMIN. The ADMIN access was introduced in Qlik Sense 2.0 and gives the user full access to data. USERID The name of the user in the format of [User Directory]\[User ID] GROUP Value of the attribute group on a user [REDUCTION] Is the field on which the reduction is performed OMIT Fields that should not be available to the GROUP or USERID

In Qlik Sense, a script for section access could look like the following:

section access;

ACCESS, USERID, REDUCTION, OMIT

USER, QVNCYCLES\flp, 1, Region

USER, QVNCYCLES\kag, 2,

];

The example above would give the user QVNCYCLES\flp access to rows with a one in the field called REDUCTION without getting access to data in the Region field, and QVNCYCLES\kag would see the data with a two in the REDUCTION field.

In Qlik Sense section access is applied using strict exclusion, which means that if you are not explicitly granted access you will not be allowed to see any data.

My favourite improvement in section access for Qlik Sense is that it will be harder to lock yourself out of an app. In Qlik Sense you have the option to open an app without data. This means that if you have permissions to change the script you can open the app without data even if you don’t have access to any. This will allow you to change the section access part of the script instead of being locked out.

We have also introduced the capabilities to use attributes sent in at the time of the user authentication to be used with section access. This means that we now can base what data you get access to using the group attribute that can be inserted using SAML or tickets.

# Smart Search

Posted by Jennell McIntire Sep 18, 2015

One of the great features in Qlik Sense is the Smart Search capabilities.  Smart Search allows you to search the data in your app when you are on a sheet.  All you have to do is select the Smart Search icon on the selection bar and type what you want to search for.  In the image below I start typing “fresh vegetables” and data that matches “fresh” or “ve” (the start of the word “vegetables”) is displayed.  From here, I can select one of the items found if it is what I am looking for.  I will select “Fresh Vegetables” in the Product Sub Group Desc field and this selection will be added to the selection bar.

One thing I should note is that I did not use quotes when I searched for “Fresh Vegetables” so “fresh” and “vegetables” were interpreted as separate search terms.  If I use quotes, my search returns less results (seen in the image below) because my search term was more specific and considered one search term versus two.

Now if I want to perform another search, I can do so and it will automatically search within my selections which is Fresh Vegetables.  If I search for broccoli, all possible data will be displayed within my Fresh Vegetables selection.

Now say I search for something that is not within my Fresh Vegetable selection like apple.  I will be given a message indicating that no matches were found and will be prompted to start a new search for only apple if I chose.  If I select the “Start a new search …” button, then my Fresh Vegetables selection will be de-selected before the search begins.

By default, Smart Search searches all fields in the data model but you have the option to set what fields you would like to include or exclude in the Smart Search.  This can be done in the script by using the Search Include or Search Exclude statements:

Search Include * fieldlist

Search Exclude * fieldlist

Fieldlist is a comma separated list of the fields that should be included or excluded in the search.  The Search Include statement is used to indicate which field(s) should be searched when performing a search.  In some apps, there may be several fields that the user may not need to search.  In that case, it is smart to use the Search Include to narrow down the list of fields to search.  This can help with the performance of the search as well.  The Search Exclude statement is used to indicate which field(s) should not be used in the search.  This may be used to exclude ID or key fields that were used to build the data model but that the user does not need to search.  In both statements, wildcard characters * and ? can be used.  Just to show you how this works, if I add the Search Exclude statement below to my script and reload, it will exclude all values in the “Line Desc 1” field when a search is being performed.

In the Master Items, the “Line Desc 1” field is added as a Dimension named Product.  When I performed the search earlier, broccoli results were found in the Product dimension.  Now watch what happens when I do another search for Broccoli within my Fresh Vegetables selection.  Broccoli does not come up in my search results because the exclude statement does not allow a search in the “Line Desc 1” field/Product dimension.

Smart Search provides an easy way for users to find the data they are looking for to filter their data.  The Search Include and Search Exclude statements offer the developer a way to control the fields that can be searched.  This can improve performance and it can make it easier for the user to focus on the fields that are most relevant.  Keep these statements in mind the next time you are developing an app.  It will improve the users search experience.

Thanks,

Jennell

# Introducing Qlik Sense 2.1

Posted by Michael Tarallo Sep 15, 2015

Hello Qlik Community! - I am pleased to announce the availability of Qlik Sense 2.1. Our third and final release in the Qlik Sense product family this year. With this release, Qlik is continuing its commitment to helping organizations use analytics to see the whole story that lives within their data. Version 2.1 provides the most complete Qlik Sense experience to date and allows customers to explore, create and collaborate with greater simplicity by taking advantage of Qlik’s unique associative model.

Allow me to summarize some of the new feature highlights in this release:

• Individuals will benefit from a new visual exploration capability that allows users to explore analytics by changing properties, such as sorting and coloring, without having to enter edit mode or directly change the underlying content
• Groups will benefit from new storytelling features that allow data stories to be directly published to PowerPoint presentations to facilitate sharing and collaboration
• Organizations can take advantage of new variables that empower content creators and developers to build more sophisticated analytics
• Developers will be able empowered with a new development hub to more easily take full advantage of the Qlik Analytics Platform for web mashups, custom apps, and extensions

There is of course much more including additional user experience features and even new Qlik DataMarket Topical Packages. These premium subscription packages include 180 currencies and weather data for 2600 cities.

Now....enough reading and get to watching! The below New Feature video summarizes these highlights and then will link you to other videos to demonstrate and briefly show you how to use these new features.

Note:

• Qlik Sense Enterprise Server and other associated files for our customers and partners will be available via on our download site (customer and partner access only) later today.

• You can experience these features immediately by visiting and registering on the Qlik Sense Cloud.

Regards,

Michael Tarallo

Senior Product Marketing Manager

Qlik

@mtarallo

Qlik Sense 2.1 New Features Presentation

Other videos worth watching:

# Mashup Api - Exporting Qlik Sense Table to CSV

Posted by Yianni Ververis Sep 11, 2015

I am sure that I am not the only one that at some point a Qlik Sense table was needed to be exported into a spreadsheet. While working with the APIs like Mashup and Engine API, this may get a little trivial, especially when we have so many solutions on the the web but not one that works in all major browsers and especially on our Qlik Sense table Object.

Even though this sound very simple and a simple copy and paste would do, here is a proper way of getting only the relevant fields displayed onto our webpage. This works on a simple html table as well as with a Qlik Sense Table Object.

In my previous posts I have showed you on how to create a webpage with Mashup API Creating a webpage based on the Qlik Sense Desktop Mashup API and for styling purposes, how to beautify your page with bootstrap Aligning objects and making a mashup responsive using Twitter’s Bootstrap and jquery

For this project I used an existing app for College Football Rankings Preseason College Football Rankings vs. Final Rankings Over The Years May Surprise You - RantSports

• Now, lets start by connecting to our app and getting the table object.

```var me = {
config: {
host: window.location.host,
prefix: "/",
port: 443,
isSecure: true,
},

vars: {
id: '1b4194fd-0ace-4934-80ff-2c679b19624e'
},

data: {},

obj: {
qlik: null,
app: null
},

init: function () {
require.config( {
baseUrl: ( me.config.isSecure ? "https://" : "http://" ) + me.config.host + (me.config.port ? ":" + me.config.port: "") + me.config.prefix + "resources"
});
},

boot: function () {
me.init();

me.log('Boot', 'Success!');

require(['js/qlik'], function (qlik) {
me.obj.qlik = qlik;
qlik.setOnError( function ( error ) {
} );

// Get the Qlik Sense Object Table
me.obj.app = qlik.openApp(me.vars.id, me.config);
} );
},

```

• Now lets put the html code that will host the object.

```        <div class="row">
<div class="col-md-12">
<article style="height: 250px" class="qvobject" data-qvid="DBujmm" id="DBujmm"></article>
</div>
</div>

```

• Now place the object with our code

```        // Get the Qlik Sense Table Object
me.obj.app.getObject(document.getElementById('DBujmm'), 'DBujmm');

```

• Lets try and get the raw data now from a HyperQube so we can create our custom html table. I have also created here a function that refactors data since I like to be working with more meaningful objects.

```  // Get raw data with HyperQube to create the Table
getData: function (callback) {
me.obj.app.createCube({
qDimensions : [{
qDef : {
qFieldDefs : ["School"]
}
},{
qDef : {
qFieldDefs : ["Conference"]
}
}
],
qMeasures : [
{
"qLabel": "# Preseason Top 10",
"qLibraryId": "HdsZnjL",
"qSortBy": {
"qSortByState": 0,
"qSortByFrequency": 0,
"qSortByNumeric": 0,
"qSortByAscii": 1,
"qSortByExpression": 0,
"qExpression": {
"qv": " "
}
}
},
{
"qLabel": "# Postseason Top 10",
"qLibraryId": "tEknwb",
"qSortBy": {
"qSortByState": 0,
"qSortByFrequency": 0,
"qSortByNumeric": 0,
"qSortByAscii": 1,
"qSortByExpression": 0,
"qExpression": {
"qv": " "
}
}
}
],
qInitialDataFetch : [{
qTop : 0,
qLeft : 0,
qHeight : 20,
qWidth : 5
}]
me.log('getData', 'Success!');
me.refactorData();
callback(true);
});
},

// Refactor Data to a more readable format rather than qText etc.
refactorData: function () {
var data = [];
\$.each(me.data.hq, function(key, value) {
data[key] = {};
data[key].school = value[0].qText;
data[key].conference = value[1].qText;
data[key].pre10 = value[2].qText;
data[key].post10 = value[3].qText;
});
me.data.rf = data;
},

```

• Now lets create the html holder for this table including the column headers.

```      <div class="row">
<div class="col-md-12">
<table id="tableData">
<tr>
<th>Team Name</th>
<th>Times in Preseason Top 10</th>
<th>Times in Postseason Top 10</th>
<th>Times in Pre & Postseason Top 10s</th>
<th></th>
<th>Conference</th>
</tr>
</table>
</div>
</div>

```

• Here is the code that will generate the table

```    // Prepare Data for Display
displayData: function () {
\$.each(me.data.rf, function(key, value) {
var html = '<tr>\
<td>' + value.school + '</td>\
<td>' + value.pre10 + '</td>\
<td>' + value.post10 + '</td>\
<td></td>\
<td></td>\
<td>' + value.conference + '</td>\
</tr>';

\$('#tableData').append(html);
});

// After everything is rendered, enable the buttons for export
\$('#export').removeClass('disabled');
\$('#exportSense').removeClass('disabled');
},

```

• OK. So far we have the code that connects to the Qlik Sense App, gets the table as an object and as row data and displays both for us to use. Lets now put the buttons that will export these two tables. The first one is for the Html Table and the second one is for the Qlik Sense Object Table

```        <div class="row">
<div class="col-md-12">
<a href="#" class="btn btn-default disabled" id="export">Export Html Table to CSV</a>
</div>
</div>

```

```        <div class="row">
<div class="col-md-12">
<a href="#" class="btn btn-default disabled" id="exportSense">Export Sense Table to CSV</a>
</div>
</div>

```

• As you can see I a gave them 2 different classes so I can control the on click events. I can definitely have more efficient way of handling the script execution but for this example it is better if I keep them separate.
• Here is the code that handles the events. We are passing to the exportTableToCSV methis

```            \$(".export").on('click', function (event) {
me.exportTableToCSV.apply(this, [\$('#tableData'), 'QlikSenseExport.csv']);
});

\$(".exportSense").on('click', function (event) {
me.exportTableToCSV.apply(this, [\$('.qv-object-table'), 'QlikSenseExport.csv']);
});

```

```  exportTableToCSV: function (\$table, filename) {
var \$rows = \$table.find('tr:has(th), tr:has(td)'),

// Temporary delimiter characters unlikely to be typed by keyboard
// This is to avoid accidentally splitting the actual contents
tmpColDelim = String.fromCharCode(11), // vertical tab character
tmpRowDelim = String.fromCharCode(0), // null character

// actual delimiter characters for CSV format
colDelim = '","',
rowDelim = '"\r\n"';

// Grab text from table into CSV formatted string
var csv = '"' + \$rows.map(function (i, row) {
var \$row = \$(row),
// Select all of the TH and TD tags
// If its a Sense Object, remove the search column

return \$cols.map(function (j, col) {
var \$col = \$(col),
text = \$col[0].outerText;

text.replace(/"/g, '""'); // escape double quotes

return text;

}).get().join(tmpColDelim);

}).get().join(tmpRowDelim)
.replace(/\r?\n|\r/g, '')
.split(tmpRowDelim).join(rowDelim)
.split(tmpColDelim).join(colDelim) + '"',

// Data URI
csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);

// Check if browser is IE
if ( window.navigator.msSaveOrOpenBlob && window.Blob ) {
var blob = new Blob( [ csv ], { type: "text/csv" } );
navigator.msSaveOrOpenBlob( blob, filename );
} else {
\$(this)
.attr({
'href': csvData,
'target': '_blank'
});
}
me.log('exportTableToCSV', 'Success!');
},

```

The Files and the entire working project is at

https://github.com/yianni-ververis/Export-Table-to-Csv

Also, you can view it live at

Export to CSV

Yianni

# Fan traps and Chasm traps

Posted by Henric Cronström Sep 8, 2015

In data modelling and in Business Intelligence there is something called connection traps. These are inconsistencies in the data model that sometimes cause problems. This blog post is about describing the fan trap and the chasm trap and how these should be handled in a Qlik data model.

When designing a data model, connection traps are sometimes built into the data model. It could be that the source data has been misinterpreted, or it could be that some relations are missing in the data. Usually the traps should be avoided. However this is not always possible. But as you will see, it is not a problem.

There are two main types of connection traps: The fan trap and the chasm trap.

Fan Trap:

“Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambiguous” (1)

Example of a fan trap:

In this model a branch has several sales people. A branch also has several customers. But the above data model says nothing about which sales person is responsible for which customer, although such an assignment may exist. Instead, the data model links all sales people to all customers within the branch.

Joining the three tables will increase the number of records - every combination of sales person and customer will get a record of its own - which means that aggregations will result in incorrect numbers. A single sales person will be counted several times. This is a problem with SQL and many other database tools.

The Qlik engine is however different: Since the three tables are stored as three different tables, the Qlik engine is able to aggregate correctly anyway. A count of a non-key field from the Customers table will count just the records in the Customers table. As long as the aggregation function contains fields from only one table, the aggregation will be correct.

Hence, a Fan trap is not a problem.

However, if you have information about assignments between customers and sales people, you should of course change the data model and load this information, e.g.

But what if a customer is assigned to a branch, but has not yet bought anything? This question takes us to the next trap.

Chasm Trap:

“Where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences” (1)

While a fan trap can be identified by looking at the data model only, a chasm trap can be more difficult to spot. The above data model (Branches - Sales people - Customers) may in fact contain a chasm trap. But the data model looks perfectly fine.

The chasm trap appears only if there is missing data in the middle table, e.g. if you have a customer who belongs to a branch but has not yet been assigned a sales person. Then the link between the customer and the branch will be broken and it will not be possible to see to which branch the customer belongs.

But if you don't need this link, the data model will still work fine. However, if you want this link, you can create it by adding dummy records labelled 'No sales person' to the Sales people table – one record per branch – and link unassigned customers to these. An additional advantage is that these customers will then be easily selectable. If you click on ‘No sales person’, you will immediately find all unassigned customers.

Hence, a Chasm trap can easily be handled.

Bottom line: Connection traps are not a problem in the Qlik engine.

HIC

PS On internet you sometimes find incorrect descriptions of Fan trap and Chasm trap where the two are confused with each other. The definitions I use come from the original description of traps:

[1] Thomas Connolly, Carolyn Begg: Database Systems: A Practical Approach to Design, Implementation and Management (Addison-Wesley, 1998).

Further reading on Qlik data modelling:

Circular References

Synthetic Keys

To Join or not to Join

# App migration: QlikView ❤ Qlik Sense

Posted by Arturo Muñoz Sep 4, 2015

I've been asked quite a few times about QlikView and Qlik Sense compatibilities, questions such as, how to “migrate” an app from QlikView to Qlik Sense? How compatibles are the two products? Let's explore the possibilities.

### How to

The procedure to move an app from QlikView to Qlik Sense couldn’t been any simpler, you just need to drag a QVW app (QlikView file extension) and drop it into your Qlik Sense Desktop, that’s it. This will convert the QVW file into a QVF (Qlik Sense app file extension) and load the same data for you.

Please note that this process is not reversible so please make sure that you have a secure copy of the QVW file that will be converted.

Once your file is transformed you will find that the Load Script has been ported and Qlik Sense will contain the data from the previous QVW so you can start discovering and create charts right away. That’s it, any data normalization/transformation you created in QlikView will work just fine in Qlik Sense.

However, if you hit the reload button right after an app conversion you most likely will find an error popup with a message like this:

LOAD statement only works with lib:// paths in this script mode

To solve this inconvenient and be able to reload normally you just need to recreate the connection to the data sources using Qlik Sense Data connection libraries or switch on the QlikSense Legacy mode.

### Are the charts compatible?

If you have tried to convert one app from QlikView to Qlik Sense or the other way around, then you already know the answer, no they aren’t.  However since expressions are fully compatible, any formula or complex expressions can simply be copied and pasted into the new environment.

### Dimensions and expressions

Jacob Vinzent jvi has created an utility to get all QlikView 11 dimensions and expressions and convert them to Qlik Sense as master items. Check it out at Qlik Branch # Fasttrack QlikView to Qlik Sense.

If you use variables in QlikView to reduce complexity in some expressions or if your goal is to reuse expressions in multiple objects then you probably should create them as a measure in the Qlik Sense Master Library. For the rest of variables to be automatically migrated we will have to wait a little bit yet... stay tuned.

UPDATE: since the release of Qlik Sense 2.1.1 variables (both script and design) are created automatically when migrating an app from QlikView.

### Can I reuse my QVDs?

Yes, you can reuse the QVDs created with QlikView and load the data really fast into Qlik Sense and the other way around.

Note: Please note this information applies to the current scenario and current versions (QV11SR2 and Qlik Sense 2.0.2)

By date:
By tag: