Do you know the difference between the RecNo() and the RowNo() functions? You may think that you can use these interchangeably to count the records or rows in a table but these functions differ slightly. The RecNo() function will return an integer for the record currently being read from the source table starting with number one. The RowNo() function also returns an integer but it represents the position of the row in the resulting QlikView internal table. So while RecNo() counts from the source table, RowNo() counts from the resulting table.In the script below the fields from the Temp table are loaded along with fields to represent the RecNo and the RowNo. A Where clause is used to load all records where Number does not equal 3.The resulting table below shows the results of this script. In the RecNo field we see values 1 through 10 excluding 3. This is because the script excluded records where Number = 3 so while that record was read, it was excluded from the results. In the RowNo field we see values 1 through 9 for the position of each row in the results table. The fact that some rows were excluded does not affect the RowNo field because we are only looking at the internal table that is created. The RecNo field indicated the order in which the records were read from the source table and the RowNo field indicates the positon of the record in the internal table.To see an example, check out my technical brief that includes the QVW and detailed descriptions of these functions. In the technical brief, I also discuss the IterNo() function and the FieldValueCount() functions.Jennell
...View More
When you want to look at the distribution of a measurement, a histogram is one possibility. However, if you want to show the distribution split over several dimensional values, a Box Plot may be a better choice.You may, for instance, want to evaluate the quality of units produced in different machines, or delivered by different suppliers. Then, a Box Plot is an excellent choice to display the characteristic that you want to examine:The graph clearly shows you the performance of the different machines compared to target: Machine A has the precision, but not the accuracy. Machine F has the accuracy, but not the precision.The Box Plot provides an intuitive graphical representation of several properties of the data set. The box itself represents the main group of measurements, with a center line representing the middle of the data. Usually the median and the upper and lower quartile levels are used to define the box, but it is also possible to use the average plus/minus one standard deviation.The whiskers are used to show the spread of the data, e.g. the largest and smallest measurements can be used. Usually, however, the definition is slightly more intricate. Below I will use the definition used in six sigma implementations.There, the whiskers are often used to depict the largest and smallest values within an acceptable range, whereas values outside this range are outliers.The concept of the Inter Quartile Range (IQR) – the difference between the upper and lower quartile level – is used to calculate the acceptance range. Hence:Inter Quartile Range (IQR) = Upper Quartile Line (UQL) – Lower Quartile Line (LQL)Upper Acceptance Limit (UAL) = UQL + 1.5 * IQRLower Acceptance Limit (LAL) = LQL - 1.5 * IQRThe picture below summarizes the box plot.And here is how you implement this in QlikView…Go to the Tools menu and choose “Box Plot Wizard”.On the “Step 1 - Define data” page, you choose your dimension. In my example, this was Machine, but it could be Supplier or Batch or something similar.Use the same dimension once more in the “Aggregator” control.Use the average of your measurement in the “Expression” control – Avg(Measurement).Click “Next”.On the “Step 2 - Presentation” page, you should choose “Median mode”.Check “Include Whiskers” and “Use Outliers”.Click “Finish”.QlikView has now created a Box Plot with general expressions that almost always display a meaningful result, and allows for an intermediate aggregator. However, the expressions are not what we want for a six sigma box plot, so we need to change them to the following: (Below, the dimension is called Dim, and the measurement is called Val.)Box Plot Middle: Median(Val)Box Plot Bottom: Fractile(Val,0.25)Box Plot Top: Fractile(Val,0.75)The whiskers and the outliers all need a nested aggregation – each value needs to be compared to the acceptance levels for the group – so they all contain an Aggr() function that calculates the relevant acceptance limit:Box Plot Lower Whisker: Min(If(Val>= Aggr(2.5*Fractile(total <Dim> Val,0.25) -1.5*Fractile(total <Dim> Val,0.75), Dim, Val), Val))Box Plot Upper Whisker: Max(If(Val<= Aggr(2.5*Fractile(total <Dim> Val,0.75) -1.5*Fractile(total <Dim> Val,0.25), Dim, Val), Val))Lower Outlier: Min(If(Val< Aggr(2.5*Fractile(total <Dim> Val,0.25) -1.5*Fractile(total <Dim> Val,0.75), Dim, Val), Val))Upper Outlier: Max(If(Val> Aggr(2.5*Fractile(total <Dim> Val,0.75) -1.5*Fractile(total <Dim> Val,0.25), Dim, Val), Val))And with this, I leave you to create your own box plots.HICFurther reading related to data classification:Recipe for a HistogramBucketsRecipe for a Pareto Analysis
...View More
In quality control, you often want to look at the distribution of a measurement, to understand how the output of a process or a machine relates to expectations; to targets and specifications. In such a case, a histogram (or frequency plot) is one possibility.It could be that you want to examine some physical property of the output of a machine, and want to see how close to target the produced units are. Then you could plot the measurements in a chart like the following:The above graph clearly shows you the distribution of the output of the machine: Most measurements are around target and the peak of the distribution is in fact slightly above target. But the histogram also raises questions: Is the variation small enough? And why is there such a long tail towards lower values? Could it be that we have a problem with a machine?Finding such questions and their answers is central in all quality work, and the histogram is a good tool in helping you find them.A histogram is special type of bar chart, and is easy to create in QlikView. A peculiarity is that it uses only one field, not several: As dimension, it uses the measurement in grouped form: Each measurement is assigned to an interval or bin, and this way the dimension gets discrete values.As expression it uses the count of the measurement, and so the graph shows the distribution of one single field.One small challenge is to determine how many bins the histogram should have: Having too many bins will exaggerate the variation, whereas too few will obscure it. A simple rule of thumb is to have 10-15 bins.This is how you create a histogram in QlikView:Create an Input Box. In its properties, create a new variable called BinWidth. Click OK.Set BinWidth to 1 in the Input Box.Create a Bar Chart with a calculated dimension, using =Round(Value, BinWidth)Set the label for the calculated dimension to “Measurement”. Click Next.Use Count(Value) as expression. Click Next.Sort the calculated dimension numerically. Click Next three times.On the “Axes” page, enable “Continuous” on the Dimension Axis. Click Next.On the “Colors” page, disable the “Multicolored” under Data appearance. Click Finish.You should now have a histogram.If you have too few bars, you need to make the bin width smaller. If you have too many, you should make it bigger.In order to make the histogram more elaborate you can also do the following:Add error bars to the bins. The error (uncertainty) of a bar is in this case the square root of the bar content, i.e. Sqrt(Count(Value))Add a second expression containing a Gaussian curve (bell curve):Convert the chart to a Combo chartUse the following as expression for the bell curve: Only(Normdist(Round(Value,BinWidth),Avg(total Value),Stdev(total Value), 0))*BinWidth*Count(total Value)Use bars for the measurement and line for the curve.With these changes, you can quickly assess whether the measurements are normally distributed or whether there are some anomalies.Good luck!HICFurther reading related to data classification:Recipe for a Box PlotRecipe for a Pareto AnalysisBuckets
...View More
If you think that your typeface or font that you use does nothing more than just conveying the content of the words themselves or just add an artistic streak to your page then, you’re wrong! Rather I must put it this way, YOU’RE WRONG! Notice my tone of voice change when I used the uppercase letters instead of lowercase in stating ‘You’re Wrong’. The UPPERCASE Bold letters not only communicate the message boldly but do it in a strong assertive, almost authoritative manner. Typography forms a quintessential part of Graphic Design and Visual Communication. In fact, typography makes 95% of Graphic design.The visual aspect of type has as much effect on how the content is communicated and perceived as does the verbal aspect. Visual appearance of text can communicate more than the text itself like expressing the mood, personality, gender, age or situation that is in context. It can also give visual cues about the hierarchy of the content, how to read it and which part is more important than the other. It can be perceived the same way as wearing the right clothes for the right occasion like wearing formal clothes for a job interview and wearing a casual pair of shorts for a barbecue house party.For instance, the 2 images below use a different typeface for the tag line.Although the one on the left side is a clean, legible and a good looking typeface, it doesn't suite the context at all. Whereas the one on the right not only suits the context but also builds the atmosphere and gives certain punch and flavor to the poster. If we put the context aside and look at both typefaces side by side there is no grounds for comparison since both have distinct personalities but if compared on a contextual level, the type used on the right immediately stands out as better suited to the image than the one on the left.Typefaces have their own distinct personalities and it is the role of a designer to decide which typeface will suite which condition. However, certain general rules can be established depending on the typeface.Recommendations for using typefacesSans Serif fonts (Arial, Helvetica) are better to use for body text while Serif fonts (Garamond, Times New Roman) are better to use for headings and titles.Avoid combination of similar typefaces together like Franklin Gothic and Helvetica since types from the same family (in this case Geometric Sans) break the visual harmony as they don’t look alike but are not totally different. For typeface combinations contrasting fonts work best like Helvetica and Garamond.Avoid using more than 2-3 types per design.Create a gradation in weight to show hierarchy in text and for better visual flow.Use Script fonts and accent fonts sparingly only to highlight elements in a design.When unsure stick to traditional and popular fonts like Arial.Consider the tone of voice and context when choosing an appropriate font.Google fonts are a great way to integrate some of the web fonts in QlikView. See blog post by Arturo Munoz about 8 ways to customize your QlikView applications with Google Fonts in which he talks about the step by step process of incorporating Google fonts in QlikView.In summary, each typeface has a different personality which needs to be identified and applied to the context accordingly. And remember, apart from saying all about the design the typeface also adds the much needed aesthetic streak to the design so don’t hesitate to experiment.postscript - Typeface & Font – What’s the difference?People often get confused between typeface and font and sometimes use it interchangeably. Typeface is the style of design of the letters, symbols or numbers whereas fonts are a set of printable text characters in a specific style. For example, Arial, Times New Roman, Garamond are typefaces but Arial Bold 12 points or Times New Roman Regular 9 points are fonts.Resourceshttp://www.smashingmagazine.com/2009/08/20/typographic-design-survey-best-practices-from-the-best-blogs/http://www.fastcodesign.com/1664719/infographic-of-the-day-why-should-you-care-about-typography
...View More
One of the topics we always cover in our data visualization training courses is the font family selection within your app. With the increasing number of devices and platforms accessing to your QlikView apps, details such as font size and type have become in a very important piece of the app success.As a rule of thumb our recommendation is to keep your app as simple as possible, meaning using Arial or any other font that work well across devices. It’s important to note that fonts such as Tahoma or MS Calibri will force those devices that don’t come with those fonts installed, to pick an alternative font based on the font family. That may cause your app to be unreadable and/or blurry in some situations.But let’s say you really need to use a non-standard typeface to customize one of your apps. Is that doable with QlikView?The answer is yes, you can customize QlikView apps based on your needs and use an external font from Google or any other font servers.To do so you will need to install and customize a document extension – you can download it at the bottom of this post - and then follow the next steps:1. Search or browse font families at google.com/fonts you want to use in your QV app For this example, I’m going to pick the Abel font for my project, one of the many open source fonts hosted at google.com/fonts. You could choose a different provider or just host the font file on your own server.2. Once you have chosen your font, grab a copy of the font file and install it on the computer where you are developing your app and in the machine(s) that host the QlikView Server(s). This is especially important: you must install the font on the QV server host machine if you want your charts to be rendered using the selected font.3. Open your QlikView app with QlikView Desktop and modify the font as usual:4. Once we are done applying your font across all the app objects it’s the time to review the document extension.5. Download the extension attached at the bottom of this post, rename the file from ExtensionName.qar to ExtensionName.zip and unzip it.A document extension will include at least 2 files, Definition.XML and Script.js.The JS file will look like the example below where Abel is the font I chosen.Qva.AddDocumentExtension('customfonts', function(){ //Load a CSS style sheet Qva.LoadCSS("http://fonts.googleapis.com/css?family=Abel"); });Edit Script.js file and modify it to include your chosen font name as in the example above.Compress the 2 files into a zip file and then rename it as ExtensionName.qar6. Install the document extension in your environment. More info on how to do it here: http://www.qlikblog.at/1597/qliktip-40-installingdeploying-qlikview-extensions/ 7. Go back to QlikView and activate the document extension in the app:From now on when the page loads, QlikView will include the extension and the font will be loaded across devices.8. Publish your app to the server and enjoy your custom fonts in any device.As a general recommendation you should stick to one of the 'standard' fonts such as Arial but in the case of you trying something new, following the described 8 steps you should be able to deploy a QV app to a server with a non-standard font on it.PROS: Wide range of font styles to choose from, easy app customization, web standard fonts, works well across any device.CONS: Web only, small impact on page load time.Enjoy Qliking!Arturo
...View More
As most of you have noticed – I hope – we have now released a new product.Qlik Sense.Qlik Sense is not just a new release of QlikView. Instead it is something different. But there are still so many similarities between the two products, so I thought it would be appropriate to dedicate a blog post to differences and similarities between the two.Basically, the two products are two different user interfaces to the same analysis engine. This means that old scripts and old formulae will (almost) always work exactly the same way as before. (There are some smaller differences in that Qlik Sense uses libraries, and cannot always use relative paths for files.)Hence, the two products both have the same Green-White-Gray logic; both use the same calculation engine; both have roughly the same response times; and you should use the same considerations for both when it comes to data modelling. This also means that many of the previous posts here on the Design Blog are just as relevant for Qlik Sense as for QlikView.But the two products are still very different. And just as a parent cannot say that one child is better than the other, I cannot say that one product is better than the other. They are good at different things:QlikView is a tool for situations where you want prepared business applications, i.e. applications created by developers who put a lot of thought into the data model, the layout, the charts and the formulae; and deliver the applications to end-users who consume the applications. We call this Guided Analytics. The end-user has total freedom to explore data, select, drill down and navigate in the information, and can this way discover both questions and answers in the data. The end-user is however limited when it comes to creating new visualizations. This type of situation will without doubt be common for many, many years to come.Qlik Sense is a tool for situations where you don’t want to pre-can so much. Instead you want the user to have the freedom to create a layout of his own and in it, new visualizations; charts that the developer couldn’t imagine that the user wants to see. You want Self-service data discovery, which means a much more active, modern, engaged user. In addition, Qlik Sense is much easier to use when you have a touch screen, and is adaptive to different screen sizes and form factors. On the whole, Qlik Sense is a much more modern tool.Finally, it is important to acknowledge that a piece of software is never ready. It evolves constantly:Qlik Sense today is only the first version of something that will evolve further and get more features and functions as time goes on. Some of the features and functions of QlikView have not yet been implemented in Qlik Sense – there just hasn’t been time enough – but many of them will be implemented in coming versions.Also QlikView is not yet a "final product". The product will be developed further, and most likely we will see some of the new functionality from Qlik Sense also in coming versions of QlikView. The goal is to use the same platform for both user interfaces.With these two tools, we believe that we are well prepared for the future.HIC
...View More
Not all of your data is equally important. Some things are more important than others. It’s your job as the creator of a guided analytics application to prioritize information, to create a hierarchy for users to better understand the information. There is an implied level of importance based on where you place something and what it looks like. Be on the front pageBeing on the first page someone sees when starting an experience is best. Like the front page of a newspaper the Dashboard of an application has an added level of importance by the very nature of being first. What you choose to lead with says a lot about what you want your audience to know above all else. It isn't that the subsequent pages aren't important it is just that they are perceived as less important in the hierarchy of pages because they aren't first. Leading with a Dashboard page is all a part of the DAR methodology which is a useful way to organize your content across multiple pages.Top of the pageLike being the first page a user sees, being at the top of the page is prime real-estate. Designers use placement to denote importance online all the time. In ecommerce design the top area (the Aspot) is the largest marketing space and has the greatest prominence. From there subsequent spaces (B spots, C spots, etc.) cascade down the page in lesser and lesser importance. The further down the page something is, the less important it is. Content at the top of the page sits above the fold (to reference newspapers again) and therefor should be universally seen by users on any device resolution before scrolling.F formation down the pageWhile we may read long-form content in a Z formation down a page we scan/read most online content in an F formation. We read the first line, then a bit of the second line, then sort of work our way down the left side of a page hunting for keywords. This works for article pages online but it also works with larger object oriented pages like shopping experiences with product photos. Again this reaffirms that the content at the top of the page is the most important real-estate on a page especially the top left. The top of the page gives a reader an idea of what content a page may contain and the scent of whether or not he/she is on the right track to finding what they are looking for.StyleOther than being at the top of the first page a final technique to denote importance is how you choose to size & style your content. Most people are familiar with this concept as it applies to typography. Magazines, newspapers, websites, etc. all make the most important information the largest type size. As the type sizes are reduced the implied importance of that information is also diminished. You can also use contrast to set some information apart from the rest. Where all of your text is a dark gray perhaps one part is bright red. Where most of your lines in a line chart follow a similar color scheme, perhaps one contrasts that scheme to stand out. Essentially you are telling the user that this thing that visually stands apart from the rest is important and something they should focus on.Establishing a hierarchy of information is beneficial to the user of a guided analytics application because it reduces the cognitive load of the user. When users don't have to sift through all of the content to decided what is most important they have more time to focus on using the application and meeting their business needs.
...View More
I find that the Dual and Exists functions are useful functions that I often use in my script. The Dual function allows me to represent a string field numerically, making it ideal for sorting purposes. The Exists function, used in conjunction with the Where clause, allows me to only load related data into my data model.The Dual function can be used in the script and chart expressions. I tend to use it in the script when I need the ability to sort a string field numerically. For example, in the script snippet below, I created age buckets and I want to be able to sort them in a list box and a chart so I used the Dual function to give each bucket a numeric value.By using the Dual function, I am able to sort the AgeBucket list box and the AgeBucket dimension in my chart numerically.The Exists function is used to determine whether a “specific field value exists in a specified field of the data loaded so far.” I often use this with the Where clause to load data only when the specified data has already been loaded in the data model. For example, in the script below the Age and AgeBucket fields are loaded only if the PersonID has already been loaded in the data model.Notice in the AgeTemp table that there are ages listed for PersonID 11 and 12 but since those IDs were not loaded in the data model (in the People table), they are excluded by the Where Exists(PersonID) clause. The table below shows the data that was loaded.I often blog about functions used in QlikView but I find the Dual and Exists to be two functions that I use often so that I can present data in a desired sort order and can keep my data model free of unassociated data. If you would like to learn more or see an example application, check out my technical brief.Thanks,Jennell
...View More
Often when creating a QlikView application, you want to add some grouping of a number, and then use this as a dimension in a chart or as a field where you make selections.
Sometimes you want QlikView to open with a specific set of selections, apply a bookmark or perhaps even deep link to a specific sheet.A typical use case could be to embed an entire app or a single object inside a CRM or ERP system and depending on the context, current customer for example, filter the QlikView app to only show records related to that specific context.So how do I use this black magic?One approach would be to use triggers with the obvious downside being that the trigger would always fire regardless of how you opened the app.Another approach is to supply a set of parameters to the URL for that specific app.Let’s take an example, the Sales Compass demo from the demo site. Below us the URL to access the app and the different components explained.Actual URLdemo.qlik.com/QvAJAXZfc/opendoc.htm?document=qvdocs%2FSales%20Compass.qvw&host=demo11Explained URL<host name>/<virtual directory>/opendoc.htm?document=<url encoded full name for the application>&host=<name of QVS>In addition to this URL you can also supply some extra parameters to control which actions will fire when the app is opened. For example the URL below will open the Sales Compass app with the value “Q2” selected in the listbox with id LB5699 (yes we create way to many objects )demo.qlik.com/QvAJAXZfc/opendoc.htm?document=qvdocs%2FSales%20Compass.qvw&host=demo11&select=LB5699,Q2Of course this is only a simple example, in the table below you will find all the available parameters you can append to your URL.Feel free to mix and match these til your hearts content.ActionParameterExampleSelect a single value&select=<Listbox ID>,<Value>&select=LB01,TotalSelect multiple values&select=<Listbox ID,(Value|Value2)&select=LB02,(2011|2012)Open the app on a specific sheet&sheet=<Sheet ID>&sheet=SH01Open the app with a bookmark applied&bookmark=<Bookmark ID>&bookmark=Server\BM01Wait a minute, you mentioned single objects?Ah, yes! When QlikView 11 was launched we also introduced the capability to display a single object from an app.This allowed customers to integrate objects from different applications into a single view in a external system. It is also this screen that powers the small devices client.Substitute opendoc.htm with singleobject.htm and specify the object id you want to display,demo.qlik.com/QvAJAXZfc/singleobject.htm?document=qvdocs%2FSales%20Compass.qvw&host=demo11&object=CH378And voila! You now have a fully interactive single QlikView object!
...View More
.png "Former Employee"){kind=icon}
