Henric Cronström

Macros are Bad

Posted by Henric Cronström Apr 29, 2014

There are several good reasons not to use macros in QlikView.


First of all, macros are run using Microsoft COM Automation. They will run client-side and work fine if you use the Desktop or the IE PlugIn, which both are Windows executables. But if you use the Ajax client, there are severe limitations since the macros now need to run on the server instead of on the client. For a server, there are both technical limitations and security implications that have forced us to restrict the capabilities of macros.

So, if you want to use the Ajax client, you should avoid macros.




Secondly, a common beginner’s mistake is to use macros to build logic into QlikView; logic that almost always works better if QlikView’s own out-of-the-box functionality is used.  An example is assigning a value to a variable every time a selection is made: In other tools you would use a trigger and a macro, but in QlikView this is not needed: You should instead define your variable using an equal sign and the appropriate expression.


Further, many problems can be solved by using a smart data model instead of macros.


Macros often confuse the user. A user wants a predictable behavior and macros often do exactly the opposite: They create behaviors that exist in some places but not in other. This is counter-intuitive.


Finally, if the macro is long or complex, it will often push QlikView into an unstable situation. We have often seen this in documents sent to our support organization. The reason is probably a poor fit between the synchronous, sequential execution of a macro and QlikView’s internal asynchronous, multi-threaded computation.


From a development perspective, the macro feature is a secondary functionality. In the development process, it will rarely get first priority when compared to the “native” functionality: I.e. in the choice between keeping macro functionality unchanged and improving the out-of-the-box functionality, the QlikView development team will often choose the latter. This means that you can get a changed macro behavior when you upgrade your QlikView installation.




Some facts around macros:

  • Single-Threaded? Macros are in their nature sequential – i.e. in a way single-threaded. But this is not the same as saying that all calculations started by macros are single-threaded. For instance, if a macro makes a selection that causes a chart to be calculated, both the logical inference and the chart calculation are multi-threaded. As they should be.
  • Clears cache? No. A macro does not clear the cache. (Unless you use the macro in the picture above...)
  • Clears the Back-Forward stack? No. A macro does not clear the Back-Forward stack.
  • Clears the Undo-Redo stack? Yes, if the macro contains layout changes, the Undo-Redo stack will be cleared.


So, I strongly recommend you don't use macros, unless it is in a Desktop environment. And there they should be short and concise! On a server you should instead use Actions.


There is however one thing that may confuse the user more than macros. And that is Triggers. If a macro or an action is started from a nicely labeled button, then the user will understand what happens.


But if the macro instead is triggered by some other event, e.g. the changing of a variable value, we have a very different situation. Then you will most likely create a non-intuitive behavior.


Macros are Bad, but Triggers are Worse.




See also

The Key to Heaven

What does Class, Alt, Pick, If, Match, Mixmatch and Wildmatch all have in common?  They are all conditional functions that can be used in the QlikView script or in the user interface.  Personally, I have only used two of them – Class and If which is why I thought it would be interesting to learn a little more about them and their capabilities.  These functions return a value based on a comparison or a condition.  Let’s take a quick look at each of these.




Class allows you to create buckets/groupings of data.  For example, assume you have a data model with order data and there is a field with the number of days an order is late.  Using class, the number of days can be grouped based on an interval we select.  The expression below will create buckets with 30 day intervals:

Class script.png

The buckets will look like the image below.

Class buckets.png


The Alt function will return the first parameter that has a valid number representation (including dates).  So in the script below, I can use Alt to check the format of the dates in the TempDate field.  If the format matches one of the parameters, then it is returned otherwise the last parameter in the Alt function is returned.  The order of the parameters indicates the priority order so that can be used to determine how the dates should be interpreted.  For example, should 7/4/2014 be interpreted as the 4th of July in the US or the 7th of April in the UK?

Alt script.png

Here are the results:

Alt table.png

The TempDate is the original date and the ValidatedDate is the value returned by the Alt function.




