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

QlikView has an intelligent algorithm to recognize dates independently of which region you are in. In most cases, you will have no problems loading them. It just works and you do not need to think about it. However, in some cases dates are not properly recognized and then you need to add some code in the script to make it work. First of all – there are no data types in QlikView. Instead QlikView uses dual data storage for all field values; every field value is represented by a string and – if applicable – a number. The task of the developer is to make sure that QlikView recognizes the date correctly so that both a textual and a numeric part of the date are created. The numeric part of a date is a serial number (same as Excel), i.e. a number around 41000 for dates in the year 2012. Here are some tips that will help you load dates correctly and hopefully help you better understand how the date handling works. Use the interpretation functions If you have the date as text you may need to use an interpretation function, e.g. Date#() or Timestamp#(). Nest functions If you want to display the date a specific way, you may need to nest an interpretation function inside a formatting function, e.g. Date(Date#(DateField, 'YYYYMMDD'), 'M/D/YY'). Use the MakeDate function If you have Year, Month and Day as separate fields, use the MakeDate() function to create a date serial number. Use the rounding functions If you have a timestamp and you want a date, you should probably use a rounding function, e.g. Date(Floor(Timestamp#(DateTimeField, 'YYYYMMDD hh:mm:ss')), 'M/D/YY'). Use the numeric value in variables If you want to use the variable for comparisons, it is simpler to use the date serial number rather than the textual representation, e.g. Let vToday = Num( Today() ). Use combination fields, e.g. Year and Month as one field It is often practical to display both year and month in one field, e.g. Date(MonthStart(DateField),'YYYY-MMM') Use the Dual function If you want more complicated combinations of a string with an associated numeric value, you can do almost anything using the Dual() function. Use the Alt function for fields with mixed date formats: If you have a field with mixed date formats, you can resolve them using the Alt() function. For a more elaborate description of these tips, see the technical brief on QlikView dates. HIC ...View More

Have you ever wanted to create a Gantt chart in QlikView, only to find out that this chart type is not one of the pre-defined charts? Then you should be happy to learn that it is possible to create a Gantt chart and that it is not very difficult. To be able to create this chart, you need some type of event in the data model, where each event has a start date (or time) and possibly also an end date. Further, an event should belong to some grouping, typically a project or a phase in time. Given the above, we can now start making the graph: Create a bar chart with the appropriate event grouping as dimension (e.g., project, phase, or other group of events) Add the duration of the project as expression: Max(EndDate)-Min(StartDate). If you only have one date per event, you just replace both StartDate and EndDate with the date that you have. The duration will then be the time between the first event and the last. Add the start of the project as an offset to the bars: Min(StartDate). [Chart Properties-Expressions-The plus sign to the left of the Expression-Bar Offset] Set the graph orientation to horizontal bars. [Chart Properties-Style-Orientation] Remove the “Force 0” option. This option will force the start of the time axis to be Dec 30th 1899. [Chart Properties-Axes-Expression Axes-Force 0] Format the expression axis labels as dates or as months (e.g. M/D/YY or YYYY MMM). [Chart Properties-Number-Date] Now you should have a Gantt chart. If you want to improve the way it looks, you may want to consider the following suggestions: Add a grid. [Chart Properties-Axes-Expression Axes-Show grid] If you want a label on each bar, add a second expression defining the text for the label and use this as “Value on Data Points.” [Chart Properties-Expressions-Display Options] Don’t forget to disable the “Bar” option for this expression. This is the first check box in the Display Options group. If the chart shows a range which is too large, add static min and static max to define the display range of the expression, e.g. Min(StartDate)-5 and Max(EndDate)+40, respectively. The additional distance on the upper limit is to leave space for the text. The unit is number of days. [Chart Properties-Axes-Scale] If you have a second dimension, you can add this too. But if you do, make sure you use the “Grouped” option and not “Stacked.” The latter does not work well with bar offsets. [Chart Properties-Style-Subtype] You may want to use the same color within each value of the first dimension, e.g., one color per project. One good way to define the color (e.g. RGB values) is to do it already in the dimension table in the script and use this color in a color function in the bar background color, e.g. RGB (ProjR,ProjG,ProjB). [Chart Properties-Expressions-The plus sign to the left of the Expression-Background Color] This Gantt chart solves most cases where you need to display that duration of a group of events. Should you need a more complex Gantt chart, you may need to make it as an extension object instead. HIC ...View More

One of QlikView’s key differentiators is the associative experience, the ability for business users to easily navigate through data sets to not only find answers to their questions, but to also discover new insights, and spot hidden trends. Only QlikView provides business users with this level of flexibility and insight.Once the business users find a key insight or trend though, they might want to compare that with a slightly different view. For example, if they find that bike and accessory sales in Europe have flattened out over the last few quarters, they might wonder how that compares with the rest of Europe. Of course with QlikView they could immediately select the other European countries instead of France and immediately get the answer. But what if they wanted to see those two or more different views side by side?One of the approaches that are used in those situations is using set analysis. With set analysis, it is possible to create data groups in charts and use them for visual comparison. The limitation of set analysis is the person creating set analysis should know about the type of groups that the other users would like to compare and set it up in advance accordingly. QlikView 11 Comparative Analysis (Alternate State is the technical name of the feature) overcomes this challenge. The goal of Comparative Analysis is to make it easier and flexible for business users to see two or more data sets in the same application, alongside each other in the same graph, in graphs next to each other, or even as reference points for calculations and comparisons.Comparative Analysis is a developer enabled capability, meaning developers need to set up the basic framework for comparison in an application. But it is a user-driven feature; meaning business users can then define the selections they want to compare. This video shows how to create alternate states, assign QlikView objects to them, and the concept of inheritance of alternate states. I will post about more creative ways of using alternate states in a couple of weeks, stay tuned! ...View More

Have you ever wondered what it takes to create QlikView extensions? Here’s someone who knows: Brian Munz, QlikView extensions “imagineer,” as I think of him. His official title is web technology lead on the demo and best practices team. Brian was the lead developer for the current version of demo.qlik.com and the version of AccessPoint that ships with QlikView Server in QlikView 11.Since QlikView 10 (released October, 2010) QlikView has enabled customers and partners to create extensions; web developers can bring custom and third-party data functionality or visualizations into QlikView, where they become fully interactive just like native capabilities. “Some of the cooler extensions I’ve seen,” said Brian, “are very powerful in terms of utility. It’s not solely about custom visualizations. You can use extensions to connect on the back end to other products (like Google Analytics) or add a Twitter feed or button, or detect browsers and devices . . . those kinds of things.” Brian has built mapping extensions, which plot regions on map (colors and shades them) based on data points, using Google Maps and open source mapping tools. He also created a geolocation extension, which enables the app to find the user’s current location (check it out here). I asked Brian what it takes to build QlikView extensions and here’s the way he put it: “A lot of work is around answering the question, ‘What am I trying to show?’ Once you figure that out, it’s about connecting to the data. Web developers often deal with SQL and MySQL — with databases with rows and columns. With a QlikView extension, the data comes into the extension the same way. Once you get the syntax right for pulling the data out of the source system, you say, ‘Oh, I know how to do this. I've done it a million times before.’ You then have to decide how the extension should communicate back to QlikView. A web developer who has worked with Ajax or JavaScript APIs can learn to do this without too much headache.”To create extensions, you need some level of JavaScript coding proficiency. To create a fairly simple extension, you could search for code snippets and paste them together. If you want to do something more complicated, you need heavy-duty JavaScript coding skills. It also helps to have a general level of comfort with other Web technologies like CSS and HTML. How long it takes to build a typical extension object? “There really isn't ‘typical,’” Brian said. “It depends what the extension object is. If it's enormously complicated it could take months. If it’s simple, like a marquee that scrolls across the top of the screen, showing KPIs, that could be done in an hour. Creating an extension that’s a map that has different regions of the country showing up in different colors takes longer, compared to just integrating with an existing JavaScript library.” Building QlikView extensions can take as little or long as any web project.“The beauty of it,” Brian said, “is in the combination. Web technology is extremely powerful, and so is QlikView. Having them work together seamlessly increases the total full power of QlikView.” ...View More

Eighteen years ago I had just started a new job at QuikTech (no that’s not a typo) and my boss – one of the two founders – dropped a shrink-wrapped FoxPro database in my lap while saying: “You see, I have this idea about color coding information – green, white and gray. Can you see if you can achieve that with this database?” At the same time, two other people in the company were given the same task but with other tools – Excel and a multimedia tool called Authorware. Neither of us succeeded very well, so we decided to call a really good developer that two of us knew from university and ask him if he could do something in C++. A couple of months later we had the first version of QuikView, later renamed QlikView. It had list boxes and nothing else. No tables, no graphs, no aggregations, no numerical calculations. We sold these early versions to companies that needed logical visualization of data. Many things have changed since, but the core in QlikView is still the same: The logical inference engine; the user selects a field value and QlikView answers by indicating which other values are implied by the selection and which are excluded. QlikView was different from all other tools on the market in that it was not a number-crunching tool – it was a tool for visualizing logical relationships. QlikView is still today different from all other tools in this regard. In fact, the logical inference engine is what enables the user to explore data freely without predefined search paths, which is the core of Business Discovery. The first two years with QlikTech, I had many hats. At the same time I was the project manager, the product manager, the R&D manager, the technical writer and the pre-sales consultant. But we did not use these labels then. We did not have clear roles and flexibility was important. Since then, I have implemented numerous QlikView solutions and been heavily involved in defining new QlikView versions. Today, my responsibilities have moved toward communication about the product. Hence, the QlikView Design Blog. Here, I and other “QlikTechies” will write about how to build QlikView solutions. We will write about scripting, visual design, extensions, server configuration, do’s and don’ts, etc. It will sometimes be very technical and sometimes not. Please give us feedback on the content. Send us questions and suggestions. We are here for you. Welcome to the QlikView Design Blog! Henric Cronström, Technical Product Advocate ...View More