I have in several previous blog posts written about the importance to interpret dates and numbers correctly e.g. in Why don’t my dates work?. These posts have emphasized the use of interpretation functions in the script, e.g. Date#(). But most of the time, you don’t need any interpretation functions, since there is an automatic interpretation that kicks in before that. So, how does that work? In most cases when QlikView encounters a string, it tries to interpret the string as a number. It happens in the script when field values are loaded; it happens when strings are used in where-clauses, or in formulae in GUI objects, or as function parameters. This is a good thing – QlikView would otherwise not be able to interpret dates or decimal numbers in these situations. QlikView needs an interpretation algorithm since it can mix data from different sources, some typed, some not. For example, when you load a date from a text file, it is always a string: there are no data types in text files – it is all text. But when you want to link this field to date from a database, which usually is a typed field, you would run into problems unless you have a good interpretation algorithm. For loaded fields, QlikView uses the automatic interpretation when appropriate (See table: In a text file, all fields are text - also the ones with dates and timestamps.) QlikView does not use any automatic interpretation for QVD or QVX files, since the interpretation already is done. It was done when these files were created. The logic for the interpretation is straightforward: QlikView compares the encountered string with the information defined in the environment variables for numbers and dates in the beginning of the script. In addition, QlikView will also test for a number with decimal point and for a date with the ISO date format. If a match is found, the field value is stored in a dual format (see Data Types in QlikView) using the string as format. If no match is found, the field value is stored as text. An example: A where-clause in the script: Where Date > '2013-01-01' will make a correct comparison The field Date is a dual that is compared to a string. QlikView automatically interprets the string on the right hand side and makes a correct numeric date comparison. QlikView does not (at this stage) interpret the content of the field on the left hand side of the comparison. The interpretation should already have been done. A second example: The IsNum() function IsNum('2013-01-01') will evaluate as True IsNum('2013-01-32') will evaluate as False since the 32:nd doesn't exist In both cases, strings are used as parameters. The first will be considered a number, since it can be interpreted as a date, but the second will not. A third example: String concatenation Month(Year & '-' & Month & '-' & Day) will recognize correct dates and return the dual month value. Here the fields Year, Month and Day are concatenated with delimiters to form a valid date format. Since the Month() function expects a number (a date), the automatic number interpretation kicks in before the Month() function is evaluated, and the date is recognized. A final example: The Dual() function Dual('Googol - A large number', '1E100') will evaluate to a very large number Here the second parameter of Dual() is a string, but QlikView expects a number. Hence: automatic interpretation. Here, you can see that scientific notation is automatically interpreted. This sometimes causes problems, since strings – that really are strings – in some cases get interpreted as numbers. In such cases you need to wrap the field in a text function. With this, I hope that the QlikView number handling is a little clearer. HIC Further reading related to this topic: Data Types in QlikView Get the Dates Right Why don’t my dates work? ...View More

In a presentation, or a seminar or just a conversation, the speaker starts presenting the topic by introducing the title of the topic, then gives a brief overview, the background and then gently flows into talking about the details of the topic and concludes with the summary or an inference. Similarly, when writing an article, one first starts with an abstract, an introduction, then writes the details of the topic in the body and ends by writing a summary or a conclusion. Order and organization of information from a reference point to the last detail, in sequence, is the most crucial part of effective and seamless communication and storytelling. Design, like writing, or a verbal presentation, or even a casual conversation is a medium of communication and a channel for storytelling. It follows the same principles of hierarchy and order as in any method of communication. Only when information is organized in a good hierarchical manner, the story is told most effectively. Most people are visual thinkers, chances of people understanding a set of images and text put together in a grid with no starting or an end point are very slim, instead, going through and comprehending information which is ordered in a top to bottom approach is much more meaningful. For instance, the design of a newspaper is a classic example of using hierarchy in the most effective way to help people read highlights and pick information they want to read. Hierarchical patterns in design can not only aid comprehension but also enable quick scanning of information, guide the user through the story and improve usability. As time becomes an increasingly valuable commodity, grabbing the user’s attention and retaining it has become the most important and the most challenging thing today. Designing information in a way that calls for the user’s attention and retains it is the key to a successful communication strategy and Hierarchy forms one of the most important ingredients for effective communication.In a QlikView application the D-A-R concept (Dashboard, analysis, report) is a great method to provide contextual hierarchy where the content is presented in a top down approach. This makes it easier for the user to grasp the data from start to finish and also enables them to pick out information that they intends to drill down to and analyze. However basic and overemphasized these principles may seem, the fact is that they always seem to work and give rise to good user experience. Research and usability tests prove that when a design layout adheres to the basic principles of design, the design becomes more user-friendly, simple and obvious. Hierarchy is one of the most important principles of basic design and should be applied to all designs from simple to complex. A technical brief can be found here which expands more on this topic. ...View More

Have you ever been asked to create a table that has several independent calculations over different metrics? Mixing aggregation formulas and counts in the same table? Did you end up creating different tables for every view point on the same information? Or did you create a table like the one below?If not, let me introduce you to ValueList() and its number oriented big brother ValueLoop().ValueListValueList (value {, value })ValueList allows us to specify a set of arbitrary values within the function, when used as a calculated dimension in a chart this will act as a synthetic dimension.We can later restate the same function, with the same parameters, in our expression to reference the corresponding value in our newly created synthetic dimension.And it is as simple as creating a straight table with following dimension and expressionCalculated Dimension: =ValueList('My First KPI','My Second KPI')Expression: =IF( ValueList('My First KPI','My Second KPI')='My First KPI', Sum([My First KPI Field], Count([My Second KPI Field]))And voila we have created a table/chart with a dimension that does not exist in our data model and with an expression that has the possibility to mix and match aggregation functions over each dimension.Matthew Crowther has also created an excellent Explosion Chart that also leverages ValueList, you can read more on his blogValueLoopValueLoop(from [, to [, step = 1 ]])ValueLoop shares the same characteristics as it’s little brother ValueList with the exception that it will create a series of numbers as the synthetic dimension.To create a dimension with values that spans between 1-100 we would create a calculated dimension with=ValueLoop(1,100,1) which we can reference from our expression with the expressionIF( ValueLoop(1,100,1)=3, 'Almost Pi', 'Not Pi')ValueLoop also allows us to create the, not so useful but fun to make, square pie chart which you can read more on in this technical brief. ...View More

There are two Swedish car brands, Volvo and SAAB. Or, at least, there used to be... SAAB was made in Trollhättan and Volvo was – and still is – made in Gothenburg.Two fictive friends – Albert and Herbert – live in Trollhättan and Gothenburg, respectively. Albert drives a Volvo and Herbert drives a SAAB.If the above information is stored in a tabular form, you get the following three tables:Logically, these tables form a circular reference: The first two tables are linked through City; the next two through Person; the last and the first through Car.Further, the data forms an anomaly: Volvo implies Gothenburg; Gothenburg implies Herbert; and Herbert implies SAAB. Hence, Volvo implies SAAB – which doesn’t make sense. This means that you have ambiguous results from the logical inference - different results depending on whether you evaluate clockwise or counterclockwise.If you load these tables into QlikView, the circular reference will be identified and you will get the following data model:To avoid ambiguous results, QlikView marks one of the tables as “loosely coupled”, which means that the logical inference cannot propagate through this table. In the document properties you can decide which table to use as the loosely coupled table. You will get different results from the logical inference depending on which you choose.So what did I do wrong? Why did I get a circular reference?It is not always obvious why they occur, but when I encounter circular references I always look for fields that are used in several different roles at the same time. One obvious example is if you have a table listing external organizations and this table is used in several roles: as Customers, as Suppliers and as Shippers. If you load the table only once and link to all three foreign keys, you will most likely get a circular reference. You need to break the circular reference and the solution is of course to load the table several times, once for each role.In the above data model you have a similar case. You can think of Car as “Car produced in the city” or “Car that our friend drives”. And you can think of City as “City where car is produced” or “City where our friend lives”. Again, you should break the circular reference by loading a table twice. One possible solution is the following:In real life circular references are not as obvious as this one. I once encountered a data model with many tables where I at first could not figure out what to do, but after some analyzing, the problem boiled down to the interaction between three fields: Customers, Machines and Devices. A customer had bought one or several machines; a device could be connected to some of the machine types – but not to all; and a customer had bought some devices. Hence, the device field could have two roles: Devices that the customer actually had bought; and devices that would fit the machine that the customer had bought, i.e. devices that the customer potentially could buy. Two roles. The solution was to load the device table twice using different names.Bottom line: Avoid circular references. But you probably already knew that…HICFurther reading on Qlik data modelling:To Join or not to JoinSynthetic KeysFan traps and Chasm traps ...View More

We sometimes suffer from trying to show as much information as possible in QlikView. In order for us to categorize the information so the users can consume it more easily and smoothly, the first option you may consider is using tabs. Tabs are a great way to categorize the information for users; however, if you abuse the tab system in QlikView, users may get confused or miss some important information that is available for them. This is why.• When there are too many tabs, then QlikView wraps the tabs and creates multiple rows of tabs in QlikView Desktop. • The point above is a different usability in AJAX client when many tabs exist. QlikView creates buttons to navigate the rest of the tabs that are hidden, just like MS Excel. As you can imagine, this can be a risk of users missing some information. Have you seen an application like this? Well, I have. Yes, in a real life use case. In this extreme case, rows of tabs can be as many as the example above. This is an example with 1024x768 screen resolution. As you can see, we are losing the real estate for information display for the tabs. It is about 1/3 of the entire real state for tabs. In order to avoid this tab nightmare, you have a few options to overcome this situation. First, think about the hierarchy of your information categories. Then, consider using 1) a container object, 2) sub-tabular system, 3) a multi-box or 4) combination of these options. This is one example of using the Option2: sub tabular-system. If you are curious to know more about this topic, you can see the tech brief here. More example snapshots and how each option works are documented in detail. I hope you have a better understanding on how to deal with many tabs in QlikView. ...View More

The table viewer is a gem.I use it for many things: to get an overview; to debug what I have done in the script; to check that all tables are linked; to check that I don’t have any unwanted synthetic keys; to preview data. I can hardly remember what I did before QlikView had it¹.Just hit <ctrl>-T and you will see it.I move around the tables until the structure corresponds to the picture I have in my head of the data. I tell new users developing applications to draw the wanted data model on a piece of paper so they know what the goal is. If they can't draw it on a piece of paper, they need to sit down and think. Or play around with data in QlikView until they can draw the data model. The structure seen in the table viewer then becomes an acknowledgement that the data model is correctly implemented. Or a warning that it isn't.There are two modes of the table viewer: The Internal table view and the Source table view. The only difference is how the synthetic keys are displayed. During the script development, I always use the source table view, since it shows me exactly what I have done in the script.If you hover above the table headers, you can see the number of records and number of fields. If you hover above an individual field, you will get the data for this specific field: Whether it is a key, the information density, the subset ratio and – if applicable – the tags.Many, when they see the table viewer, start thinking about a graphical tool to define which source data to load. But this is not what the table viewer is – it is a viewer only. Unfortunately or luckily, whichever way you look at it…“Unfortunately” since a graphical tool no doubt would help many people, especially in the initial phase, when they want to load data quickly just to get a look at it. “Luckily”, since you never will get the same versatility using a graphical tool as you will with a script editor. For instance, almost all advanced data transformations I have shown in previous blog posts (e.g. How to populate a sparsely populated field) would be almost impossible to describe with a graphical tool. They would at least be very complex to depict, and most likely less user-friendly than having to write the actual code.So, if we get a graphical script generator some day in the future, it should be some kind of hybrid where scripting still is allowed and we have the best of both worlds.Until then, I will happily use the table viewer as it is: As the best available tool to get an overview of the data model. And as one of the best debugging tools in the application development process.And that is not bad.HIC¹In fact, I do remember what we did before the table viewer existed: We used a cross table (a pivot table) with the system fields $Field and $Table as dimensions and Only([$Field]) as expression. It worked, but it did not give the same overview. ...View More

More and more, blog posts, news articles, and various other online media are incorporating data visualization. A well placed chart or graph can drive home the point of the story being told. While print has been doing this for a long time, unique to the web experience is the arrival of interactive visualizations which allow users to explore and experience the data in a new way.Not to be left out, there are several options for easily embedding QlikView into websites. While integrating QlikView into the web can be tricky (cross domain scripting, security concerns, etc.) there are some terrific posts on the web by Stefan Walther and Alexander Karlsson (amongst others) describing various ways a person can add some slight code to embed objects into a web page. Using these same principles, I’ve also developed a tool which should make things as easy as possible for web novices to get QlikView visualizations into their site. Once this tool is implemented, a blog writer who is desperate for some visual aids on their postcould go to their QlikView appuse the tool in an app to add QliKView objects to a staging area where they arrange them how they wantclick the “export” button to get the iframe codepaste the iframe code into the htmland check out the new visualization on your siteThis is very simple for the blogger, and by using iFrames, we are able to avoid cross domain scripting issues and also allow the objects within the frame to communicate with each other, making them interactive.This solution is an example of how several different types of customizations can be used to create a helpful tool. Click here to download the solution and documentation. To see embedding in action, visit the Image Gallery on the demo site. The “Pinterest-style” image gallery is embedded into the site using these same techniques. ...View More

A common question in the QlikCommunity forum is how to show only the last N months. The suggested answers are most of the time relatively complex set analysis expressions including dollar expansions with aggregation functions, e.g. Sum( {$<Date={">=$(=MonthStart(AddMonths(Max(Date),-12)))<$(=MonthEnd(Max(Date)))"}>} Sales) Such an expression may work fine. However, it is not simple. When seen by someone who didn't write it, it is almost incomprehensible. So instead of such an expression, I would like to suggest a slightly different method: Relative calendar fields.The idea is to define relative calendar fields in the master calendar. By doing this, it will be a lot simpler to define chart expressions. For example, you can in your master calendar define fields calculating how many days or months ago a specific date was: Today() - Date as DaysAgo, 12*(Year(Today())-Year(Date)) + Month(Today()) - Month(Date) as MonthsAgo,Then you will be able to have much simpler chart expressions, e.g: Sum({$<MonthsAgo={">=0<12"}>} Sales) This expression does almost the same as the initial expression. But it is much easier to read and understand. Below you have a chart using this expression showing the last 12 months. (Financial year that starts in April; today is a day in June and the dimensions are rolling month [Date(MonthStart(Date),'MMM')] and fiscal year.)Further, you can also create fields for relative months within the financial year; fields that you can use for a year-on-year comparison: Today() - YearStart(Today(),0,$(vFM)) - Date + YearStart(Date,0,$(vFM)) as DaysAgoYTD, Mod(Month(Today())-$(vFM),12) - Mod(Month(Date)-$(vFM),12) as MonthsAgoYTD,The variable vFM is the first month of the financial year, see more on Fiscal Year.If you have these fields defined, you can easily make a year-to-date chart comparing the different years. The expression will be the same as before, but with MonthsAgo changed to MonthsAgoYTD: Sum({$<MonthsAgoYTD={">=0"}>} Sales) Below you have a chart of a year-over-year comparison (using the built-in accumulation):Bottom line: By defining relative dates and months in your master calendar, you can significantly simplify your set analysis expressions. Also, such fields will help your users create new charts. And your charts may even evaluate faster...Simplicity.HIC ...View More

Usability engineers & researchers are crucial parts of User Experience. While not as "glamorous" as designers they bring evidence to the world of design. Through observation of heuristic tests they offer empirical evidence that a design is working, failing, what users like, what users aren't finding, what users are doing that they don't even realize they are doing it, etc. Usability is the closest thing design has to being a science. The attached technical paper goes through a variety of topics with usability in mind and makes recommendations. It links out to studies and research already done supporting best practices. The basic findings and recommendations in this document are that:• People don’t read everything online, they skim• Paragraph width impacts comprehension• Scrolling is good• Monitor Resolution: design for 1024x768• Ipads: design for 1024x768 and allow scrolling• Icons don’t necessarily help usability• Filters should be on the left ...View More

As one creates QlikView applications one sometimes encounters a data modeling problem where a dimensional attribute varies over time. It could be that a salesperson changes department or a product is reclassified to belong to another class of products. This problem is called Slowly Changing Dimensions and is a challenge for any Business Intelligence tool. Creating an application with static dimensions is simple enough, but when a salesperson is transferred from one department to another, you will have to ask yourself how you want this change to be reflected in your application. Should you use the current department for all transactions? Or should you try to attribute each transaction to the proper department? First of all, a changed attribute must be recorded in a way that the historical information is preserved. If the old value is overwritten by the new attribute value, there is of course nothing QlikView can do to save the situation: In such a case, the new attribute value will be used also for the old transactions and sales numbers will in some cases be attributed to the wrong department. However, if the changes have been recorded in a way so that historical data persists, then QlikView can show the changes very well. Normally, historical data are stored by adding a new record in the database for each new situation, with a change date that defines the beginning of the validity period. In the salesperson example, you may in such a case have four tables that need to be linked correctly: A transaction table, a dynamic salesperson dimension with the intervals and the corresponding departments, a static salesperson dimension and a department dimension. To link these tables, you need to match the transaction date against the intervals defined in the dynamic salesperson dimension. This is an intervalmatch. The solution is to create a bridge table between the transaction table and the dimension tables. And it should be the only link between them. This means that the link from the transaction table to the bridge table should be a composite key consisting of the salesperson ID (in the picture called SPID) and the transaction date. It also means that the next link, the one from the bridge table to the dimension tables, should be a key that points to a specific salesperson interval, e.g. a composite key consisting of the salesperson ID and the beginning and end of the interval. Finally, the salesperson ID should only exist in the dimension tables and must hence be removed from the transaction table. In most cases of slowly changing dimensions, a salesperson (or product, customer, etc.) can only belong to one department (or product group, region, etc.) at a time. In other words, the relationship between salesperson and interval is a many-to-one relationship. If so, you can store the interval key directly in the transaction table to simplify the data model, e.g. by joining the bridge table onto the transaction table. A word of caution: If a salesperson belongs to several departments at the same time, such a join may cause QlikView to make incorrect calculations. Bottom line: Double-check before you join. For a more elaborate description of Slowly Changing Dimensions and some script examples, see the technical brief IntervalMatch and Slowly Changing Dimensions. HIC ...View More