The Pick function will return the expression/value corresponding to the expression that matches the first parameter.  For example, Pick(2, ‘A’, ‘B’, ‘C”) will return B because B is the second expression and Pick(Number, Sum(1+1), Sum(2+2), Sum(3+3), Sum(4+4), Sum(5+5)) will return 6 if Number = 3.  This function is excellent when you want to generate a random field value, e.g.

Pick script.png


Everyone has used an If statement at some point whether in QlikView or some other programming language.


If(condition, then , else)


The If statement check a condition (the first parameter) and if the condition is true, it returns the “then” parameter otherwise it returns the “else” parameter.


For example, if(1+1=2, ‘Woo hoo, I can add’, ‘Need more practice’).



Match, Mixmatch & Wildmatch

These functions are very similar in that they all perform a comparison.


  • The Match function does a case sensitive comparison between the first parameter and the expressions


          match( str, expr1 [ , expr2,...exprN ] )


          Match(X, ‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’)

          If X=Jan, 1 is returned

          If X=’Feb’, 2 is returned


  • The Mixmatch function does a case insensitive comparison between the first parameter and the expressions


          mixmatch( str, expr1 [ , expr2,...exprN ] )


          Mixmatch(X, ‘Jan’, ‘Feb’, ‘Mar’, ‘Apr’)

          If X=Jan, 1 is returned

          If X=’feb’, 2 is returned


  • The Wildmatch function also does a case insensitive comparison between the first parameter and the expressions and allows the use of wildcards


          wildmatch( str, expr1 [ , expr2,...exprN ] )


          Wildmatch(X, ‘Ja*’, ‘F?b’, ‘mar’, ‘Apr’)

          If X=jan, 1 is returned

          If X=Feb, 2 is returned

          If X=Mar, 3 is returned


Now that I know a little more about some of the other conditional functions available in QlikView maybe I will find the need to use them in my apps when I need to compare data or check a condition.  There are numerous ways these functions can be used besides what I discussed here so I am sure they will be useful in many of my future apps.  For more details and an example QVW of these conditional functions in action, check out my technical brief.




Starting with version 3.9.1 of QlikView Expressor we released a new capability that allowed developers to extend, share and package custom functionality using the QlikView Expressor Extensions SDK. The Extensions SDK is a facility that creates new QlikView Expressor artifacts such as operators, connections, schemas and more. Extensions can range from connectivity and transformation to Dataflow coordination and orchestration. Under the covers, the appropriate and desired operations would need to be coded using QlikView Expressor Datascript, but as you can see from some of the samples these can vary in degrees of complexity.


Prior to the release of the Extensions SDK - extending QlikView Expressor required Datascript within Expression Rules, Custom Read /  Write Operators as well as reusable QlikView Expressor Datascript modules. This continues to work well, but is not as structured or portable. With the Extensions SDK, extensions can be packaged and shipped to other environments to be installed with the QlikView Expressor Extensions Manager; allowing easily installation and sharing of the new custom functionality without having to maintain or use any Datascript.


Attached to this post I am providing a packaged developmental extension (RESTfulJSON.eex) that currently supports a RESTful API response in the form of JSON. The extension could be enhanced to to support other result formats such as CSV and XML.


Watch this companion video to learn how to use it and see it in action.



Note in order to use this, you should be familiar with RESTful APIs and there methods of querying and retrieving the results response/


An introduction to the QlikView Expressor Extensions SDK along with detailed information, tutorials and samples are available at the references listed below:



Michael Tarallo

Senior Product Marketing Manager

QlikView and QlikView Expressor

Follow me - @mtarallo

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 object or 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 usually not be purged until the RAM usage is close to or 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.




Further reading on the Qlik engine internals:

Symbol Tables and Bit-Stuffed Pointers

Colors, States and State vectors

Logical Inference and Aggregations

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().


Further reading related to this topic:



“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.

Filter Blog

By date:
By tag: