Of the 2 ½ years I have been with QlikTech, I have never had to use the IntervalMatch prefix in any of my scripts but I recently found out how powerful it can be. I was tasked with creating a Profit and Loss (P&L) statement in QlikView based on the format outlined in this Excel file.The “s” in the Exec P&L Level column indicates blank rowsThe “c” in the Exec P&L Calculation column indicates rows where a calculation needs to be performedThe Exec P&L Heading column stores the headings that are to be used in the P&L statementThe ExecPLStart and ExecPLEnd columns indicate the range of rows that are to be summedWho would have thought something as simple as IntervalMatch would solve my problem? I have always said that I learn something new about QlikView every day and this day was no different. I was able to load my master account information, my account balance information and then perform the IntervalMatch, using the reporting code field from the master account table, to get everything in sync so that the correct rows were summed in the P&L statement. Then all I needed to do was create my chart (a straight table) in QlikView. In the end my chart looked like the image below and I was quite pleased.I wrote a technical brief about how I completed this task. I know this is just one of the many ways IntervalMatch can be used. I am sure in the future I will learn some of the other ways. ...View More

There is a little known function in QlikView that hardly anyone uses and that doesn’t do very much, but still has a tremendous impact on many of the calculations made in QlikView. It is the Only() function. It returns the value of a parameter – but only if there is just one possible value. Hence, if you have a one-to-one relationship between the chart dimension and the parameter, the Only() function returns the only possible value back. But if there are several values, it returns NULL. The Only() function is an aggregation function, which means that it uses many records as input and returns one value only. The Sum() and Count() functions are examples of other aggregation functions. Aggregations are used whenever you have a group of records and need to show only one value representing all records. When you think about it, QlikView uses aggregations in virtually all calculations: The expression in a chart, in a sort expression, in a text box, in an advanced search and in a calculated label are all aggregations and cannot be calculated without involving an aggregation function. But what if the user enters an expression that lacks an explicit aggregation function? What does QlikView do then? For example, if the sort expression is set to “Date”? Or if there is an advanced search for customers using the expression “=Product='Shoe' ” (the intent is to find customers that have bought this product)? This is where the Only() function affects the calculation without the user knowing it; if there is no explicit aggregation function in the expression, QlikView uses the Only() function implicitly. Hence, in the above cases, “Only(Date)” is used as sort expression and “=Only(Product)='Shoe' ” is used as search criterion. Sometimes the new expression returns a result that the user does not expect. Both the above examples will work fine for cases when there is only one possible value of Date or Product, but neither of them will work for cases when there is more than one value. Therefore, when you write expressions you should always ask yourself which aggregation you want to use, or: Which value do you want to use if there are several values? If the answer is that you want to use NULL to represent several values, then you indeed want to use the Only() function and you can leave the expression as it is. But if you do not know the answer, then you should probably think again. For numbers, you probably want to use Sum(), Avg() or Min() instead and for strings you may want to use Only() or MinString(). For debugging you can always use something like, “Concat(distinct <Field>, ',')” and analyze the result. But you should not leave your expression without an aggregation function. HIC Further reading related to this topic: It’s all Aggregations Use Aggregation Functions! ...View More

I've been a designer for about 10 years, 8 of which I've been designing web sites. The fancy buzz-word way of saying you work on websites is to say that you work in "User Experience Design." The user experience is how someone (a user) might interact with something (a website, an ATM, or in this case QlikView). You consider what troubles a user might encounter, what might be helpful, how they will accomplish certain tasks, etc. From Amazon to Zappos, User Experience Design is the (not so) secret ingredient that separates the bad experiences from great experiences with loyal customers.So what makes User Experience Design work? One of the keys is to be empathetic, to put yourself in the shoes of the user and design for them and not yourself. You design for and with others, that is you don't do it alone. Designers are just one group of people who take a website from conception to completion. Information Architects, Writers, Designers, Developers, and Usability Engineers all contribute to the process. People with specialized disciplines doing what they do best to make great experiences. If you aren't lucky enough to have a team of bright UX professionals, it probably means you will have to become a "jack of all trades" and take on all of these roles yourself.Never fear. As a guide I've written the attached Technical Paper that walks you through how you can apply the iterative UX waterfall design process to developing QlikView applications. There is a lot of overlap between designing a website and designing a QlikView application because at the heart of both is the user experience. Designing for what is best for the user is key no matter what the final product.Next Steps? Start small. I would recommend trying these ideas on a smaller, easier project first and then apply what works for you to larger projects over time. ...View More

