Last year I wrote a blog post on how to set Sunday as the first day of the week, instead of using the ISO 8601 default. This is fairly straight forward – all you need to do is to write some formulas in the script. See Redefining the Week Start.


However, if you use Qlik Sense it has become even simpler. You don’t need any custom formulas and usually it just works - without you doing anything.


When you create your Qlik Sense app, a number of environment variables are created in the beginning of the script. These variables are based on regional settings of your computer, so usually you don’t need to change any of them. One of the variables is “FirstWeekDay” and this defines which day of the week you want to use as your first day.


If you want Monday, then you should use

    Set FirstWeekDay = 0; // 0=Mon, 1=Tue, ... , 6=Sun


And if you want Sunday, you should use

    Set FirstWeekDay = 6; // 0=Mon, 1=Tue, ... , 6=Sun


Change it if you need to!


This variable is used as default for several functions, most notably WeekDay() and WeekStart(). So when you call these functions, you will automatically get the correct week start and the correct order of the week days.




If you use FirstWeekDay=6, you will get a result like in the picture above. You can clearly see that both the order of the week days (in the filter pane to the left) and the week starts (in the pivot table) are correctly defined. I have used US settings and the following expressions to define these fields:


    WeekDay( Date as WeekDay,

    WeekStart( Date as WeekStart,

    WeekDay( WeekStart( Date ) )  as [Day of WeekStart],


The environment variable changes the defaults of these functions, but you can of course also override the default by using an explicit parameter in the function call:


    WeekDay( Date, $(MyFirstWeekDay) )  as MyWeekDay,

    WeekStart( Date, 0, $(MyFirstWeekDay) )  as MyWeekStart,


With Qlik Sense, we have made the date and time management a lot easier.




PS This functionality does not yet exist in QlikView.

Further reading related to this topic:

Ancient Gods and Modern Days

Roman Emperors and the Month Names

Redefining the Week Start

Redefining the Week Numbers

Some time ago I wrote a blog on Mapping functions and described how they can be used to replace or modify field values when you run the script.  But how do you know when to map versus join the data in your data model?  Mapping works well when you need to look up a single value in another table.  For instance, you may have a products table with product data like the table below and you want to add the product category name to that table.


The product category name is in another table that looks like this:


Now you can add the ProductCategory field to the Products table by doing a join and that would work fine but you can also add the ProductCategory field by simply mapping.


Using a join:

Left Join.png


Using a map:


Since we only want to add one value to the Products table, mapping is a safer option.  With this small sample data, either will work but sometimes when you have a large data set, you have be cautious when using joins.  You need to watch out for new records being added to the table as a result of the join thus potentially changing calculations.


While both a join and a map can work to combine data from two tables, in cases where only one value needs to be added, choose to map.  It is an easier approach and it reduces the chance of errors being made in your data model.  Now I am not saying that joins are bad and should not be used because that is not the case at all.  I am simply stating that mapping should always be used versus a join when you only need one value.




In my last blog post I wrote about the overall logic in the search string. Today I will dive into one specific search method: the Expression search.


Any search string beginning with an equals sign will be interpreted as an expression search, e.g.:




If you use this search string in a field listing customers, it will pick out customers for which the total sales number is more than 80000. This means that the Qlik engine will create a hypercube – the same as what you have in a chart – with Customer as dimension and Sum(Sales)>80000 as measure, and use this to determine the search result.




You can of course use the same search string in any field, e.g. Products or Months. But the results will be different, just as the different charts would show different numbers.


There are many implications of the above; implications you need to be aware of in order to make correct use of an expression search.

First of all: A hypercube is normally based on the current selection, which means that the search will not include excluded values. This is different from the wildcard search and the numeric search where the search is made in the symbol tables that hold all values, also excluded ones. If you want to include excluded values in your search, you need to use Set Analysis in your search string, e.g.:


     =Sum({1} Sales)>80000


Further, the search string is a Boolean expression. This means that you can have several criteria, e.g.:


     =Sum(Sales)>80000 or Count(distinct OrderID)>100


But it also means that you can simplify your expression, using the fact that all non-zero numbers are interpreted as TRUE. As an example, the two following expressions are equal:


     =Count(If(Product='Trousers', OrderID))>0

     =Count(If(Product='Trousers', OrderID))


Finally, the expression search implies an aggregation. This means that you must use an aggregation function. If you don’t, the Only() function will be used. For example, if you want to search for customers from a specific country, you can write




This will work fine if each customer is associated with one country only. However, it will not work if there are several possible values per customer. For example, if you instead want to search for customers that have bought a specific product, you might try:




Then you will find some customers, but probably not the ones you are looking for. You will find those that have bought only trousers and nothing else, since it is interpreted as Only(Product)='Trousers'. If you want to find all customers that have bought trousers and perhaps also other products, you should instead try one of the following


     =Count(If(Product='Trousers', OrderID))>0

     =Count({$<Product={'Trousers'}>} OrderID)

     =Index(Concat(distinct Product, ','), 'Trousers')



I admit that neither of the above expressions is very user-friendly, but it is nevertheless how it works.


If you want a simpler way to find these customers, you will just have to do it the manual way: Click on 'Trousers' in the Product list box, then look at the list of customers.




Further reading related to this topic:

The Search String

Symbol Tables and Bit-Stuffed Pointers

The Only Function

It’s all Aggregations

Charles Bannon

Responsive Design Tips

Posted by Charles Bannon Mar 13, 2015

The responsive design of Qlik Sense allows apps to be developed once and deployed anywhere. This makes the developer’s life easier but there are still a couple of things to keep in mind when you are building your app.


Object Positioning – On a small device like a phone, Qlik Sense will redisplay the page by ordering objects in a single column. The order of displayed objects is determined by a top to bottom, left to right fashion. Meaning that the object that sits in the top left corner of the full dashboard will be displayed first, next the object to the right of the first object will be displayed. So if you are going to build out a dashboard, it makes sense to build it horizontally and not vertically.




Chart Titles and Subtitles - Using chart titles and subtitles ensures that objects can be interpreted by the users who are looking at your chart as a single object.


Titles and SubTitles1.png      Titles and SubTitles2.png


Text and Image Objects - I would recommend that you use text and image objects wisely. In small device mode, images and text can look out of place as they shift positions to accommodate the viewing area of a small device.


Text and Image.png       Text and Image bad.png


So if you keep small devices in mind when you are building your app, you can be sure that all users will have the best user experience possible. Happy Qliking!

Qlik Sense uses Attribute Based Access Control (ABAC) as a basis for its security. In this post I will explain why we decided to implement this, and give you an understanding of the future of access control.


Is it only Qlik that thinks that ABAC is the future of access control? No


When Gartner predicts the future of access control, they say

“By 2020, the majority of enterprises will use attribute-based access control (ABAC) as the dominant mechanism to protect critical assets, up from less than five percent today.” (http://www.issa.org/resource/resmgr/JournalPDFs/feature0614.pdf)

And the National Institute of Standard and Technology (NIST) has released a special publication to raise awareness and help with making effective implementations of ABAC (NIST SP 800-162 http://nvlpubs.nist.gov/nistpubs/specialpublications/NIST.sp.800-162.pdf)


If this is the future, what is then ABAC? I have taken the liberty of changing the NIST definition of ABAC to be consistent with the syntax (marked in bold green) that is used in Qlik Sense.


An access control method where user requests to perform actions on resources are granted or denied based on assigned attributes of the user, assigned attributes of the resource, environment conditions, and a set of security rules that are specified in terms of those attributes and conditions.


This sounds pretty generic. So why is ABAC seen as an important step into the future of access control?


The first major benefit is that with ABAC you can implement other well-known access control methods such as mandatory access control (MAC), identity based access control (IBAC) and role-based access control (RBAC). This makes it very flexible and therefore suitable to solve most company’s security needs.


But are there other benefits? Yes.


You can develop an access control policy independent of users

We have a language that can be used to describe security policies that Qlik Sense understands and be used to enforce the security controls needed.


Accommodating the unanticipated/external user

We can describe the security controls using attributes of resources and users. With this approach new users accessing the system will be in compliance with defined policies, and access will be granted based on the assigned attributes.  No additional configuration is needed in Qlik Sense.


Avoid role/group explosion

Using logic conditions in our rules, we no longer need to create new roles/groups to grant new subsets of user access.


Easier management

The administrators of the system don't need to understand or change the security policy; they only have to ensure that resources and users are correctly described by their attributes for them to be in compliance with the security policy.


Cater for taking more things into consideration for security decisions such as dynamic attributes (environment)

With ABAC we can start using the environment (the context in which the security decision is made, e.g. if the connection to the system is encrypted or not) to describe our security policy.


Hopefully this has provided you insights to why ABAC is the future of access control and the benefits this will bring to your Qlik Sense deployments. If you have questions on this blog post or have ideas of what you want to read in the future please don’t hesitate to comment this post.

Data Literacy

Posted by Michael Anthony Mar 6, 2015

A skill set rarely discussed in the BI narrative is that of data literacy. Much is made of newer & more advanced visualizations, but the ability to understand what you are seeing and make smart decisions from that is incredibly valuable. Only a person with a great degree of data literacy can successfully both read & manipulate complex data to arrive at meaningful insights.


Reading & Writing

Data literacy comes in two parts: the front-end and the back-end. On the front-end a dashboard page requires the lowest degree of data literacy. Most people can read a well designed dashboard page and understand the general status of things. It is when the user advances to pages intended for critical analysis that the bar rises and users begin to drop out. Real analysis requires a data literate audience to get to the root cause of a KPI's status.


Marching hand-in-hand with front-end literacy is back-end literacy. While a great dashboard may not require much data literacy from the users it required a greater degree of data literacy from the person(s) who built it. What is the data being measured? Where does it come from? Are there compatibility issues between the data sets in an application? Building new visualizations or manipulating existing ones require familiarity with the data as well as how complex that data source is. Working with a simple data set requires relatively little data literacy but the more complex the data the greater the need for a data literate developer. Creating new objects is more than just technical development knowledge - it is understanding what you are measuring and why you want to measure it. Data literacy is often overlooked when it comes to the skill set of a great developer.


Increasing the data literacy of your organization, and yourself, is the key to spreading BI to the masses.

Henric Cronström

The Search String

Posted by Henric Cronström Mar 3, 2015

One of the strengths of QlikView is its search engine. With it, you can find pieces of information in a fraction of a second and select the found field values. But how is the search defined? And where can the search be used?

Obviously, a search is defined by the search string that you enter when you search for something. But there are several different ways a search string can be interpreted. See for instance the picture below: In the normal search to the left you have a different result set from what you get in the wildcard search.


search h.png


The logic is the following:

  • Normal search
    The search string is matched against the beginning of every word in the field value. Normal search is used only in interactive searches.
  • Wildcard search
    If the search string contains a wild card or the search string is used in a programmatic search (e.g. in a Set Analysis expression), a wildcard search is made instead of a normal search. This means a strict, case insensitive, match between the search string and the field values, where the only way of representing unknown characters is explicit use of wild cards.
  • Numeric search
    If the search string begins with ‘<’ or ‘>’, a numeric comparison is made. E.g. ‘>=1000’. Only values that fulfill the numeric requirement will be matched.
  • Expression search
    If the search string begins with an equals sign ‘=’, an expression search is made. E.g. ‘=Sum(Sales)>1000’. Then an aggregation is made for each value in the field, and a match is found if the expression is true. This means that you can make a selection in one field based on an aggregation in another field.
  • Fuzzy search
    If the search string begins with ‘~’, a fuzzy search is made. This means that all field values are ranked according to similarity and the top one will be selected when you hit return.
  • Compound search
    Using a compound search, you can express more complicated search conditions with logical operators. Use brackets and ‘&’ or ‘|’, e.g. ‘(California|Nevada)’.


search num.png


Hence, how the search string is interpreted depends only on which characters it contains. The settings in the list box properties do not affect the evaluation. The "Default Search Mode" only affects how the initial search string is created.


Further, you can use the search string in a number of different places, not just in the search area in the user interface: You can also use it in Set analysis, in actions, in bookmarks, in API calls, etc.


In principle, you can combine any search string with any search place. There are however some anomalies and exceptions. For example, you cannot use normal search or fuzzy search in programmatic searches and you cannot use the advanced search modes in the Qlik Sense global search.


Type and place2.png


The bookmark deserves a special mention. If a search is made and the resulting selection is stored in a bookmark, the bookmark remembers the search string and not the selection. This means that if new values appear when the script is run, they may be selected by the bookmark, even though they didn’t exist when the bookmark was created.




Further reading related to this topic:

Text searches

Search - But what shall you find?

The Expression Search

Filter Blog

By date:
By tag: