This is part one in a series of blog posts where I will share some of the resources that I use on a daily basis. Today I am going to share some of my bookmarks, specifically blogs. These are some of the most interesting blogs discussing QlikView today. QlikView notesQlikblog.atQV DesignQlikView notes is one of the first QlikView oriented blogs. It’s made by Rob Wunderlich and it is a great source for developers to find performance tips and best practices.Stefan Walther is behind this amazing blog. It's one of the best blogs to stay up to date with QlikView extensions, so if you are looking for something Qool you are in the right place. Beingpassionate about QlikView design, this is one of my favorite sources of inspiration. In Matthew Crowther own words: “So have a browse around and get inspired; it’s not plagiarism; it’s research.”QlikView TipsQlikView MavenQlikShareStephen Redmond is covering visualization, technical tips and business related opinions, all in one place. Tim Benoit : “a mix of tips for both beginners and advanced users with a focus on things you won't find in the manual”. Deepak Vadithala is doing an impressive job with QlikShare. Here you will find QlikView video tutorials, quizzes and tips. Well done Deepak!The Qlik Board!QlikView AddictmindspankBill Lay is running this very practical blog where he writes about personal experiences with QlikView and creative tips for standard objects.Matt Fryer, creator of QlikView Notepad ++, has a blog that contains explanations, examples, tips, tricks and musings from a QlikView addict.At Alex Karlsson´s blog you will find some new and exciting QlikView extensions. Great for map extensions and visualization related topics.QlikFixiQlikQuickIntelligenceQlikView blog: tips, tricks and tutorials. By Barry Harmsen, co-author of the book "QlikView 11 for Developers".One tip per month seems to be the rule. By Miguel García, co-author of the book "QlikView 11 for Developers".Steve Dark’s blog contains QlikView tips about development and also some screen casts on youtube.TIQViewLiving QlikViewOne Qlik at a TimeWith focus on data integration you will find articles around how to connect with not so ordinary data sources like HANA, Hadoop and so forth.Interesting blog around data visualization and tips and tricks. By Aaron CouronChris Johnson is categorizing his posts in three self explanatory categories: Basic, Intermediate and opinion.Bi Review...and points beyondMichael EllerbeckDmitry Gudkov's blog is about Business Intelligence and Data Visualization. Not just about QlikView but mainly.Jay Jakosky writes mostly about data, so if you are in this industry this is a nice read with good links.Michael writes about a bunch of different topics tech related. A very active blog; it seems he read and knows all about QlikosphereIf you know another QlikView blog and you think I should include it in the Qlikosphere, please leave a comment and I´ll do my best.Enjoy Qliking!Arturo Muñoz ...View More

A scatter chart or a bubble chart is easy to make in QlikView – if you know how to… There are however a couple of things that may be confusing when you make the chart.The first thing is the Dimension. Many think that this is identical to one of the axes of the chart, and for most chart types it is. But not for a scatter chart. Here, the logical dimension is not the same as the graphical.Instead, you should visualize your graph and ask yourself: “What should each dot or bubble represent?”In the graph above, each bubble represents a country. In your case, it could perhaps be one bubble per customer, supplier or product. This is your dimension.The next question is: “Where should the bubble be positioned?” In the graph above, the x-coordinate is per capita GDP and the y-coordinate is the life expectancy. You may want to use e.g. the total order value, gross margin, net cost or some other numbers. These are your measures. These are used for the axes in the graph.You need at least two expressions. Optionally, you can have a third expression that will be used for the size of the bubbles. In the chart above, the country population is used as third expression.When defining the measures, you encounter the next confusing thing: A country has only one GDP, but QlikView still wants you to use an aggregation function, e.g. Sum() or Avg(). The reason is that QlikView cannot “know” if your data has one or several records for each dimensional value. So, you need to use an aggregation function to tell QlikView what to do, should there be more than one record. If there really is only one record per dimensional value, then it doesn’t matter if you use Sum() or Avg(). Both will work fine.But if you have several records per dimensional value, then you need to stop and think. Do you want to sum the records? Or do you want the average?Once you know which dimension and expressions to use, it is straightforward:Create a chart and choose Scatter Chart. Click Next.Add your dimension. Click Next.Add the fields that you want to use for your two expressions in the “X” and “Y” controls.If you want a bubble chart, you need to add the third expression that determines the size of the bubbles.Check “Advanced mode” to the lower left. QlikView 11 unfortunately jumps to the “Sort” page here, so you need to click “Back” to verify that the right aggregation functions for your expressions are used. You may need to change the functions.Click Finish.Now you will have made a scatter chart. To make it more beautiful, you should also consider the following:Choose a style [Style – Look]Increase the Bubble size [Presentation – Max bubble Size]Change the scale of one or both axes. [Axes]Remove the “Force 0” option.Use a logarithmic scale.If you want to try to make a scatter chart using some good sample data, look at the document Creating a Scatter Chart.HIC ...View More

Have you ever noticed how information is constantly communicated to you via icons in the world of electronic media?Right from the first screen that appears after you start your computers, tablet or phone to checking your emails and then moving on to surfing the web, you are constantly being informed by these little visuals which help you navigate your way through information and effectively alert you of various situations.Icons are a great way of communicating information quickly and precisely.If we look at some of the top usability rated websites and applications, we will find that they rely very heavily on the use of icons to facilitate excellent user experience. For instance, Dropbox uses icons for almost every function which not only makes it quick to grasp but also makes it visually appealing.In our case as QlikView application designers, we need to consider the use of icons as a way to draw the users’ attention, to alert them of a situation or to alarm them of an impending. Also icons can be used very effectively to display priorities for KPIs which can be used in combination with the traffic light chart (Red, yellow, green). The other part to the story is that the use of icons has become very popular in the industry today, especially the web world, so people tend to associate certain actions to certain icons. In our case, while designing application, it becomes important to consider the fact that the users’ psychological paradigm works in alignment to the surfing the web when trying to work with an application. They try to find similarities between using a website and using an application. So, the use of icons becomes all the more relevant in designing applications for us.So, what are some of the reasons that can justify why icons can be effective in designs? There are a few:-•Effective visual communication•Easy identification of information•Draw users’ attention and alert them of a given situation – this is especially important in dashboards•To provide a visual relief and enhance visual display of information•To save screen real estate by replacing text with iconsAlthough icons can be a great way of conveying an idea, their use, in design can be slightly tricky. There are a few dos and don’ts that one needs to keep in mind when incorporating icons in design. That said, Icons can enhance the look and feel of a design and at the same time can enhance the readability of content and thus the usability of an application. A detailed description with examples of best practices of using icons can be found in the technical brief here. ...View More

Last week Adam wrote a post about the Aggr() function and a Technical Brief about how to use it (Explaining the Aggr function). If you haven’t read it, I strongly recommend that you do. It is an excellent paper describing a function that can be used for nested aggregations - complex calculations where you need to make a second aggregation based on the result of a first aggregation.However, I have noticed in the discussion forum that Aggr() often is used when it isn’t necessary. And since you get a performance penalty when you use the function, today’s post is about discouraging you from using it when you don't need to.So, when should you not use it?1. Standard AggregationsStandard, non-nested aggregations, e.g. “=Sum(Sales)” or “=Count(distinct OrderID)” can be used almost anywhere in QlikView, in charts, in text boxes and as labels in any object. They will work directly as they are. Here you do not need any Aggr() function.If you want to calculate the aggregation several times, e.g. once per customer, just use the aggregation function in a chart and use Customer as dimension. Nothing else.This seems obvious, but I have on multiple occasions seen developers use the Aggr() function in such situations. Totally unnecessary - and it will just increase response times.Bottom line: You should not use Aggr() for standard non-nested aggregations.2. Calculation of a subtotal within the same chartIn some cases you need to use a subtotal from the same chart in your calculation. Then you should use the total qualifier inside your Sum() function (or other aggregation function). It will perform the given calculation disregarding one or several dimensions of the chart. It will however respect the selection.For instance, the sales in percent can be calculated using Sum( Sales ) / Sum( total Sales )This expression will return the sum of sales for each possible dimensional value, as a fraction of all possible sales. In other words; "Sum( total Sales )" disregards all dimensions of the chart.Bottom line: You should not use Aggr() for calculating subtotals that you can calculate with the total qualifier.3. Calculation of an AverageIf you can avoid the Aggr() function by writing your expression smarter, you should. One specific case is a standard average.For example, say that you want to calculate the average order value. This is a nested aggregation: First you need to sum the sales value per order (an order can have several order lines), and then you need to average over the resulting set of sales values per order. In other words: You can calculate it using: Avg( Aggr( Sum( Sales ), OrderID ) )But since it is a linear calculation, you can also calculate the same number just by dividing with the number of orders: Sum( Sales ) / Count( distinct OrderID )… which in most cases is evaluated faster. Also, the latter expression is easier to understand for the person responsible for the application maintenance.Bottom line: You should usually not use Aggr() for calculating an average that is a ratio of two separate aggregations.4. Static aggregationsSometimes you want to classify a dimensional value (customers, products, suppliers, etc.) using static aggregations, e.g. “Customers that only placed one order” or “Customers that bought goods at a total value higher than X”. “Static” meaning that you do not want the classification to change as you make a selection.In such a case, the aggregation should not be made in the UI, but rather when creating the data model and stored as a separate field.Bottom line: Aggregations for classification of dimensional values should often be made by creating an attribute field in the script using a “Group By”.HIC ...View More

The AGGR Function.....I thought I would start this blog post with a simple multiple choice question:Is AGGR.....a - Used in many QlikView applications to great effect b - Mis-used in many QlikView applications c - Used to return an answer without really knowing why you get the answer d - Not used at all because we're "not quite sure what is does" e - All of the aboveThe answer in my opinion is "e - All of the above".When I came to write this post and indeed the technical brief, the hardest part of all was actually coming up with a really good, easy to understand description of AGGR, as it has to make sense to both "technical" and "not so technical" people. I finally settled on the one below...When it is used, the AGGR function produces a virtual table, with one expression and grouped by one or more dimensions. The contents / result of this virtual table can then be used / aggregated by a further outer aggregation function(s).With this definition in mind, I have produced a Technical Brief and application which can be found here and here. In this document I have tried to provide an overview of the function and provided some examples of where it can be used to great effect.So, when should you use AGGR? The answer is: Whenever you want to perform an aggregation in two steps. In the technical brief we show you some scenarios like.....Largest average order value for each Salesperson for each Country, where average order value for each Salesperson is the first calculation and the largest in each Country is the second.The Salesperson responsible for the largest average order value in each CountryHow many Salespeople have average order values of less than $100 in each CountryI would also be interested in any other creative and powerful uses of AGGR you may have.Thanks.ABY ...View More

A QlikView feature that is poorly known and brilliant in its simplicity is the Preceding Load. If you don’t know what it is, then I strongly suggest that you read this blog post and find out. Because it will help you in your QlikView scripting. So what is it? It is a way for you to define successive transformations and filters so that you can load a table in one pass but still have several transformation steps. Basically it is a Load statement that loads from the Load/SELECT statement below. Example: you have a database where your dates are stored as strings and you want to use the QlikView date functions to interpret the strings. But the QlikView date functions are not available in the SELECT statement. The solution is to put a Load statement in front of the SELECT statement: (Note the absence of “From” or “Resident”.) Load Date#(OrderDate,’YYYYMMDD’) as OrderDate; SQL SELECT OrderDate FROM … ; What happens then is that the SELECT statement is evaluated first, and the result is piped into the Load statement that does the date interpretation. The fact that the SELECT statement is evaluated before the Load, is at first glance confusing, but it is not so strange. If you read a Preceding Load as Load From ( Select From ( DB_TABLE ) ) then it becomes clearer. Compare it with nested functions: How would you evaluate “Round( Exp( x ) )”. You would of course evaluate the Exp() function first and then the Round() function. That is, you evaluate it from right to left. The reason is that the Exp() function is closest to the source data, and therefore should be evaluated first. It’s the same with the Preceding Load: The SELECT is closest to the source data and should therefore be evaluated first. In both cases, you can look at it as a transformation that has an input and an output and to do it correctly, you need to start with the part of the transformation closest to the input. Any number of Loads can be “nested” this way. QlikView will start from the bottom and pipe record by record to the closest preceding Load, then to the next, etc. And it is almost always faster than running a second pass through the same table. With preceding Load, you don’t need to have the same calculation in several places. For instance, instead of writing Load ... , Age( FromDate + IterNo() – 1, BirthDate ) as Age, Date( FromDate + IterNo() – 1 ) as ReferenceDate Resident Policies While IterNo() <= ToDate - FromDate + 1 ; where the same calculation is made for both Age and ReferenceDate, I would in real life define my ReferenceDate only once and then use it in the Age function in a Preceding Load: Load ..., ReferenceDate, Age( ReferenceDate, BirthDate ) as Age;Load *, Date( FromDate + IterNo() – 1 ) as ReferenceDate Resident Policies While IterNo() <= ToDate - FromDate + 1 ; The Preceding Load has no disadvantages. Use it. You’ll love it. HIC ...View More

In a perfect world for developers and designers, their work would be approached by users on the same (or very similar) device. Ensuring a consistent aesthetic and user experience would be simple because the developer’s experience would be the same as everyone else’s.As we’re all well aware, unfortunately, we don’t live in a perfect world, and I’m not completely convinced that world would be all that perfect. There are hundreds of different devices, screen resolutions, and operating systems that contribute to a nearly endless combination of user experiences while traversing the web. In some ways this variety is very exciting in that it drives innovation, offers users choices, and potentially allows the user to customize their experience.Luckily, despite this variety, the users are basically attempting to view a document or image which is a very old form of communicating information. But what if we want users only on a particular device viewing our apps differently? For example, the Insurance Demo on the QlikView Demo site had a fairly large dashboard, and attempting to use the app on a phone would be very difficult without tiny fingers:In this case, tailoring this experience toward a particular device seems unavoidable. Fortunately, device detection can be fairly simple to do on the web. There is a variable called “navigator” that is set in most browsers that can be easily examined using javascript. Navigator contains some important and useful information such as browser type, operating system, and whether or not the device identifies itself as mobile. So how do we get that javascript into QlikView? Extensions of course! Document extensions are especially useful for performing simple javascript tasks to gather user information and pass it into QlikView by using a QlikView variable. This way, QlikView is able to take this variable and use it for whatever purpose it wants, which is much simpler than attempting to customize the page from the document extension itself.The best part is that the extension itself couldn’t be simpler. JavaScript has already done all of the work. The deviceDetect document extension referenced below, for example, consists of four lines of JavaScript code to set a QlikView variable called “vDevice” to the browser information retrieved from “navigator”:Qva.AddDocumentExtension('deviceDetect', function() { var mydoc = Qv.GetCurrentDocument(); mydoc.SetVariable("vDevice", navigator.userAgent.toLowerCase());});Using this extension on the Insurance Demo, a sheet customized for the iPhone could be shown, creating a much nicer view:Click here to download the deviceDetect document extension. ...View More

Comparative Analysis is a way to analyze data based on multiple groups. It is not a comparison between two items such as Company A vs Company B. It is all about you, the user, creating customized groups on demand. For example, you might have seen a graph like this, which is a standard way to compare between companies.Note: The sketch was created for a demo purpose only.Comparative analysis lets you group these three companies as portfolio A and compare to another portfolio group B, which may have one company swapped. And then, you may compare which grouping is a better choice for you. Note: The sketch is created for a demo purpose only.In this example, I have used the same type of values as two groups; however, you can mix different level of data or different types of data. For example, you may choose one company against an entire industry for business growth comparison, or comparing one country, such as Japan, to a state in the US, California, for population increases. The most common usage of comparative analysis is for basket analysis. If you would like to try this functionality, go to Financial Stocks Analysis demo > Comparative Analysis tab. Make selections as you’d like and you will see the top chart being populated for your groups.Here is technical brief on how to enable comparative analysis. You can download the Financial Stocks Analysis demo or Whats New in QlikView11 demo to see how this functionality is implemented. Here is the video of Comparative Analysis recorded by Michael Anthony. Shima Auzins ...View More

Sometimes when you load data into QlikView you have validity ranges, but the range is only implied by one field – a single change date. It could be like in the table to the right where you have currency rates for multiple currencies: Each currency rate change is on its own row; each with a new conversion rate. Also, the table could contain rows with empty dates corresponding to the initial conversion rate, before the first change was made. This problem is very similar to the one in a previous blog post (How to populate a sparsely populated field) but this time I will approach the problem in a different way. Instead of inserting records and populating these with the correct field values, I will instead maintain the number of rows and create a new column “To Date”, so that the new table will become a list of intervals. Here’s how you do it: Determine which time range you want to work with. The beginning of the range must be before the first date in data and the end of the range must be after the last. Load the source data, but change empty dates to the beginning of the range defined in the previous bullet. The change date should be loaded as “From Date”. Sort the table first according to Currency, then according to the “From Date” descending so that you have the latest dates on top. Run a second pass through data where you calculate the “To Date”. If the current record has a different currency from the previous record, then it is the first record of a new currency (but its last interval), so you should use the end of the range defined in bullet 1. If it is the same Currency, you should take the “From Date” from the previous record, subtract a small amount of time, and use this value as “To Date” in the current record. In the QlikView script, it could look like this: Let vBeginTime = Num('1/1/2013');Let vEndTime = Num(Now()); Tmp_Rates:LOAD Currency, Rate, Date(If(IsNum([Change Date]), [Change Date], $(#vBeginTime))) as FromDate FROM Rates ; Rates:LOAD Currency, Rate, FromDate, Date(If(Currency=Peek(Currency),Peek(FromDate)-0.00000001, $(#vEndTime))) asToDate Resident Tmp_Rates Order By Currency, FromDate Desc; Drop Table Tmp_Rates; When this is done, you will have a table listing the intervals correctly. This table can then be used in a While loop to generate all dates in the intervals (See Creating Reference Dates for Intervals) or with an IntervalMatch to compare with an existing date. In this example, I subtract 0.00000001 from the date in the previous record. This corresponds to roughly a millisecond. This means that the “To Date” will have a value of one millisecond before midnight, but formatted to show the date only. The reason I do it this way, is for the IntervalMatch to work: No point in time will belong to two intervals. HIC Further reading related to this topic: IntervalMatch ...View More

A common recurring question on the QlikCommunity forum is around dates that don’t work. Here follows a help on fixing the three most common causes. If you encounter such a question on the forum, just link to this post in your answer.

...View More