I recently updated to iOS7….hmm...The new look and feel is certainly delightful, although, one particular thing really got me frustrated. While exploring Apple’s new eye candy, I was trying to delete an email from my phone by the usual way, which is by swiping my thumb from left to right. Obviously, I was expecting a delete button to show up. I tried once, twice and several times, it just wouldn’t do anything and the most frustrating part was that it opened the email instead of deleting it. At this point, I gave up and went into my setting to see if something was turned off…and nothing! Finally I decided to do a web search and AHAA!....They actually switched the whole interaction paradigm of deleting an email from swiping from left to right to now swiping from right to left.This was the complete opposite of the model that I had carried in my mind for all these years. This was just a small example of a mismatch in the user’s mental model to the designer’s mental model which caused some degree of frustration and took some time and efforts in figuring out. In this instance, the learning curve was quite low, but what happens when there are so many things in an interface which throw you off because what you expect it to do, is not what it does in reality. This not only causes frustration but a repulsion effect and even the things that work well go un-noticed spoiling the whole experience.A designer’s goal should be to keep the learning curve as smooth as possible. This is when the concept of mental models comes into play. A mental model is a person’s perception and an understanding of how something works based on prior knowledge and experience, it is based on beliefs not facts. These mental models are very loose and keep evolving over time; they cannot be visualized and often times aren’t accurate.Today, we are constantly surrounded by different systems and interfaces that we have to keep adapting to. Although, these interfaces differ from one another, there are certain design standards that have evolved over time, which they all follow. People form a mental model of how these interfaces work. For example, one of the reasons why we place our Filters on the left side of the sheet in a QlikView application is because we are all used to the web where mostly the navigation panel is always on the left side. Leveraging these already formed mental models in our designs can be very useful in creating a seamless interaction pattern and creating a good user experience.However, sometimes, it becomes necessary to challenge the standards in order to keep up with the changing technological landscape. This needs a seamless transition of the already formed mental models to new and evolved paradigms. As we move slowly into the phase of designing QlikView applications in the 'Next' version, these considerations will become vital to ensure a smooth transition. The below mentioned design considerations can be helpful in achieving this -Providing subtle cues so that users don’t get lostFrequently used functions to be easily accessibleProviding certain familiarity aspects in the new design which users can recognize easilyProviding flexibilityAlways providing a feedback from the system to the userMore information on this topic can be found at http://www.nngroup.com/articles/mental-models/. ...View More

There are many different areas in QlikView where the developer can define a specific color to be used. Most times developers will simply navigate to the color picker and select the color that is closest to the color that they are looking for.QlikView also offers the user ways to define what the color should be based on the result of an expression. This gives the developer the ability to change the color at will. QlikView also gives the developer the ability to define the opaqueness of a specified color as well. This is particularly useful when scatter chart coordinates fall within the same intersection. The two functions that I want to talk about are RGB() and ARGB().To show the RGB() and ARGB() functions in action I will run through a couple of examples from the Pharma Sales Demo on demo.qlik.com.Scenario 1 – Coloring the background in a table based on an expression result using RGB()On the Expressions tab within the Object Properties, we are going to create the expression in the Background Color area that will prompt QlikView to set the background color of the Quarter cells.We need to fill the background color with green if the quarter is greater than the previous quarter, to fill the background with yellow if the quarter is equal to the previous quarter and fill the background with red if the quarter is lower than the previous quarter.The expression looks like this:If([Q2]>[Q1], rgb(108,179,63),If([Q2]=[Q1], yellow(),If([Q2]<[Q1], rgb(255,110,110))))Because we are filling the background with a specific color based solely on the result of the calculation, we used the RGB() function. Scenario 2 – Specify the colors of data points on a scatter chart based on an expression using ARGB()In this chart we will use ARGB() to show multiple plot points on a scatter chart that are placed on top of each other.If we used RGB() in this chart, we would not be able to see the different levels of the same color on one plot point.The requirement is to fill the background and the text color as follows:Must Call = RedNeed To Call = YellowRecent Call = GreenNot Targeted = GreyThe expression looks like this:if([Call Priority]='Must Call', argb(175,242,0,0),if([Call Priority]='Need To Call',argb(175,254,197,80),if([Call Priority]='Recent Call', argb(175,111,176,29),if([Call Priority]='Not Targeted',argb(175,190,190,190) ))))The expression result shows how the opaqueness allows the user to see multiple intersecting plot points, helping the user to get a clearer picture of all of the data points. As is shown above, without the opaqueness, the green plots on the plot of 1 would look like a solid green dot instead of showing that there is more than one plot point.We can also use the ARGB() function with maps as well. Using the same expression as above we can see how the opaqueness allows the user to see multiple intersecting plot points as well as original map data such as city names, street names, etc. In conclusion, if you want to specify the color of your backgrounds, text colors, data points, etc, you can with QlikView. The take away from this should be to think before you color. If you are coloring a background or text, use RGB(). If you are coloring data points on a scatter chart and some or many of the data points will overlap, you may want to consider ARGB(). I wrote a technical paper about this that goes into a little more detail. You can find it here.Happy Qliking!! ...View More

2 years ago I was certain that the tablet craze would not reach the business world.I was convinced that our smart phones were too small and limited to provide any real business value.I was wrong. Dead wrong.Since then we have stopped talking about mobile or desktop, instead we talk about software. We expect the software we use to work everywhere, anytime and on any device.Luckily QlikView makes it easy for us; the AJAX-client works just as well in the browser as it does on a tablet or even on a smartphone.However every device comes with its own screen real estate so if we optimize our apps for a desktop experience our tablet users will be less than pleased and vice versa. Technically the app would work on all devices but if you were on an iPad perhaps the buttons should be a tad bit bigger, the width and maybe the length of the app smaller and so on.So how do we achieve this without having to deploy one app optimized for every device/screen?Not only would that be a maintenance nightmare but it would also scale horrible as we would potentially have to load the same application twice into the working memory.ClientPlatform() to the rescue!ClientPlatform() returns a string containing the platform the user is using, see table below.With this information we could switch between a dashboard optimized for a desktop experience and a tablet experience depending on the device the user is using at the time within the same QlikView application.For example using WildMatch(ClientPlatform(),'*mobile*') = 1 in a Show Condition would enable the sheet or object for mobile devices but it would be hidden for a desktop user.Now we can design our apps to cater for a perfect user experience regardless of which device the user is using.If you own an iPad you can see it in action by visiting our demo site and browse to the Pro Golf app which will change look and feel between a desktop and an iPad.This is a few examples on what the ClientPlatform() can return.BrowserClientPlatform()Internet Explorer <VersionNumber>browser.MSIE <VersionNumber>Google Chromebrowser.chromeFirefox <VersionNumber>browser.gecko <VersionNumber>iPadbrowser.safari.mobileAndroid Tabletbrowser.android ...View More

Modern data visualization and business intelligence has its roots in the very ancient organization methods of information itself. One of the earliest forms of organization was simply drawing lines in the dirt or sand. This gave way to using beans or stones moved around in grooves in the dirt/sand and eventually on stone or wooden boards. This became what we now know as the abacus between 2700–2300 BC with the Sumerians. The abacus was then adopted by cultures all around the Mediterranean. As written languages developed and matured there became written records of information regarding a variety of fields such as commerce, taxation, the sciences, etc. This information remained as written text however. There was no easy way to see trends or outliers in the data collected.Data visualization really took off in the 18th century when William Playfair, a Scottish engineer & political economist, brought the intellectual enthusiasm of the Enlightenment to data. Playfair went on to invent the line, bar, pie, and circle charts. Other notable visualizations followed such as Dr. John Snow's 1854 dot distribution map of cholera cases in London, Florence Nightingale's polar area diagram visualizing mortality rates in the field hospitals she managed during the Crimean war, and Charles Minard's 1869 flow map of Napoleon's failed Russian campaign of 1812.Fast forward to the 1950s when IBM researcher Hans Peter Luhn coined the term "business intelligence" in his 1958 paper A Business Intelligence System. His work laid the groundwork for modern information sciences. What we largely recognize as modern BI really developed over the years beginning in the 1960s right up through the 1980s. Statistician Francis Anscombe helped demonstrate the value of data visualization in his 1973 Anscombe's quartet which is a series of four datasets with nearly identical properties but look very different when visualized. He was making the point that relying solely on a table of values wasn't enough to fully understand the data. Visualizing the data was crucial to seeing trends and outliers.Our modern tools for Business Intelligence have never been more powerful. The challenge of taking action on your data though is largely the same today as it was thousands of years ago. Businesses and individuals are increasingly looking for ways to not only see their data but understand it in ways unimaginable to our ancestors drawing lines in the sand. ...View More

Often when you create scripts, you need to create new fields, based on counters. There are several ways to do this. Some are simple, others not so… Using RecNo() The RecNo() function simply counts the input records and returns the number of the current record. Simple, and very useful if you want to create a record ID. However, if you concatenate several input files, or use a While clause, the numbers will not be unique. Using RowNo() The RowNo() function is very similar to the RecNo(), but this instead counts the output records and returns the number of the current record. Also simple and useful, especially if you concatenate several input tables. In such a case, the function will return consecutive numbers for the output table. Using AutoNumber() The AutoNumber() function is useful if you want to put a number on a specific field value, or on an attribute that is a combination of field values. The attributes will be numbered and their numbers re-used appropriately. AutoNumber( Product & '|' & Date ) as ID Using inline Peek() and RangeSum() This is the mother of all record counters. Anything can be numbered; it can be done conditionally and anything can be used as condition. The idea is to fetch the counter value from the previous record and increase it only if some condition is fulfilled. Or reset it. An example: For production quality control, some process indicator is measured and the Quality Manager wants to track trends and trend shifts. Then it is important to see the number of consecutive days that this indicator has increased or decreased. It is also good if the series of consecutive days can get a unique ID that can be used for selections and charts. The following script creates these two fields; TrendID and DaysWithTrend. Load *, If( Change * Peek( Change ) > 0, Peek( TrendID ), RangeSum( 1, Peek( TrendID )) ) as TrendID, If( Change * Peek( Change ) > 0, RangeSum( 1, Peek( DaysWithTrend )), 0 ) as DaysWithTrend;Load Indicator, Indicator - Peek( Indicator ) as ChangeResident Daily_TQM_Measurement Order By Date; First of all, the change of the indicator value is calculated in the bottom Load using the Peek() function. In the preceding Load, the change is used as condition in the If() function. The condition compares current record with the previous record using the last change and the second last change. If the product of the two is greater than zero, the trend has been the same two days in a row, so the TrendID of the previous record is used (the Peek() function) and DaysWithTrend is increased by one. But if the condition is false, the TrendID is increased by one and the DaysWithTrend is reset to zero. When a counter is increased, normal addition cannot be used since the Peek() will return NULL for the very first record. Instead the addition is made using the RangeSum() function. Summary: You can create any auto-incrementing counter in a QlikView script. Just choose your counter function… HIC ...View More

The use of icons and other visual cues has been proved as a great value asset in data visualization. Icons let people to better understand complex information. Another good thing about icons is that they let us communicate globally. An up-pointing triangle is universally understood as a representation of growth.QlikView brings you the possibility to choose from a predesigned set of visual indicators.A good alternative to the standard icons is the Unicode Geometric Shapes set. “Unicode is a computing industry standard for the consistent encoding, representation and handling of text expressed in most of the world's writing systems.” (Source Wikipedia). Geometric Shapes is a set of visual indicators that effectively work as a text character. This means, you could use it everywhere you can write text in QlikView. Text objects, captions, cells, sheet titles, etc.The main advantage of using Unicode Geometric shapes over images is the fact that you can control their properties, like size and color as any other text character. You could, for example, set up an expression to change the color based on an expression.Another good example of how to take advantage of Unicode Shapes is including them as a part of number format pattern. You can predetermine how the shape will look for positive and negative values.▲ #,##0.0%; ▼ #,##0.0% Using this pattern will let you integrate an icon inside a cell like as in Change column in the table below.Color is, in some situations, not enough to differentiate from one state to another. Using different fills or patterns will help color blind people to identify differences.To include any of these shapes into your next QlikView project you just need to copy the shape from here and paste it where needed.0123456789ABCDEFU+25Ax■□▢▣▤▥▦▧▨▩▪▫▬▭▮▯U+25Bx▰▱▲△▴▵▶▷▸▹►▻▼▽▾▿U+25Cx◀◁◂◃◄◅◆◇◈◉◊○◌◍◎●U+25Dx◐◑◒◓◔◕◖◗◘◙◚◛◜◝◞◟U+25Ex◠◡◢◣◤◥◦◧◨◩◪◫◬◭◮◯Note: Remember to test your app to make sure it renders properly on every possible browser.Enjoy Qliking!AMZUpdate: Here you can get a working app with some examples on how to use these shapes. ...View More

When a calculation is made in a QlikView session, it always involves an aggregation over the relevant data records. But which records are relevant? What is the scope of the aggregation?

...View More

The visualizations and charts in QlikView are dynamic and always changing based on the user’s selections and interactions with the data. That being said I rarely find the need to print the visualizations and take them with me because I know that the data may be refreshed thus changing the visualizations and charts. But sometimes there is a need for the user to be able to print a static report of what they see in QlikView to share with someone or refer to at a later time. QlikView’s Report Editor provides users with an easy way to capture the QlikView objects in a formatted report that can printed. Reports can be created by simply dragging objects from the user interface to the report sheet.User InterfaceReport sheetSimply drag the object from the user interface and place them anywhere on the report. Objects can be moved and resized in the report and arranged as you see fit. When I need to add objects to the report that are not a part of the user interface such as an image for the cover sheet or a logo, I will add the object to a hidden sheet in QlikView and format it as I would like it to appear in the report. Then I can drag it to the report. There is also the ability to add an image, text object or current selection box directly from the Report Editor if you prefer. In either case, static reports that are nicely formatted can be created in QlikView for users who prefer to have a printout of their visual QlikView experience.Check out this technical brief for step-by-step details of how the reports were added to the Asset Management demo using the Report Editor.Jennell ...View More

Iterations – or loops – are constructions where a set of statements are executed zero or more times, until some condition is met. They are very common in all programming languages, and QlikView scripting is no exception.

...View More

In some situations in Business Intelligence you need to make simulations, sometimes referred to as "Monte Carlo methods". These are algorithms that use repeated random number sampling to obtain approximate numerical results. In other words – using a random number as input many times, the methods calculate probabilities just like actually playing and logging your results in a real casino situation: hence the name.These methods are used mainly to model phenomena with significant uncertainty in inputs, e.g. the calculation of risks, the prices of stock options, etc.QlikView is very well suited for Monte Carlo simulations.The basic idea is to generate data in the QlikView script using the random number generator Rand() in combination with a Load … Autogenerate, which generates a number of records without using an explicit input table.To describe your simulation model properly, you need to do some programming in the QlikView script. Sometimes a lot. However, this is straightforward if you are used to writing formulae and programming code, e.g. Visual Basic scripts.The Rand() function creates a uniformly distributed random number in the interval [0,1], which probably isn’t good enough for your needs: You most likely need to generate numbers that are distributed according to some specific probability density function. Luckily, it is in many cases not difficult to convert the result of Rand() to a random number with a different distribution.The method used for this is called Inverse Transform Sampling: Basically, you take the cumulative probability function of the distribution, invert it, and use the Rand() function as input. See figure below.The most common probability distributions already exist in QlikView as inverse cumulative functions; Normal T, F and Chi-squared. Additional functions can be created with some math knowledge. The following definitions can be used for the most common distributions:Normal distribution: NormInv( Rand(), m, s )Log-Normal distribution: Exp( NormInv( Rand(), m, s ))Student's T-distribution: TInv( Rand(), d )F-distribution: FInv( Rand(), d1, d2 )Chi-squared distribution: ChiInv( Rand(), d )Exponential distribution: -m * Log( Rand() )Cauchy distribution: Tan( Pi() * (Rand()-0.5) )Finally, an example that shows the principles around Monte Carlo methods: You want to estimate π (pi) using a Monte Carlo method. Then you could generate an arbitrary position x,y where both x and y are between 0 and 1, and calculate the distance to the origin. The script would e.g. be:Load *, Sqrt(x*x + y*y) as r;Load Rand() as x, Rand() as y, RecNo() as ID Autogenerate 1000;The ratio between the number of instances that are within one unit of distance from the origin and the total number of instances should be π/4. Hence π can be estimated through 4*Count( If(r<=1, ID)) / Count(ID).Bottom line: Should you need to make Monte Carlo simulations – don’t hesitate to use QlikView. You will be able to do quite a lot.HICSee also the Tech Brief on how to generate data. ...View More