Since we introduced the first version of the QlikView SAP connector 6 years ago, many of the QlikView customers leveraged the power of QlikView with the SAP data. Through QlikView’s unique, in-memory associative technology, they are able to make SAP based business decisions with the speed of the business.The key enabler of the solution is the certified QlikView SAP Netweaver® Connector, which can extract data from SAP® R/3®, mySAP™, SAP BW, and BEX queries into QlikView. The extracted data can be combined with non-SAP data in one QlikView application providing a 360 degree view of the business.Let’s take a look at a business case and understand how the QlikView SAP Netweaver® Connector would help. Assume a company has various SAP Netweaver data stores and its users would like to do sales analysis across their business. With SAP Netweaver® Connector they can connect and extract data from;SAP BW/BI Business Explorer Query for sales informationSAP Data Store Object (DSO) for additional order values and shipping weightsAn SAP Query to gain insight into sales organization detail metricsAn SAP R/3 Table so they can integrate the company details into Google mapsAn SAP Extractor for customer itemsAn SAP BAPI for business area lookupsAn SAP Report for customer payments They can then create visual and flexible analysis with this data in one single QlikView application which can also integrate data from external non-SAP data sources.QlikView provides pre-packaged QlikStart templates for SAP R/3 as well as QlikView SAP data dictionary and QlikView script builder applications. These apps would give a good head start for IT by delivering the background database information of how tables within SAP interact. It would also enable the business users to quickly realize their own data in ways they may not have envisioned before.Last month we introduced a new version of the QlikView SAP Netweaver® Connector (SAP Netweaver version 5.7) with even more capabilities. If you would like to have a jump start to do Business Discovery with your SAP data, you can download the QlikStart templates from QlikCommunity. ...View More

