1 2 3 Previous Next

QlikView Design Blog

168 Posts
Henric Cronström

The QlikView Cache

Posted by Henric Cronström Apr 15, 2014

QlikView has a very efficient, patented caching algorithm that effectively eliminates the calculation time for calculations that have been made before. In other words, if you use the “back” button in the toolbar, or if you happen to make a selection that you have made before, you usually get the result immediately. No calculation is necessary.


But how does it work? What is used as lookup ID?


For each combination of data set and selection or data sub-set and expression QlikView calculates a digital fingerprint that identifies the context. This is used as lookup ID and stored in the cache together with the result of the calculation.




Here "calculation" means both the Logical Inference and Chart calculation - or in fact, any expression anywhere. This means that both intermediate and final results of a selection are stored.


There are some peculiarities you need to know about the cache…


  • The cache is global. It is used for all users and all documents. A cache entry does not belong to one specific document or one user only. So, if a user makes a selection that another user already has made, the cache is used. And if you have the same data in two different apps, one single cache entry can be used for both documents.
  • Memory is not returned, when the document is unloaded. Cache entries will only be purged when RAM usage has reached the lower working set limit. QlikView will then purge some entries and re-use the memory for other cache entries. This behavior sometimes makes people believe there is a memory leak in the product. But have no fear – it should be this way. So, you do not need to restart the service to clear the cache.
  • The oldest cache entries are not purged first. Instead several factors are used to calculate a priority for each cache entry; factors like RAM usage, cost to calculate it again and time since the most recent usage. Entries with a combined low priority will be purged when needed. Hence, an entry that is cheap to calculate again will easily be purged, also if it recently was used. And another value that is expensive to recalculate or just uses a small amount of RAM will be kept for a much longer time.
  • The cache is not cleared when running macros which I have seen some people claim.
  • You need to write your expression exactly right. If the same expression is used in several places, it should be written exactly the same way – Capitalization, same number of spaces, etc. – otherwise it will not be considered to be the same expression. If you do, there should be no big performance difference between repeating the formula, referring to a different expression using the label of the expression or using the Column() function.


The cache efficiently speeds up QlikView. Basically it is a way to trade memory against CPU-time: If you put more memory in your server, you will be able to re-use more calculations and thus use less CPU-time.



In my last blog I explained how, using conditional expressions, a developer could enhance the experience of a user.  And based on some feedback, I have decided to continue the discussion of conditional expressions. In this blog, I want to discuss another area within QlikView where a developer can use conditional expressions to his/her advantage.


Using Conditional Expressions to Show/Hide Sheets


There are times when, as developers, we need to tailor the user experience based on a device (i.e. Desktop vs Mobile). In the GPS – Store Finder app on demo.qlik.com, we do just that. Based on the values of conditional expressions on the sheet properties, we can give the user a more desired experience.


The version of the app on the demo site uses a mobiledetect extension that checks to see through which device type the user is accessing the application. It then sets a variable (vStyle) to either Mobile or Desktop.


The app also uses non-traditional navigation by hiding the Tabrow.


Setting the conditional show sheet expression to only show when the variable vStyle=’Mobile’ hides the sheets designed for the Desktop and allows the user to experience the Mobile version of the app.


Mobile Version

This is set up to fit nicely on a mobile phone with a vertical scroll and larger fonts to assist in better navigation.


Conversely, setting the conditional show sheet expression to only show when the variable vStyle=’Desktop’ hides the sheet designed for Mobile and allows the user to experience the Desktop version of the app.


Desktop version

Designed with a more traditional approach with the list boxes on the left and the viewing area set within the 1024x768 screen resolution.


By taking advantage of the conditional expression for a sheet, we were able to customize the user experience and, in essence, create one application to handle multiple client types. Another example of the use of conditional expressions to show/hide sheets based in device is the Insurance Demo which can also be found on demo.qlik.com.

To make a chart in QlikView – in any Business Intelligence tool, for that matter – you need to have one or several dimensions; entities with discrete values that you use as grouping symbols. But where should you define these dimensions: In the script or in the object where the dimension is used?


In most cases, you will use an existing field as dimension, i.e. an attribute that exists in the source data. In such a case, the answer to the above question is easy: Just make sure to load the field in the script, and you're done.


But in some cases you want to use derived attributes: Attributes that do not exist in the source data but one way or another can be calculated from existing fields.


One example is the fields of the Master Calendar: Year, Month, etc. These can all be derived from a date found in the source data:


   Month(Date) as Month

   Year(Date) as Year


A more advanced example is if you want to classify or rank a field. The following expression returns ‘A’ for the 10 best customers and a ‘B’ for the rest:




For such fields the above question is very relevant: Should they be calculated in the script and saved as fields, or should they be calculated on the fly in a sheet object?




There are pro:s and con:s with both approaches: A field calculated in the script is calculated once and for all, so it does not need to be re-calculated every time the user clicks. Hence, response times will be slightly shorter if the field is calculated in the script.


On the other hand, in some cases you want the field to be re-calculated every time the user clicks. A good example is the classification using Rank() above. Most likely you want this field to depend on the selection made: If you have selected a product, you want to see the classification of the customers given this selection. Such a number is in its nature dynamic and should be calculated every time the user clicks.


The key is whether the calculated field should be static or dynamic. The field Month is static: A specific date always belongs to the same month, irrespective of user selection. As opposed to a classification or a rank where the calculation usually should be dynamic, since the result potentially could change every time the user clicks.


Bottom line is that dynamic fields must be calculated in the chart or the list box. But for static fields it is better if they are calculated in the script, since precious CPU-time otherwise will be unnecessarily spent every time the user clicks.




Note: The Rank() function cannot be used in the script, so if you want to calculate a static rank in the script, you need to do it in a different way, using e.g. a combination of Order By and RecNo().


“Attractive things work better” says usability expert Don Norman in his article Emotion & Design. I fully agree with this statement since I have seen and experienced myself how something that is attractive can turn on a switch inside my brain by which I tend to overlook flaws and problems and re-prioritize what I want.


I use the iPhone analogy a lot but have you ever thought why the iPhone is so popular among people? It is not only because of its colorful and seamless interface but also because of the way the body of the phone is crafted that you feel like you want own it. And why do we want to own those expensive, sleek cars that don’t give a good mileage and also aren’t very economical? Because they make us feel good and attract people around us which makes us feel even better since it reflects something about our personality.


So we all know that attractive things are certainly more preferred than not so attractive things, but why would they work better? In many of the experiments that scientist have conducted to study the human psychology, they have all found that emotion has a huge role to play in how we perceive things and how we solve problems. Positive emotions broaden the thought processes and enhance creative thinking. So how does that make something easier to use? Simple, when people feel good about something it makes it easier for them to find solutions to the problems they encounter.


Considering the theory above, QlikView applications that we design should work the same way. The more attractive they are the more the customers will like them, will like to own them, and will like to use them. And the most important of all, they will be more tolerant to minor difficulties and issues. But that certainly doesn’t imply that it is okay to ignore the usability quotient. As I quote Don Norman “True beauty in a product has to be more than skin deep, more than a façade. To be truly beautiful, wondrous, and pleasurable, the product has to fulfill a useful function, work well, and be usable and understandable.”


To hear more on this topic you can watch this video.

Henric Cronström

The Generic Load

Posted by Henric Cronström Apr 1, 2014

There are a number of prefixes in QlikView, that help you load and transform data. One of them is the Generic prefix.


Whenever you have a generic database, the Generic prefix can be used to transform the data and create the desired fields. A generic database is basically a table where the second last column is an arbitrary attribute and the very last is the value of the attribute. In the input table below you have a three-column generic database.


Generic transformation4.png


But if you want to analyze this data, it is much easier to have all attributes in separate fields so that you can make the appropriate selections. It is not very practical to have one single field for all attribute values, since you may want to make selections using different attributes at the same time.


Enter the Generic prefix.


It converts the data to a structure where each attribute is placed in a field of its own. Another way to express it is to say that it takes field values and converts these to field names. If you compare it to the Crosstable prefix, you will find that they in principle are each other’s inverses.


The syntax is


   Generic Load Key, Attribute, Value From … ;


There are however a couple of things worth noting:

  • Usually the input data has three columns: one qualifier field (Key in the above example), an Attribute and a Value. But you may also have several qualifying fields. If you have four or more columns, all columns except the two last will be treated as qualifying fields.
  • The Generic prefix will create several tables; one table per attribute. This is normally not a problem. Rather, it is an advantage: It is the least memory-consuming way to store data if you have many attributes.


If you have more than one key, this means that you will get a composite key – a synthetic key – in the data model:


Multiple keys3.png


Although it looks ugly, this synthetic key is completely harmless. But it may still be a good idea to replace it with a manually created concatenated key:


   Autonumber(Key1 & '|' & Key2 & '|' & Key3) as Key,


Finally, I have seen many examples on QlikCommunity where a For-Next loop is used to join together all tables created by the Generic prefix, e.g.:


   Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='GenericLabel' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo



   Load distinct Key From GenericDB;


   For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName


The result is one big table that contains all attributes; a table that often is sparse (containing many NULL values) and much larger than the initial tables. And no performance has been gained… So I can only say:


You should not do this - unless you have a specific reason to.


The Generic prefix creates a set of tables that store the data in an optimal way. In most cases you should not change this. I realize, however, that there are cases where you want to transform the data further and need the data in one, unified table. Then the above scriptlet can be used.



Custom dimension grouping


Picture this situation: Your company has a long list of products in its catalog, and you want to compare sales per product. Further, the company has recently created a promotional pack with 4 products and you want to see this as one product in your QlikView app. As this is a limited time only situation there’s no need to create a permanent group in your system.




If you ever faced a situation as described above, you will probably already have found several approaches in our community. Most of them will imply a reload process to store the new groups in the backend.


This time Christof Schwarz has created a different method using a combination of the functions PICK () and MATCH () allowing the users to create and administer the grouping on the fly without reload the entire app.


You can find an example and an explanatory video at http://community.qlik.com/docs/DOC-6086


This way to group items is especially well conceived for those situations where users need that flexibly of creating and administering non-persistent groups on the fly, and letting them to check individual items within the group itself.


For those occasions where users’ needs to be able to store new groups to make them available for later analysis sessions and\or dealing with large data sets it still makes sense to go through a different approach that in most of the cases will involve a script process.


ABC Analysis


ABC analysis is also based in groups, but this time we'll be using three alternate states and Pareto-Select action to solve the ABC customer classification.




Christof’s ABC analysis approach comes with two significant features: It classifies the ABC relatively to the current selection and it stores those classes as is they were normal dimensions until the user recalculate ABC.


Check it out at http://community.qlik.com/docs/DOC-6088


This is a very simple and performant way to implement an A/B/C classification letting users to generate the ABC groups dynamically based on their business needs and to compare those values with other elements in the app.


Enjoy Qliking!


Henric Cronström

The Crosstable Load

Posted by Henric Cronström Mar 25, 2014

There are a number of prefixes in QlikView, that help you load and transform data. One of them is the Crosstable transformation.


Whenever you have a crosstable of data, the Crosstable prefix can be used to transform the data and create the desired fields. A crosstable is basically a matrix where one of the fields is displayed vertically and another is displayed horizontally. In the input table below you have one column per month and one row per product.


Crosstable transformation4.png


But if you want to analyze this data, it is much easier to have all numbers in one field and all months in another, i.e. in a three-column table. It is not very practical to have one column per month, since you want to use Month as dimension and Sum(Sales) as measure.


Enter the Crosstable prefix.


It converts the data to a table with one column for Month and another for Sales. Another way to express it is to say that it takes field names and converts these to field values. If you compare it to the Generic prefix, you will find that they in principle are each other’s inverses.


The syntax is


   Crosstable (Month, Sales) Load Product, [Jan 2014], [Feb 2014], [Mar 2014], … From … ;


There are however a couple of things worth noting:

  • Usually the input data has only one column as qualifier field; as internal key (Product in the above example). But you can have several. If so, all qualifying fields must be listed before the attribute fields, and the third parameter to the Crosstable prefix must be used to define the number of qualifying fields.
  • It is not possible to have a preceding Load or a prefix in front of the Crosstable keyword. Auto-concatenate will however work.
  • The numeric interpretation will not work for the attribute fields. This means that if you have months as column headers, these will not be automatically interpreted. The work-around is to use the crosstable prefix to create a temporary table, and to run a second pass through it to make the interpretations:



   Crosstable (MonthText, Sales)

   Load Product, [Jan 2014], [Feb 2014], … From Data;



   Load Product,

      Date(Date#(MonthText,'MMM YYYY'),'MMM YYYY') as Month,


      Resident tmpData;

   Drop Table tmpData;


Finally, if your source is a crosstable and you also want to display the data as a crosstable, it might be tempting to load the data as it is, without any transformation.


I strongly recommend that you don’t. A crosstable transformation simplifies everything and you can still display your data as a crosstable using a standard pivot table.



History is scattered with luminaries who used personal reflection to change their lives. From the personal journal entries of James Boswell to the more scientific daily measurements of Sanctorius Sanctorius, people have used qualitative and quantitative records to better understand themselves. The inventor of seemingly everything, Benjamin Franklin, had a system to measure how well he lived his 13 virtues on a daily basis using the data to see where he went wrong with the intention of ultimately living a life free from transgression.


Over the last several years personal activity trackers have gained significant traction in the market place. BI Intelligence has conservatively forecasted a $12 billion market for wearable devices over the next 5 years. I've been using a Fitbit device for the past few years but I have also been tracking a variety of data points more manually for the last 5 years. Actively collecting & analyzing personal data definitely places me in the minority but also potentially as an early adopter.


As technology gets smaller and less expensive more and more people are actively & passively tracking data about themselves. Just looking at the news shows us how much data we are passively (sometimes unknowingly) generating about ourselves and is being used by big business and big government.



When will this wave of passive data collection break into active mainstream collection & use of data?

In some ways many people already do actively collect data. People are regularly posting thoughts to Twitter and Facebook which can be used as a running tally of feelings and analyzed for sentiment. Runners and cyclists have been using various hardware and smartphone apps for years now to analyze their performance. People can count calories and check their weight. We have credit card statements of how much we spend as well as investment data on how our money is performing. The problem though is that, with a few exceptions, most of this data is never seriously analyzed by the people generating it. Further, most of the data is left as isolated data sets and is rarely brought together into one consolidated view. Several activity trackers have ways of feeding other activity oriented data into their Dashboard pages but even then they remain isolated from other personal data.


So what's the hold up? Why is the notion of the quantified self still seen as a fringe concept? There are a few answers but two specifically. First, most people aren't very technical and connecting all these disparate data repositories is still not easy. A second answer could lie with the concept of path dependence. Most people don't actively collect & analyze personal data and it's easier to just keep not collecting & analyzing personal data. You have to go out of your way to get started and since most people you know aren't doing it it's hard to see the value.


So why develop your quantified self?

The answer is varied and up to you. Looking around the internet you can find a variety of people who collect personal data and study their own behavior for a variety of reasons. Nicholas Felton generates a very well designed personal annual report each year of his activity; Thomas Christiansen has studied how many times, and the circumstances under which, he sneezes to better understand his allergies. Most people collecting & analyzing their own data are doing so to improve some aspect(s) of their own lives.


One of the nice things about using data is that it is an impartial and detailed mirror of our lives. The human brain is greatly influenced by a variety of cognitive biases. In short we forget things and we aren't great at thinking about ourselves in the future. We suffer from impact bias which is the tendency for our prefrontal cortex to not simulate future situations as well as we think it can. To help make new or better decisions it is nice to have an impartial record of our behavior that might steer us towards the best (and possibly different) future course of action other than the one our brains may have imagined on their own.


As Richard Buckminster-Fuller said "There is no such thing as a failed experiment, only experiments with unexpected outcomes."

Henric Cronström

AND and OR

Posted by Henric Cronström Mar 18, 2014

In QlikView, the logic of the selections is always an OR between selections in the same field, and an AND between selections in different fields. Selecting e.g. two products and one customer is very much like the WHERE clause in the following SELECT statement:


     SELECTWHERE (Product='Cap' OR Product ='Tracksuit') AND Customer='ACME' ;


Under some special circumstances, you can however use something called AND-mode. Read more about it in Jennell’s excellent blog post: AND-Mode. With AND-mode you can select two different products and find the customers that bought both.




However, the AND-mode logic is quite different from a standard AND operator in a WHERE clause: And it does not work at all the same way as OR-logic. There are theoretical implications that do not exist for OR logic.


For example: If you select two products and demand an OR between them, the possible values of all other fields are immediately determined: Any field value implied by either of the products is marked as “possible”.


But if you instead demand an AND between them, it is not clear what you mean: Do you mean “Customers” that have bought both products, or do you mean “Months” when both products have been sold? Or do you mean “Countries” where both products have been sold? Just specifying the two products is not enough to determine a result. You also need to specify the field that the AND-mode refers to.


The example shows that the AND-mode demands an intermediate iterator: The AND-mode always infers a second field for which the AND-logic is relevant. This is a theoretical problem that has nothing to do with how the logic is implemented in the software.


Let’s look at SQL: In a standard SELECT statement, the conditions on either side of the AND operator almost always concern two different fields. It would not make sense to demand


     SELECTWHERE Product='Cap' AND Product ='Tracksuit' ;


since there are no records that fulfill that requirement: "Product" can only have one value at the time. But this is exactly the type of requirement that you have in AND-mode - but operating on a group of records instead of on a single record.


If you would implement something similar to AND-mode in SQL, you would need to join a table with a copy of itself. The following will pick out customers that have bought both a Cap and a Tracksuit:


     SELECT DISTINCT Customer FROM Orders AS Orders1

     INNER JOIN Orders AS Orders2 ON Orders1.Customer=Orders2.Customer

     WHERE Orders1.Product='Cap' AND Orders2.Product='Tracksuit'


Again, an intermediate iterator is needed: Here it is "Customer" - the field used to join the two tables.


In QlikView we have chosen to solve this problem by demanding a two-column table for AND-mode, where the first column defines the iterator (e.g. Customer), and the second is the field where the user makes the AND selection (e.g. Product).


So, the two-column table is not just an arbitrary limitation; it is instead a framework implied by the theoretical problem.



Jennell McIntire

Scrambling Data

Posted by Jennell McIntire Mar 14, 2014

When building demos, I am often required to scramble the data.  QlikView has a scrambling feature that allows selected fields to be scrambled in some random fashion.  I find this to be a quick and easy way to scramble data but when I have a lot of fields to scramble, I think it can make the application difficult to follow because QlikView’s scrambled data is not readable and does not appear realistic.  For example, let’s see what the sheet below looks like after it has been scrambled.


Before scrambling

Before Scrambling.png


After Scrambling

After Scrambling.png


At a glance, it may be hard to comprehend what you are looking at because names do not look like names and regions do not look like regions, etc.  The scrambled data is not readable so it is harder to make sense of what you are looking at.


An alternative to using QlikView’s scrambling feature is to use mapping and modifiers.  I have used modifiers as seen in the example below to change numeric data in my script so that sales figures are not recognizable.




Mapping can be used to change the text data to values that are readable and realistic to the user.  For instance, if I want to change the names in the Manager, Sales Rep and Customer fields I can load mapping tables that store the original name and the new name as seen below.




Then I can use ApplyMap() on the fields that need to be changed.





After a reload, the new “scrambled” data is being used in the visualizations.  Note: If you plan to distribute the application, I recommend removing the scrambled script or creating QVDs and loading from these.  You do not want the user to be able to re-engineer your scrambling and determine the original values.


Check out my technical brief on this topic for more detailed information.  Have fun scrambling!



Part of my job as an Enterprise Architect at Qlik is dealing with hardware sizing. There are several factors to take into account to properly size any environment, and one of the most underrated I have found when discussing this subject with IT teams and developers, is the value of distinctness. But this is exactly one of the things that makes QlikView different. Let me explain this by taking a look at an example.


My app needs a calendar from January 1, 2011 to February 25, 2013. This data is stored in the database in the form of a timestamp:


DD/MM/YYYY hh:mm:ss


That makes 99 531 648 possible values. This field alone, when the QVW is opened, uses 2.3 GB of RAM.


That’s quite a bit of RAM for only one field. Let’s do some math. QlikView pointers are bit stuffed pointers meaning that QlikView needs 27 bits to store 99.5 million values. So, to calculate the RAM usage for the symbol tables, you need to use the following formula:


(Field Length in bytes * Number of Values) + Pointer Size (2^27) = Theoretical Total Size of Model
(18 * 99 531 648) + 134 217 728 = 1 925 787 392 Bytes


So for that field alone, I need to allocate 2 GB RAM, of which 134 MB are to store the pointers. It seems to be quite big for this small part of the model.


Since I cannot afford that much for one field alone, I’m going to split that field into two: date and time.


Now I have a Date field of 1 152 possible values and a Time field of 86 400 possible values. These two fields, when the QVW is open uses 24 MB of RAM.


Let’s do the math with these figures, using the same formula for Date and Time:


Date: 13 568 Bytes
Time: 707 576 Bytes


Because I’m always thinking of optimizing the models, I have decided to discard seconds, as they are not going to be used in my app, but I do need hour and minute.


The Date field has 1 152 possible values but the Time field is now reduced to 1 439 possible values. These two fields, when the QVW is open use 3.9 MB of RAM.


Let’s do the math with these figures, using the same formula


Date: 13 568 Bytes
Time: 9 243 Bytes


These file sizes may vary slightly depending on the file system, size of hard disk, amount of RAM in the system, etc.


We have seen some figures handling dates, but use this same technique with full names, addresses, composite fields, phone numbers… This is when the associative magic happens!


As you can see, distinctness is everything but trivial in QlikView, and taking proper care of fields when developing from the start will make the difference between a viable, well performing model and app and a huge, slow app.


Do you have any examples or tips and tricks of the value of distinctness?



Appendix I: Table with file sizes and RAM footprint for each example


Appendix II: Script code used for each scenario, that you can download here.

When you start getting into the Data Visualization field you quickly learn that there are good visualizations and there are bad visualizations. Most scorned are probably the horrible pie chart and its cousin the donut chart. Should we follow Stephen Fews advice and save the pies for dessert or is there a time and place for sub-optimal visualizations?




With data visualization celebrities such as Edward Tufte and Stephen Few being very vocal in their crusade against bad visualizations the rest of the industry has started to follow suit. BI vendors have slowly adopted and almost everyone is promoting data visualization best practices now a days.


I'm not saying they are wrong, a bar chart or line chart for time series are always a better option than one or several pie charts when the core objective is to compare data points.


But is that always the core objective?

Sometimes we build QlikView apps for very large audiences, apps they might only use once in a while, apps that aren't critical for them to perform their job and sometimes we build apps that contain downright “boring” data. It’s still an important app; the users would more than likely gain additional insight from the data or the app would help them perform their job more efficiently.


Looking at myself I know there are probably several applications that Qlik has deployed internally that could help me in my job. They aren’t critical for me, I would probably only look at them once a quarter or less but still I don’t open them at all.


These would be apps with “boring” data, apps built according to every best practice in the book. Absolutely no pie/donut charts, muted downplayed color series from http://colorbrewer2.org/ and consisting to 99% of bar charts and data tables. They are in no shape or form bad apps, they are built around solid best practices, every data point is correct and every visualization carefully selected to achieve the maximum efficiency but still I can’t get myself to spend more than 15 seconds in them.

What they are lacking is attention.


Attention vs Accuracy

I want to make the case that sometimes it’s appropriate to sacrifice a certain degree of accuracy for attention. Sometimes you need some sex and sizzle to get your users to care at all.


For example, humans are naturally drawn to rounded objects versus squared yet our brains are not wired to quickly grasp the sizes of a pie chart. Despite the logical part of my brain telling me that the bar chart would be a more optimum medium to display the information my eyes are still drawn to the pie chart.


The Bar Chart makes it easier to compare the individual values against each other but for me the pie chart is more inviting.


This holds true for pie charts and it also is true for maps. In this day and age as soon as we have an address or a location in our data we are compelled to put it on a map. Why? Most of the time the geospatial dimension is totally irrelevant to our analysis but we still squeeze a map in every application that we can. Because maps engage the users - you can put almost any boring data set on a map and I would still explore it, I will most likely not gain the most knowledge out of the map BUT my interest has been sparked and I might explore the data and the app further.


So should we go wild and crazy, sprinkle every app with pie charts, donut charts, bubble charts or maps?

Absolutely not, while at the same time we need a certain degree of attention to get our users to take interest in the data; we also have a responsibility to represent the data in the most accurate way possible. But what good is the data if people won’t take any interest in it at all?


I say it’s okay to stray from the path of best practice as long as you are aware why you are doing it.


Michael Anthony has previously blogged about Progressive Disclosure which can be used to overcome the initial attention hurdle while the rest of the application can focus on delivering as accurate representation of the data as possible.


TL;DR Pie charts - bad. But sometimes good.

A number alone doesn't tell you very much – you need to compare it with something. And very often you want to compare this year’s number with last year’s.


It is called Year-over-Year (YoY).


In such a comparison, you can for example compare the sales of the current month with the sales for the same month last year. Or – if you want to avoid fluctuations due to good or bad months, you instead look at the accumulated sales in the current year compared the same period last year. You look at the Year-to-Date (YTD) number.


But how do you calculate it? How do you write a simple formula that picks out a subset of transactions from last year and compares them to the corresponding transactions from the current year?


If you have Month as dimension and show accumulated numbers in the chart, you don’t need to do anything. The numbers are comparable as they are.


Line chart months.png


However, if you don’t use Month as dimension, the numbers will no longer be comparable since last year contains transactions from a longer period. You still may want to make the comparison, but with another first dimension and Year as the second.


There are several ways to do this, and they differ in how the reference date is defined. One way is to let the user define an arbitrary reference date – either through a selection or through a variable – and then use this is an advanced Set Analysis expression.


Another, much simpler way is to use the date of the script run as reference date. If your application is refreshed every night, this would mean that the Year-to-Date calculation always is up until today’s date.


Here’s how you do it:


In your Master Calendar you should define flags – Boolean fields – that define whether or not a specific date should be included in the calculation:


   If( DayNumberOfYear(Date) <= DayNumberOfYear(Today()), 1, 0 ) as IsInYTD,


The above formula tests whether the date falls before today’s date or not. Note that this flag will be useful also for dates belonging to other years than the current. The value of the flag will be 1 for dates in the beginning of the year irrespective of which year it is.


Then you can use this flag in a simple Set Analysis expression:


   Sum( {$<IsInYTD={1}>} Amount )


The Set Analysis expression will pick out the correct dates and thus the correct transactions for the comparison. Further, this expression can be combined with any dimensions.


Bar chart Product.png


Flags for a number of different time periods can be created like this, not just Year-to-Date, but also Quarter-to-Date, Month-to-Date, Current Month, Last Month, etc.


   If( DayNumberOfQuarter(Date) <= DayNumberOfQuarter(Today()), 1, 0) as IsInQTD,

   If( Day(Date) <= Day(Today()), 1, 0) as IsInMTD,

   If( Month(Date) = Month(Today()), 1, 0) as IsCurrentMonth,

   If( Month(AddMonths(Date,1)) = Month(Today()), 1, 0) as IsLastMonth,


Summary: Create the necessary flags in your Master Calendar. It will simplify your Set Analysis expressions tremendously.



As QlikView developers, one of our main focuses is on the user experience. There are many instances where objects only need to be shown upon the user’s request. An example of this is list boxes (filters). With the use of conditional expressions, a QlikView developer can make the list boxes available when the user needs them.


I have seen too many applications where list boxes take up most of the design area available to the developer. Invariably you get the same response, “It is what the users want”. And while I am sure they “need” all of the list boxes, they just do not need them on the screen the entire time.  So how do you get around this demand for list boxes? Create a “Filters” button and have the user show and hide the list boxes whenever they want.


For this first example, I will reference the Workforce Management demo. On the dashboard below there are no visible list boxes. The lack of list boxes opens up more space to the developer. Instead of making the list boxes available at all times, we have a Filters button which, when invoked, will display the filters over top of the visualizations. Once the user makes the appropriate selections, he/she can close the filter panel and continue with his/her discovery.  This is done by setting a variable and conditionally showing the objects based on the current value of the variable. The beauty of this approach is that the user gets to have the filters that he/she needs and the developer does not lose any display area.


Here you can see the filter button with a conditional show. When invoked, there is an action to change the variable to 1 which makes the filters button disappear and makes the filter objects appear.



All of the filter objects have the same show conditional. When the user selects the close button, the variable is changed back to 0 which makes the filter objects disappear and the Filters button reappear.



This approach can also be used if you need to show/hide help/information pop-ups as shown here in the IT Asset Management demo.




If you would like to see how the filter and the help/information buttons were developed, the QVWs can be downloaded from the demo site. As was stated earlier, the filter example referenced the Workforce Management demo and the Help/Info button referenced the IT Asset Management demo. Happy Qliking!

When you use a platform that consists of several services such as QlikView, you need to build trust into the platform to make it secure.  The trust will protect the platform from external threats pretending to be part of the QlikView installation to get access to information.


Every time you go from one trust zone to another you need to cross a trust boundary and that means that you need to authenticate. Examples of when you cross trust boundaries in QlikView include going from your computer trust zone to the application using the browser, using the browser to access the administrative interface, loading data from a data source into QlikView, and each time you need to authenticate.


In QlikView, there are two ways to create the internal trust zone between the QlikView Services:

  1. Windows Integrated Security: the services will authenticate to each other through the use of Windows built in Security.
  2. Certificate Security: each service needs to have a valid certificate from the QlikView installation to be allowed to communicate.


Both of these create trust between the services and hinder unauthorized computers from being able to interact with the QlikView Services, but there are situations where one is better than the other.


You need to protect the communication between the QlikView services from eavesdropping
In this case you should choose certificate trust because that also enables encryption of all traffic between services using SSL/TLS.


You only use Windows infrastructure
Choose Windows Integrated Security as this will be where you have knowledge and it is easiest for you to setup.


All QlikView services do not have access to the Active Directory or you dont have an Active Directory
Use certificates these will function without a Windows Active Directory.


It is also important to understand that you cannot mix how you create trust in QlikView; either you use Windows Integrated Security or you use Certificates.


When you have chosen the most appropriate way of creating trust, look through the technical requirements found in the Server reference manual before installing to make sure it fits your needs and environment.


To summarize, QlikView has two ways to create trust between services.  Both have their benefits; however there are use cases when one is preferred over the other. Which have you used and what benefits have you seen?  I would be interested in your comments and questions!

Filter Blog

By date:
By tag: