Colors are a very important and critical part of our lives. They not only give meaning to objects but also trigger feeling and emotions within us, influence perspective and affect our psychological being. The study of colors is very complex and a lot has been written and talked about the role and use of colors in various aspects of life. As QlikView application designers, we don’t have to study in depth the meaning and theories of colors but it is of utmost importance for us to be aware of the usability norms, best practices and social and cultural implication of colors to use them in a conscious and respectful way in our designs and data visualizations. It is very easy to fall in traps of using excessive color variations or inapt color schemes since we have such a wide range to choose from which leads us to falling for temptations of using colors as per our personal likes and dislikes. A few tips and tricks about how to use colors judiciously can not only help tremendously from falling into these traps that impair usability and deceive the user but also allows datasets to be layered in order to tell eloquent stories. The following guides can be applied to our data visualization for QlikView applications.1. Use minimal amount of colors in your designs and data visualization. Every application has a focal point or something to highlight. Overly colorful designs can tend to hide the focal point, giving an overall vibrancy to the design and hindering the user from focusing on important points in the data.2. Using Shades and tints of the same color while showing quantitative information instead of different hues of colors is preferable, like in the pie chart shown below. However, it is okay to use different color in the same chart when color is used as an indicator of information as long as the colors are limited to 3-5 and color choice is such that they don’t create visual noise. The example below shows how color is used as an indicator of information. 3. Avoid pure gradient rainbow color scales to show data. Because there is no inherent order in the scale, they all appear to come from different families. 4. Keep the colorblind audience in mind when choosing color schemes. Since a large portion of our audience might be colorblind, it is unaffordable to use colors which are not colorblind safe. Color Oracle(http://colororacle.org/index.html) is a good evaluation tool for colorblindness.5. In places where Red, Green, Yellow have to be used together, introduce other attributes such as icons so that color is not the only differentiating factor. These colors appear similar to colorblind people. 6. When using Red and green together, choose a green which is closer to blue in hue, this way tthe 2 colors can be differentiated easily by the colorblind users.Apart from the above basic usability guidelines, there are other things to consider while choosing color schemes for designs. Choice of colors is very subjective, however, this not only because of personal preference but can also due to deep rooted cultural and social connotations associated to colors that are imbedded within us. Colors hold different meanings in each culture and we should be conscious of those meaning when presenting to a global audience or a specific country. This way misinterpretations and offensive use of colors can be avoided.An elaborate insight on colors, their use and interpretations can be found in this Technical Brief. ...View More

QlikView 7 was a great version – the Desktop, the Server and the Publisher all worked fine, but… they were not quite enterprise ready. It was not always simple to integrate all components in an already complex enterprise environment. So, for QlikView 8, one focus was to drastically improve the enterprise readiness. Hence, QlikView 8 brought us a a number of server components and functions: An html based control panel A built-in web server: Previously it was necessary to have the Microsoft IIS installed. A Directory Services Controller: Previously it was possible to connect to the Microsoft AD only. QlikView Server authorization (DMS mode): Previously you could use the NTFS file system security only. Server clustering Another focus was on a light-weight, install-free client – the Ajax client. We had already started to experiment with this in the 7.5 version, but with QlikView 8 we launched it as one of our three basic clients: The C client (Desktop and Plug-In), the Java client and the Ajax client. The three clients had different capabilities – if you wanted full functionality, you chose the C client. On the other hand, there was some effort involved to install this, and if you wanted an install-free environment, you chose the Ajax client. The Java client was somewhere in between. The Ajax client was however not as dynamic as it is today. The web pages had to be created in a manual step and posted on the web server. We didn’t just add functionality; we also removed some: The Itanium processor had been launched some years earlier, and by now we had realized that its market share would not grow. So, for QlikView 8, we decided to discontinue the Itanium edition of QlikView. QlikView 8 also brought the Advanced search, the Hierarchy resolution, the Input fields, the License lease and Set Analysis. And, yes, we got the logo that we use still today. Finally, QlikView 8 also brought us the first steps towards collaboration. It started with a simple function: the possibility to mail a link with a selection to someone else. We already had local bookmarks and now realized that it would not be practical to store an entire selection in a URL. Instead, we should of course store the bookmark on the server, and mail a link to this bookmark. From there, it was only a small step to expand this idea and allow the user to create and share other types of objects also. QlikView 8 was, with the server components, the Set analysis and the collaboration a version that architecturally very much resembled today's QlikView. HIC Further reading on the Qlik history: A Historical Odyssey: QlikView 7, QVD files and the Aggr() function A Historical Odyssey: The Early Days of QlikView Publisher ...View More

I often see incorrect expressions being used in the QlikCommunity forum. Expressions that seem to work correctly – but really don’t… So, let me make this clear: Calculations in QlikView are aggregations. It doesn’t matter if it is a measure in a chart, or a calculated object label, or a show condition for an object, or a calculated color, or an expression search – all expressions in the user interface are evaluated as aggregations. (Except calculated dimensions, and some search strings.) This means that it is correct to use the Sum() function in an expression, since this is an aggregation function - a function that uses several records as input. But if you omit the aggregation function or use a scalar function only, e.g. RangeSum(), you can get an unexpected behavior. Basically, all field references should be wrapped in an aggregation function. The Aggr() function and some constructions using the total qualifier can even have several layers of aggregations. But if the created expression does not contain an aggregation function, the expression is ill-formed and potentially incorrect. Examples: =Sum(Amount) =Count(OrderID) These are both correct aggregations. Amount is wrapped in the Sum() function which will sum several records of the field Amount. OrderID is wrapped in the Count() function, which will count the records where OrderID has a value. =Only(OrderID) This is also a correct aggregation. OrderID is wrapped in the Only() function, which will return the OrderID if there is only one value, otherwise NULL. =OrderIDA single field reference is not an aggregation, so this is an ill-formed expression. But QlikView will not throw an error. Instead it will use the Only() function to interpret the field reference. I.e., if there is only one value, this value will be used. But if there are several possible values, NULL will be used. So, it depends on the circumstances whether an expression without aggregation function is correct or not. =If(Year=Year(Today()), Sum(Amount1), Sum(Amount2))Here, both the amounts are correctly wrapped in the Sum() function. But the first parameter of the if() function, the condition, is not. Hence, this is an ill-formed expression. If it is used in a place where there are several possible Years, the field reference will evaluate to NULL and the condition will be evaluated as FALSE, which is not what you want. Instead, you probably want to wrap the Year in the Min() or Max() function. =ProductGroup= 'Shoes' =IsNull(ProductGroup) These expressions can both be used as show conditions or as advanced searches. However, since there are no aggregation functions, the expressions are ill-formed. If you want to test whether there exists Shoes or NULL values among the field values, you probably want to use the following instead: =Count(If(ProductGroup= 'Shoes', ProductGroup))>0 =NullCount(ProductGroup)>0 Conclusions: An aggregation function is a function that returns a single value describing some property of several records in the data. All UI expressions, except calculated dimensions, are evaluated as aggregations. All field references in expressions must be wrapped in an aggregation function. If they aren’t, QlikView will use the Only() function. HIC Further reading related to this topic: The Only Function Use Aggregation Functions! ...View More

Mobile access to information is becoming big business. While the share of the mobile market depends on what study you read, a 2012 study by Chitika said US mobile devices accounted for about 20% of all US internet traffic. Of that 20% smartphones were 14.6% while tablets were 5.6%. These numbers are all up from previous years. As the number of devices increases so too does the power of these devices. Mobile devices are moving away from being solely information consumption devices and into the realm of information creation devices. As the hardware becomes more powerful, and the user experiences become more sophisticated, our mobile devices are allowing us access to interact with information from anywhere. With this groundswell in mobile are increased expectations from users for well-designed, considerate, intelligent designs that work well on the device at hand. Smartphone experiences shouldn’t be the same as desktop experiences but it should be just as good. The usability considerations for how we interact with these devices can be very different from device to device.The attached technical paper begins to address some mobile usability considerations. The summary of this paper is that:• The conversation around mobile devices is less about Consumption vs. Creation and more about Task Complexity vs. Task Duration.• Properly sized designs for the desktop experience will also work for the tablet experience.• Smartphones want customized experiences.• Progressive disclosure is more important than ever on smartphones.• How people interact with touch-based devices influences success rate of applications.• Leave room to enable scrolling. ...View More

If you want to display an average number of something in QlikView, you should use the Avg() function, right? Wrong. Yes, there is an Avg() function that returns the average value, but – this is usually not the value that you are looking for. The Avg() function returns the average transactional value, whereas you probably are looking for a larger amount. For example, let’s say that you have an orders database where the grain - the most atomic level – of the data is Order Line. Each order can have several order lines and each order line has an amount. Then, the Avg() function will return the average order line amount, which is utterly uninteresting. No, you are most likely more interested in questions like “How much do we sell per month, on the average?” In other words – a calculation of an average has an implicit internal grouping entity; the average per month, per day, per order, per delivery or something else. You can look at it as a two-step aggregation: Sum all the amounts – per each value of the internal grouping entity (e.g. month, day, order or delivery) Calculate the average of the sums from previous bullet. In QlikView, you would calculate the average monthly sales value in one of the two following ways: Sum( Amount ) / Count( distinct MonthID ) Avg( Aggr( Sum(Amount), MonthID ) ) … and similarly for orders, days or deliveries. Use of the Aggr() function will work, but it is not as fast as the first option, and should therefore be avoided. Sometimes there are several internal grouping entities. You may for instance want to show the average monthly sales value per customer, i.e. you want to use both month and customer as grouping entity. Then you should use one of the following expressions: Sum( Amount ) / Count( distinct MonthID & '|' & CustomerID ) Avg( Aggr( Sum(Amount), MonthID, CustomerID ) ) The Count() aggregation with a string concatenation will find every unique combination of month and customer. Note that the internal grouping entity has nothing to do with the dimension you choose to use when you display it. It would make perfect sense to show the above number using Product as the only dimension, as shown in the graph below. Hence, the internal grouping entity is not necessarily visible in the end result. So, you need to figure out which internal grouping entity you want and then use this in a smart way in your expression. And by the way – an internal grouping entity can be used also for other aggregation functions: Smallest, largest, most common: Min(), Max(), Mode(). But for these, you will need to use the Aggr() function. HIC ...View More

Most people who create a QlikView application do so to help analyze their business data. And while business is the main focus when analyzing data, there is an equally important segment of data that gets overlooked and that is personal or recreational data. Many times this data is captured in an Excel spreadsheet and beginner QlikView users might need to know how to load their spreadsheets into QlikView. In this blog I want to run through two scenarios of how I used QlikView to help me analyze some recreational data.Scenario 1 - In my personal life I am a coach. I coach youth football, basketball and baseball. This past spring, while coaching my son’s 9-10yr old baseball team, I had the grand idea to analyze the team’s game data in QlikView. So, after each game, I took the data that I captured on our scorebook and entered it into an Excel spreadsheet. Each game had its own tab within the spreadsheet. After entering the data in to Excel, I would then load it into QlikView. Once the data was in QlikView, I created calculations that analyzed the productivity of my hitters. I tracked On-Base %, Batting Average, In-Play% and K/AB%. As the season progressed, and I had more game data to analyze, I started using QlikView to create my batting order. My mindset was that my top on-base hitters batted 1-3, my top in-play hitters batted 4-7, and my weaker hitters batted 8-11. It was a youth version of Moneyball and I was the team’s Billy Beane. So what did all of the data crunching get us? Well, while we averaged over 9 runs per game during the season, we did not win the championship. My next order of business is to see how QlikView can help my team improve our fielding. Scenario 2 – As I mentioned previously, I volunteer my time with youth sports organizations. Many of you know that youth organizations are administered by volunteers, many of whom have full-time jobs and families. So any amount of added help is greatly appreciated. Many of these organizations use Excel spreadsheets to keep track of the finances. Trying to analyze that data takes lots of time and energy from volunteers who are already stretched too thin. This is where QlikView can help. QlikView allows the administrators to visualize expenses and revenues to get a better understanding of the money flow within the organization. I helped one youth organization complete this task and it was a real eye-opener to see just how much money was brought into the organization and where it was all spent. Those are just two ways that I have utilized QlikView to help me analyze recreational data. And for those of you wondering how I loaded my Excel spreadsheets into QlikView, I wrote a technical paper that will explain the process. ...View More

The Master Calendar table is a central component in many QlikView applications: It is a dimension table listing different calendar attributes such as Year, Month, Day, etc.

But what about time attributes, such as hours, minutes and seconds? How should these be handled? Should these also be included in the Master Calendar? Or should you create a Master Time table?

...View More

Have you ever wished the QlikView list box used a logical AND instead of a logical OR when you made selections? Well, AND-Mode is a hidden QlikView gem that allows a list box to be changed from the default logical OR to a logical AND. By default, if one or more items are selected from a list box, the other fields will display data that is associated with any of the selections. When using AND-Mode, the other fields will display data associated with all the selections.In order to add AND-Mode functionality to your QlikView application, you need to:First, include a table in the data model that stores the field that will be used in the AND-Mode list box. The records in this table must be distinct and the table can only have two fields, one of which is a key field and the other which is the field used in the AND-Mode list box. Here is an example of what this data model may look like: Second, in the user interface, add a list box and check the And mode checkbox on the General tab of the Properties window to enable the AND-Mode functionality.Now the list box will use AND logic versus OR logic. In the screenshot below, I can use the AND-Mode list box to view customers who purchased both Bologna and Cheese. Notice the amperstand (&) in front of the selected values indicating that we are using AND-Mode.You can go one step further with the AND-Mode functionality and make NOT selections. For instance, I can see which customers purchased Bologna and Cheese and not Sliced Bread by also selecting Sliced Bread from the AND-Mode list box. To make a NOT selection, you need to click and hold the selection a moment until it turns red as seen in the image below.Now I can see customers who purchased Bologna and Cheese but did not purchase Sliced Bread. AND-Mode is quite powerful and can be added to any list box as long as the data model criteria are met. Check out this technical brief for step-by-step details of how to implement AND-Mode functionality into your QlikView application.Jennell ...View More

Every time you click, the Qlik engine recalculates everything. Everything. A new selection implies a new situation: Other field values than before are possible; other summations need to be made; the charts and the KPIs get other values than before. The state vectors and the objects are invalidated. Everything needs to be recalculated since this is what the user demands. Well, there is of course a cache also – so that the Qlik engine doesn’t have to recalculate something which has been calculated before. So it isn’t quite true that everything is recalculated: If a calculation has been made before, the result is simply fetched from the cache. But it is true that nothing is pre-calculated. There is no need for that. Everything can be done in real-time. The Qlik engine is an on-demand calculation engine. From a principal point, there are two steps in the recalculation of data: The logical inference in the data model, and the calculations of all objects, including sheet labels and alerts. The logical inference is done first. The goal is to figure out which field values in the symbol tables are possible and which records in the data tables are possible, given the new selection. There is no number crunching involved - it is a purely logical process. The result is stored in the state vectors. Think of it as if the selection propagates from one table in the data model to all other tables. Table after table is evaluated and the Qlik engine figures out which values and records are possible, and which are excluded. When the logical inference is done, the Qlik engine starts to evaluate all exposed objects. List boxes and dimensions in charts must be populated and sorted. All expressions – in charts, in text boxes, in labels, in alerts – must be calculated. Objects that are on other sheets, minimized or hidden, are however not calculated. The calculations are always aggregations based on the data records that have been marked as possible by the logical inference engine. I.e., the objects do not persist any data on their own. The calculation phase is usually the phase that takes time – often over 90% of the response time is due to calculations. The calculations are asynchronous and multi-threaded on several levels: First of all, every object is calculated in its own thread. Secondly, in the 64-bit version, many aggregations e.g. Sum() are calculated using several threads, so that a sum in one single object can be calculated quickly using several CPUs. Finally, when an object has been calculated, it is rendered. Since the calculation is asynchronous and multi-threaded, some objects are rendered long before other objects are ready. And when an object has been rendered, you can click again. And everything is repeated. HIC PS. All of the above is of course true for both QlikView and Qlik Sense. Both use the same engine. If you want to read more about the Qlik engine internals, see Symbol Tables and Bit-Stuffed Pointers Colors, States and State vectors The Calculation Engine ...View More

My first blog post was about 4 tips to start working with QlikView, the title was right but it was incomplete in a way. I was referring to 4 tips for designers. Koen left a comment pointing that it would be nice to have a version for developers. I agreed, so I contacted Luis Cortizo one of our best consultants, to have a conversation about the most frequently asked questions that came up during the first days of QlikView training. Here are some tips:1. Forget everything you knew about data modeling. Ok, you don't need to forget everything, but you better be open minded to learn a new way to see and work with your data. There are some SQL modeling rules, constraints and hierarchies that you won't need anymore, QlikView is a lot easier than that. And yes, you will need to learn how to work with the script but trust me, this is about common sense rather than strong data base skills.If you are looking at the best way to create your data model, best practices will guide you to Star or Snowflake models. But remember, always start by understanding the business needs, then you will know what tables makes sense to have in your app and how you can join them. By doing that your data model will often look like a star or snowflake.2. Practice with your personal data.QlikView is probably one of the most comprehensive tools in the data visualization market. It could be used for creating corporate apps with billions of rows of data and thousands of users or it could be used to visualize your personal music library or your file system.When starting to create an app, frequently the hardest task will be to understand the business needs, particularly if you are in a complex corporate environment or if you are dealing with incomplete requirements. If you are a QlikView novice, my advice is to practice by loading some personal data and by trying to create something with meaning for you, that way you could practice with data modeling and visualization and you will learn how both are related. For example, I just moved to the US from Spain so lastly I use Skype to talk with family. Understand that talking on the telephone is not one of my favorite things to do so, if during the day I have to make several calls, I try to keep the call duration as short as possible. Intuitively I think I should see an inverse correlation in my call history, in other words, the more calls per day the shorter average duration will be.To prove my intuition, I just downloaded several CSV files from the Skype website which contained call activity historical data for the last 8 months. Once the data was loaded into the QlikView app, I used a scatter chart to see if I could prove my theory.Because real data is (sometimes) ugly I had to transform the call duration from HH:MM:SS to seconds to normalize it and typically I want to work with dates in a human readable format rather than computer timestamps.The fact that I had my goal well defined - to show if there´s a correlation or not - helped me to identify what I had to have as an output from my model. In this example I needed to count the number of calls per day and this business requirement forced me to create a new field named "Calls". Later on using an expression like sum(Calls) I could obtain the number of calls per day (or any other dimension).Finally and after a few transformations I was able to produce the chart I was looking for:Looking at the chart, I can see that there is a non-strong (how close the points are to a straight line) inverse correlation between X and Y axis. Note: remember that correlation does not imply causation. Unfortunately, the data did not support my hypothesis. I did, however, learn how to show correlation and next time I will need to show how two variables are related I will always remember what I did with my personal data. 3. Start simple.The QlikView approach to app development is based on quick iterations, small steps that put all together will let you build amazing apps. To start working in a new data model, you better start by loading a couple of tables and then create some list boxes with the fields you has just loaded. This will help you to experiment with QlikView Associative experience.Green, Gray and White color code will provide you with a great feedback. If everything is working fine, then is time to start with the second phase of your development including a new table or data source, remember that a QlikView app can contain data coming from multiple sources, inside and outside your organization.The more time you invest in a good data model the less you will spend on design, that´s the golden rule. And again having well-defined business goals will help you to collect, normalize or denormalize data to specifically respond to that business needs. This for sure will simplify the development process.4. Search, copy, reuse… and share.QlikView functions set is one of the most powerful data transformation sets in the market, it's important to get familiarized with it, every time you get stacked on how to achieve a complex calculation, use the search function in QlikView Help (F1).It’s also crucial to get involved in the QlikView Community - one of the most vibrant websites to discuss about data modelling and visualization - by searching, reading, asking and answering questions, but also by sharing… what makes unique our Community is the ability to share apps and get feedback from other business users.Enjoy Qliking!AMZ ...View More