I have received a few emails from QlikView developers asking what the best practice is for placing list boxes. There are two arguments that you may think of right away. In information design, left-side, top-left to be precise, is always used for the most important information. It is because as a human-nature that’s the space people pay attention to first. So why should I waste the space for placing the navigation pane there? I should place it on the right.Another argument is that people are used to use left navigation because most of the web sites have menus on the left. Think about a shopping web site. All departments, categories, genders, sizes… whatever you think of usually on the left hand side. So why should I go against human’s habit and place it on the right-side? People will get confused. I can buy both arguments. But then how about placing it at the top? I have seen QlikView applications that have navigation pane at the top. Is this the best of all? Let’s think about this in QlikView usability, with an elimination method. Right-paneWe create a demo application for 1024px width so that it will fit nicely with any devices you may have including a projector. But some users may have a wider screen or higher resolution screen. If for some reason if I want to expand the line chart at the bottom to see more in detail or utilizing the space of my screen, I will need to overlap the navigation section with my chart by extending the width. Then, when I want to make a selection in Expense Category, I need to either move the chart or resize it to do so. If the navigation pane was on the left hand side, you could resize the width with no problem, and you still could make selections in Expense Category list box. So the right-pane is out. Top-paneI have seen applications with list boxes at the top. This may be a good idea because then the ‘body’ part can be used only for information display. Is this the best of the all world? Let’s think about it for a second. We all know that you will get a request of adding more list boxes on the screen. Eventually you will run out of the room towards the right, and you will consider adding a second row of list boxes. Then will you shift everything down? You are losing the important real-estate to display information by the navigation pane. So that doesn’t work well. That being said, I personally think that having the left-side pane works the best in QlikView applications, and here are the benefits. Left-panePeople are already used to look at the left side to navigate a web site. So why not also for QlikView? Also remember that QlikView is also a web page.QlikView tabs as well as clear button navigation bar are at the top starting from the left. So it is the best to keep the navigation elements on the left hand side so that when a user looks at the top left corner, all navigation related items can be seen easily. It’s all about where your eyes start on the screen.If a user wants to expand the width of an object on a wide screen, there is no disturbance with other objects. The area to display the information is consistent. Even if I add more list boxes, I do so within the left pane. So there is no need to shift the information display area.Lastly, you may wonder why I have the timeline list box at the top. This is my 11-year QlikView habit. I believe it is the best to keep the timeline list boxes separated from other selection categories. If you cannot give up the top-pane option or wish to have many list boxes on user’s figure tip, then you can use a trick. Here is an example. When you click on the ‘filter’ button, then there is a drop panel with list boxes. I recommend you using this in dashboard where you need lots of real-estate for important information. Or also you can use this together with the left-pane navigation. In this case, create list boxes for the most frequently used fields on the left for easy navigation (accessibility), and you can create the hidden panel for additional list boxes. Now it is up to you what method you will use in your QlikView application. Will you go with a top, left, right or hidden pane?You can also download the technical paper on this topic here. ...View More

"Good Data"Cultivating culture that emphasizes consistency and reusability is vital when introducing successful data governance practices. Common problems with many decision support systems are the amount of variation, redundancy and overlap that exists within the data models and business logic used across multiple analytical applications. These problems can delay critical decisions and disrupt IT operations while users struggle to verify the truth in data. Having data is one thing, having “good data” is another. With the volume of data increasing it is important to create a structured and consolidated data management layer that contains reusable and consistent definitions. This in turn gives developers and business users assurance that the data they are using, whether to develop applications or make decisions, is “good data”. It also expedites the process of creating new applications and eliminates much of the guesswork in maintaining applications as business requirements evolve over time.Storing and Reusing QlikView ExpressionsUsing QlikView Expressor (QVE) to manage and prepare data for QlikView is a great step towards adding data governance and data management to your QlikView deployment. Not only can you visualize where data originates and its final destination, but you can also create reusable parameterized business rules that can be shared across multiple applications.By design QVE uses a Transform Operator to store Expression and Function rules to manipulate and add data. When transforming data - a simple QVE expression is used. The results yield transformed and/or new data columns to be used in the final output of the QlikView table model. But what if you want to store and reuse an actual QlikView specific scripted expression and not just the resulting column output? This would be an ideal method to reference a single version of that expression in a unified manner. In turn it could reduce maintenance significantly if changes are made since there is only one place to make modifications, QlikView Expressor. This approach would also increase productivity and data confidence as it creates a single common expression stored in a centralized reusable repository.Want to learn more and see it in action? Download the complete document and sample below.If you are viewing this from http://community.qlik.com/blogs - you must click this link to get the sample:(http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/24/defining-qlikview-expressions-with-qlikview-expressor)The document is also available here: http://community.qlik.com/docs/DOC-4214Regards,Michael Tarallo - Senior Product Marketing ManagerBill Kehoe - Product Manager and founder Expressor ...View More

With QlikView 4 and QlikView 5 we reached a much larger audience than before. We now had large enterprise customers that had demands on the product that we didn’t quite satisfactory fulfill: The demands were around Security, Distribution and Workflow. As a result, one large Swedish customer developed their own system to administrate QlikView: They developed software that used the QlikView COM Automation interface to update and distribute QlikView documents. They showed it to us and we were impressed – so impressed that we bought the code to develop it further. As a result, we could in 2001 release the QlikView Administrator. The Administrator had three basic components: The Factory – which later became The Distribution service The User Access Portal – which later became The Access Point The Administration Panel – which later became The Management Console The Factory’s tasks were to update the QlikView documents and distribute them in a secure way. On the portal, the users could either download the documents for off-line use or connect to the documents using QlikWeb – which was the name of our server at the time. Finally, the administration panel was used to set the rules for how and when the updates should be made and to whom the documents should be distributed. The Administrator was the basic workflow tool that our enterprise customers demanded. It contained tasks, scheduling, data reduction, document categorization, document distribution and it also set the user rights per document. So it covered all the basic needs. The administration panel for Administrator version 1 The name was not quite good, so we renamed it “QlikView Publisher”. We also improved the UI and the functionality and when we released version 2 a few years later, it was much richer in features and much more usable. The administration panel for Publisher version 2 Initially, it was developed in Visual basic 6, but we soon were looking for a more modern development tool and today it is developed in C#. The version numbers were not in sync with QlikView until QlikView 8. Before that, the Publisher had its own numbering. For QlikView 9 the QlikView Server and the QlikView Publisher were completely merged, with a common management console and a common installation. This also means that some of the original Publisher features became available also without a Publisher license, e.g. the reload of a document. Although very much has changed since the first Publisher version, the basic concepts for the Publisher remain the same: Security, Distribution and Workflow. Today, the Publisher is a mature workflow tool that allows our customers to manage the distribution of information both to off-line and on-line users. It can connect to a large number of directory services; it can be integrated with almost any authentication system and it can use either Windows integrated authorization or the QlikView internal authorization. It can take a master document, refresh it, reduce it so that the user only gets its own data and finally distribute it in any way the administrator wants it. It is an absolute necessity for a company with enterprise demands on security and data governance. HIC ...View More

In a well visited post on the community forum, John Witherspoon some time ago asked “Should We Stop Worrying and Love the Synthetic Key?” John’s post begins: “Synthetic keys have a bad reputation. The consensus seems to be that they cause performance and memory problems, and should usually or even always be removed. I believe that the consensus is wrong.” Here’s my view on this topic.The creation of synthetic keys is simply QlikView’s way of managing composite keys. There is nothing strange or magic around it.A single key is easy to manage: Just list all unique values in a symbol table (see Symbol Tables and Bit-Stuffed Pointers), and then link the data tables using a natural join.But a composite key is slightly different – there is no single symbol table that contains the relevant combinations of the multiple key fields. So QlikView needs to create such a table for all combinations: the $Syn table. In a way, you can say that the $Syn table is a symbol table for composite keys. In the data tables, the multiple keys are replaced by an identifier that uniquely identifies the combination of the values of the original keys: the $Syn key.Hence, if you have the same set of multiple keys in two or more tables, the QlikView synthetic keys create a general, correct, compact and efficient solution. Synthetic keys do not per se cause performance and memory problems. They do not use a lot more memory than if you autonumber your own concatenated key. And they treat NULLs correctly, as opposed to an explicit concatenated key.Hence: The synthetic key is in itself good and we should all love it.However… still, also I avoid synthetic keys. Why?A synthetic key is in my experience often a sign of a poorly designed data model. I say that, given the number of times I have found a synthetic key in the table viewer only to realize that I made a mistake in the script. If you get a synthetic key and didn’t expect it, I can only say: Back to the drawing board! You should most likely change your data model.QlikView creates an additional table (the $Syn table) that in many cases is superfluous: An additional table is the best solution if none of the data tables by itself completely spans the set of composite keys. But in real life, there is usually one table that contains all relevant combinations of the keys, and then this table can be used to store the clear text of the individual keys.For clarity, I like to create my own concatenated keys. It forces me to think and create a data model that I believe in. Removing the synthetic keys becomes a method to ensure a good data model, rather than a goal in itself.But in principle, I totally agree with John’s initial conclusion: Any problem around synthetic keys is really a data modeling problem and not a problem with the synthetic key itself.The short answer to John’s question is Yes and No. Yes, we should love the synthetic key. But, No, we should not stop worrying. We should always be alert and ask ourselves: “Do I want this synthetic key? Is the data model OK?”And so, because of the automated and irrevocable data-modeling process which rules out human meddling, the Synthetic Keys are scaring. But they are simple to understand. And completely credible and convincing.Dr HICFurther reading on Qlik data modelling:Circular ReferencesFan traps and Chasm traps ...View More

Knowing when to show more information and when to show less is not only good design it's good usability. The more information you present the greater the cognitive load a user has to juggle to accomplish a task. Sometimes you want to have lots of information visible to make the best choice but other times you don't. There is a point of diminishing returns where you have given your users too much information too soon. Sometimes people need to ease into an application, get acquainted with it, and then proceed to learn more.Progressive disclosure is when information is sequenced out across several pages or screens to help a user process information and to avoid overwhelming them with too much information. Additional information, or more advanced or rarely used features, are hidden away until needed. This is a technique that was used by IBM in the 1980's when developing user interfaces. They realized that progressively disclosing additional tasks and information through a series of menus was better than having everything present up front. It managed complexity by clearing up clutter. It helped new users get familiar with the UI and as they became more savvy users they used the menu systems to find more advanced functionality.The attached Technical Paper discusses a bit more how progressive disclosure is useful in BI. Progressive disclosure helps the DAR methodology to give users the general summary on the Dashboard, more advanced functionality on the following pages, and then the real deep dive information for Reporting. Progressive disclosure helps you design for other people at a variety of skill levels to get the most out of your applications. ...View More

In all programming environments there is a need for quotation marks, and QlikView is no exception. But which symbol should you use? " ", [ ], ` ` or ' ' ? This post will try to explain the differences between the different quotation marks.

...View More

As many QlikView developers have grown accustomed to, QlikView offers developers more than one way to accomplish a task. Knowing when to use each function is half of the battle. For example let’s take a look at Peek() vs Previous(). There are certainly some similarities between the two functions but there are also distinct differences that need to be taken into account when deciding which function to use.The SimilaritiesBoth allow you to look back at previously loaded rows in a table.Both can be manipulated to look at not only the last row loaded but also previously loaded rows.The DifferencesPrevious() operates on the Input to the Load statement, whereas Peek() operates on the Output of the Load statement. (Same as the difference between RecNo() and RowNo().) This means that the two functions will behave differently if you have a Where-clause.The Peek() function can easily reference any previously loaded row in the table using the row number in the function e.g. Peek(‘Employee Count’, 0) loads the first row. Using the minus sign references from the last row up. e.g. Peek(‘Employee Count’, -1) loads the last row. If no row is specified, the last row (-1) is assumed. The Previous() function needs to be nested in order to reference any rows other than the previous row e.g. Previous(Previous(Hires)) looks at the second to last row loaded before the current row.So, when is it best to use each function? The previous() and peek() functions could be used when a user needs to show the current value versus the previous value of a field that was loaded from the original file. The peek() function would be better suited when the user is targeting either a field that has not been previously loaded into the table or if the user needs to target a specific row. I wrote a technical brief that shows you how and why to use the Peek() and Previous() functions. You can see it here. Happy Qliking! ...View More

A common problem in business intelligence is when you want to link a number to a range. It could be that you have a date in one table and an interval – a “From” date and a “To” date – in another table, and you want to link the two tables. In SQL, you would probably join them using a BETWEEN clause in the comparison. But how do you solve this in QlikView, where you should avoid joins? The answer is to use IntervalMatch. IntervalMatch is a prefix that can be put in front of either a Load or a SELECT statement. The Load/SELECT statement needs to contain two fields only: the “From” and the “To” fields defining the intervals. The IntervalMatch will generate all the combinations between the loaded intervals and a previously loaded numeric field. Typically, you would first load the table with the individual numbers (The Events), then the table with the Intervals, and finally an intervalmatch that creates a third table that bridges the two first tables. Events:Load * From Events; Intervals:Load * From Intervals; IntervalMatch:IntervalMatch (Date)Load distinct FromDate, ToDate resident Intervals; The resulting data model contains three tables: The Events table that contains exactly one record per event. The Intervals table that contains exactly one record per interval. The IntervalMatch table that contains exactly one record per combination of event and interval, and that links the two previous tables. Note that this means that an event may belong to several intervals, if the intervals are overlapping. And an interval can of course have several events belonging to it. This data model is optimal, in the sense that it is normalized and compact. All QlikView calculations operating on these tables e.g. Count(EventID) will work and will be evaluated correctly. This means that it is not necessary to join the intervalmatch table onto one of the original tables. Joining it onto another table may even cause QlikView to calculate aggregations incorrectly, since the join can change the number of records in a table. Further, the data model contains a composite key (the FromDate and ToDate fields) which will manifest itself as a QlikView synthetic key. But have no fear. This synthetic key should be there; not only is it correct, but it is also optimal given the data model. You do not need to remove it. IntervalMatch can also be used with an additional key between the tables – i.e. when you have Slowly Changing Dimensions. But more about that in a later post. HIC For more on IntervalMatch and some script examples, see the technical brief IntervalMatch and Slowly Changing Dimensions. ...View More

QlikView is full of functions that can be used in expressions and/or the script to manipulate and parse the data. I decided to write about some common string functions that can be very helpful:Subfield()Len()Index()MinString()MaxString()Substringcount()Subfield()One of the most useful functions is the Subfield() function. From within the script, this function returns a specific substring from a larger delimited substring. This function allows you to transform a table like this:To a table like this parsing the Color field so that each product and color combination has its own row. This makes it easier to filter the data by color using list boxes.Len()The Len() function returns the length of a string in either an expression (Len(Name)) or in the script providing the number of characters in in string like this:Index()The Index() function returns the position of a substring within another string. This function may be overlooked but it can be very handy when parsing a string field like a phone number. Using the Index() function, I can determine where the ‘-‘ are in the phone number therefore capturing the parts correctly. Phone: NoConcatenate LOAD ID, Phone, Left(Phone, Index(Phone, '-')-1) as Phone1, Mid(Phone, Index(Phone, '-')+1, 3) as Phone2, Right(Phone, Len(Phone)-Index(Phone, '-', 2)) as Phone3 Resident PhoneTemp;MinString() and MaxString()Next are the MinString() and MaxString() functions. I think everyone has used these functions at least once in QlikView. They return the first or last value over a dimension (in an expression) or over a group by clause (in a script). I find these functions most helpful in chart expressions when I need to see the first or last value across a dimension.Substringcount()The last function is the Substringcount() function. I think this is a hidden treasure – not many people know about it but once you do, you use it all the time. The Substringcount() function returns the number of times a substring is in a string. So, if the field String is “abcdefabcdef” then the expression Substringcount(String, ‘def’) will return 2. I find this helpful with a show condition when I want to determine is a value has been selected or is possible.There are so many string functions in QlikView that can make your life easier. Browse through them the next time you are in the Help section to see what shortcuts you can use to manipulate your data.I wrote a technical brief about these string functions with more detail. ...View More

To make a chart in QlikView – or in any Business Intelligence tool, for that matter – you need to know what Dimensions and Measures are. But not all people have a clear picture of the difference between the two. So this week’s post will try to straighten out what’s what.

...View More