Are you colourblind? Do you actually know if you’re colourblind? On the 3 occasions I have presented on this topic I have had a two male attendees admit to not knowing….. (until now...) that they were colourblind to some degree.It is estimated that approximately 8% of men and 0.5% of women are colourblind. This large difference in probability is due to the fact that the genes that produce photopigments are carried on the X chromosome; if some of these genes are missing or damaged, color blindness will be expressed in males with a higher probability than in females because males only have one X chromosome, whereas women have two!(http://en.wikipedia.org/wiki/Color_blindness)The most common way to determine level of colourblindness is to take an Ishihara test. The test consists of a number of plates that contain patterns of different coloured/shaded dots. These dots form to show numbers and shapes that you can…. or in the case of colour blind people…. can’t see.Can you see a number in this plate…… I can’t…. I am actually colourblind, which is quite ironic considering I work with QlikView visualisations every day. A person with “normal” vision should see the number 6, colourblind people will see nothing.You can take the tests online on a number of websites :http://www.tedmontgomery.com/the_eye/colortst/colortst.htmlhttp://colorvisiontesting.com/ishihara.htmhttp://www.colour-blindness.com/colour-blindness-tests/ishihara-colour-test-plates/One great example I found is the image below. I cannot see any difference between the two images due to me being Red-Green colourblind. People in the office who have perfect vision could see a massive difference in the colours.…(Source: http://facweb.cs.depaul.edu/sgrais/colorvisiondefi.htm)I often find that colour and the use of colour is something that is decided upon without much thought, hence me writing this. We have to be very careful when we decide on the colours we use in our charts and dashboards. In your QlikView objects, really think carefully about colours and the impact your choices will have on your users. Ensure you use colour blind friendly palettes, and avoid using colours that are “close” together in tone, next to each other in charts such as scatter and bar charts.There are a number of resources on the web to help you choose friendly colour palettes..http://jiminy.medialab.sciences-po.fr/tools/palettes/http://colorbrewer2.org/So, choose your colours wisely, try not to use colours that are “close” together. Utilise the web resources to help determine your palette. If, like me, you are colour blind then these resources are a massive help!Remember the number…. 8% of males are colourblind…this is a big number. This 8% could be the key decision makes or indeed the developers.Wherever possible, colour should purvey meaning, not just used because it looks nice!See more about this topic in this techincal brief ColourABY ...View More

Software development is a learning process. In the development you sometimes reach a point when you realize how you should have done it in the first place and as a consequence you want to re-write all or parts of the code from scratch. In 1996, when starting the development for QlikView 2, we were in such a situation. We had learned a lot from coding 32-bit Windows code (which was still fairly new) and we had seen that the older Single Document Instance (SDI) paradigm was not what we wanted to have in the future: SDI programs could only hold one document open at a time. We wanted the Multiple Document Instance (MDI) paradigm instead. To make a long story short: We re-wrote large parts of the code and as a consequence, we had problems with the product stability. We had to postpone many of the planned features and spend time bug fixing instead. The features included in version 2.0 were hence few, the major ones being MDI interface, the introduction of the button object and the possibility to have several dimensions and several expressions in charts. With the button object you could e.g. export data to Excel. And, yes, we discontinued the 16-bit version of QlikView. We saw that the future lay in 32-bit code. Also, and more specifically for QlikView, we saw that memory was getting cheaper and started realizing that it was possible to perform analysis down to the transactional level even for fairly large data sets – but for this we needed 32-bit code to be able to load the larger amounts of data. After the release of version 2.0, we started working on the postponed features – and they were many! When we got closer to the release of 2.1, we noted that the new version would have little in common with the 2.0 version. So someone (from marketing) suggested we’d number the new version “2.5” instead. Here you must understand that the QlikTech company culture contains a large element of frankness. If you don’t agree, you speak up; you say your meaning. So at the suggested jump from 2.0 to 2.5, there were people – techies including myself – that expressed their dissatisfaction over the inconsistent version numbering. An animated argument followed where no side got the upper hand. The people in favor of jumping to 2.5 were just as stubborn as we. In these early days, such decisions were always made in consensus. But now we were in a deadlock. The compromise was to release neither a version 2.1 nor a version 2.5. Instead we labeled the new version “3.0”, which in retrospect was a good decision. But more about that in a later blog. Bottom line is, that QlikView 2 had a very short lifetime and did not have a big impact at all. But it did pave the road for versions to come: We had re-written the code and the new 32-bit architecture was sound and allowed to be built further upon. HIC Further reading on Qlik history: A Historical Odyssey: QlikView 1 A Historical Odyssey: QlikView 3 ...View More

Few things are as important to understand as the concept of nothingness. Or, rather, the fact that there are always many levels of nothingness. In physics, vacuum is the word used for nothingness. But whereas the best vacuum on earth contains billions of molecules per cubic meter, vacuum in outer space contains fewer than a dozen. So, these two vacua are completely different. And neither is really empty. What if we find some space completely void of molecules? Would that represent nothingness? No, because the space would still be traversed by force fields, e.g., gravitation from distant stars. But space void of force fields, then? No, you would still have vacuum fluctuations, a quantum mechanical effect that can create particles from nothing. True nothingness may perhaps not exist. But one thing we know for sure is that there are levels of nothingness; one vacuum is not the same as the other. In Lund there is a statue of Nothingness (Swedish: “Intighet”). There is nothing there, except the void of statue. But the statue’s existence is shown by a small plaque in the ground. To complicate matters, there is a second plaque some centimeters away that announces that the statue has been stolen. The two plaques illustrate both the sense of humor in the student city of Lund and the universal existence of different levels of nothingness. In databases and in QlikView, NULL is the word used for nothingness. But this is not the only type of nothingness. Also here you have different levels: The simplest representation of nothingness is the 0 (zero). But this is not true nothingness, since there is a numeric value in the field. The value will be used for calculations, e.g., it will affect both Count() and Avg(). So it is certainly not NULL. Another level of nothingness is the empty string. This may not be as obvious, but also this is a field value that affects the calculation of Count(). Hence still not NULL. The next level is the true NULL. This is when you have a record in the database, but there is no value for the specific field. This cell in the table is marked as NULL, meaning “a value is missing here.” The final level is when the entire record is missing. An example is if you have a customer table and an order table and a specific customer has not placed any orders. Then the customer is not represented in the order table and there is no table cell that can be marked as NULL. These are called Missing values and are treated the same as NULL values – when possible. If you want to present data in a correct way and at the same time enable the user to search for missing values, e.g., customers that have not bought a specific product, you need to understand the different cases of nothingness. Nothing could be more important. HIC More on nothingness: NULL – The Invisible Nothing Finding NULL Excluding values in Set Analysis Also, see more about this topic in this Technical Brief: NULL and Nothingness ...View More

QlikView is about empowering the user. Allowing a user to freely choose what to look at and how to look at it is one of the cornerstones of Business Discovery. Another is about simplicity – which often translates into removing unnecessary technical obstacles so that the user can focus on the data and the information that lies hidden in it. This often leads to a situation where you have two different user roles: an application developer and a business user, where the application developer takes care of the data modeling aspects of the analysis and the user interacts with the data, asks questions and finds new areas of exploration. In such a situation it is important that the developer treads carefully – he or she must remove technical obstacles without limiting the user. However, in an effort to simplify, application developers sometimes complicate things without realizing it. Let me give you a couple of examples. Macros and Actions are features that unfortunately sometimes are used to “help” the user make the right selection. I have often seen developers create buttons that make selections and activate other sheets, or triggers that clear selections and make new selections when you leave a sheet. Such constructs are confusing for the user, who doesn’t understand why these new selections happen. Macros and Actions often obscure the QlikView logic and prevent the user from learning how to interact with data on their own. Another area where the power to select can be taken away from the user is when Set Analysis is used. In set analysis, the developer can define a formula with an arbitrary selection that overrides the one made by the user. Often the new selection is based on the user selection. Set analysis is necessary for calculations that extend outside the user-made selection, e.g., if the user wants to make a year-to-date calculation for the time up to the selected month. Properly used, it is an extremely powerful tool that enhances the user's ability to find information in data. But I have seen cases where set analysis instead replaced the user's selection and limited the his ability to interact with data. To hard code the selection inside a formula is in some cases disrespecting the user’s intelligence! My view is that navigation – choosing a sheet, activating sheet objects, expanding branches in pivot tables, and, most importantly, making selections – should be left to the user. Constructions that “help” the user in this area usually have the opposite effect. Instead of helping the user, they often complicate the interaction with data and confuse the user. I am convinced that macros, actions and set analysis sometimes must be used, but these features should be used with caution. They should never be used for things that the user can do better. We live in an age of empowerment, and users want to be free to explore data in their own way and on their own. QlikView should allow them to do just that. HIC ...View More

1994 was the year when the Swedish soccer team took bronze in the world championships. It was also the year the channel tunnel opened. Boris Yeltsin was the Russian president and Bill Clinton was one year into his first term. 1994 was also the year when the first version of QlikView - or QuikView - was released. Version 1.0 had only the logical inference between fields – it had no graphics capabilities and no calculations were possible. But during the lifetime of QlikView 1 graphics and numeric calculations were added and by the release of version 1.42, QlikView had the same basic structure as we still have today: a multi-table relational data model, a logical inference engine, and graphs that hold no data of their own but instead are calculated on the fly based on the result of the logical inference. Further, the conceptual idea of an “app” came with QlikView 1: the holy document. A QlikView document is in its basic form still today a self-contained file that holds all necessary information: a snapshot of data, layout information, and information on how the data should be refreshed. A user can have several documents, each corresponding to a specific area of the data. The document can be mailed to other users and no installation is necessary. This approach ensured portability and has been key to simplifying backward and forward compatibility as well as compatibility between QlikView Desktop and QlikView Server. The mid nineties were also the time when 32-bit Windows software started to emerge. Most programs, as well as Windows 3.1 itself, were only 16-bit, but if you installed the Microsoft Win32s module, you could also run 32-bit programs. Consequently, several of the QlikView 1 releases were produced in both a 16-bit version and a 32-bit version. In 1995 Microsoft released Windows 95 and although we today are not very impressed by this operating system, it was at the time a big leap forward. After that, 32-bit programs became standard. With the 16-bit QlikView you could only have 16,000 distinct values in a field and 65,000 records in a table. These limits were however not a huge problem because most of the analysis in those days was made on data sets with few distinct values and often with pre-aggregated data. By the introduction of the 32-bit QlikView, these limits were removed and this opened up the field for transactional analysis. It would take many years before the new limit of 2GB memory would become a problem. Much of what was invented in QlikView 1 is still there today, in QlikView 11. In fact, the principal features from QlikView 1 are the core of the modern QlikView; they are the foundation of how QlikView still works today. However, there is one thing from QlikView 1 that I miss – the marble background… HIC Further reading on the Qlik history: A Historical Odyssey: Quality - Learning - Interaction - Knowledge A Historical Odyssey: QlikView 2 ...View More

After doing a couple demos recently, I noticed that some of our customers are not aware of the repository panel. It came to me as a surprise as repository panel was introduced with QlikView 10. The repository is a QlikView app level entity that lists all sheet objects, dimensions and expressions that have been used within the current app. The repository panel is an AJAX client only entity. A user can view the content of the repository and elect to reuse any existing chart or definition by dragging and dropping that definition out on to a sheet. A chart can be pulled from the repository panel. Exposing its properties, a user can then change the dimensionality or measures within the chart. Instant feedback gives the user immediate insight into what they have created.How does using the repository panel benefit the users? A common reaction from IT and business process owners alike is that they may not trust their users to have the know-how to create QlikView objects. The repository panel aims to eliminate these fears by giving an opportunity to train users to reuse what is already in their app.On my previous blog post, I explained how to setup comparative analysis on a QlikView app. I have been thinking on how to make it easier for the business users to assign QlikView objects to different alternate states and I came up with this idea of using the container object and the repository panel. The video shows the details of setting up the solution.In the solution, I used multiple container objects where each of them is assigned to a different state. I set the presentation type of the container objects to grid mode. By using the repository panel, the users can drag and drop different objects into the container objects. As objects in a container object inherit the alternate state, the user can then create different selection states in each container object and visually compare the charts for these different selections.This solution is a great example of showing how QlikView enables business users to remix and reassemble data in new views and create new comparisons on the fly for deeper understanding! ...View More