Qlik Design Blog

13 Posts authored by: Charles Bannon
Charles Bannon

Coloring a Scatter Plot

Posted by Charles Bannon Apr 25, 2015

Skittle Plot.png

It is known as the Skittles effect. It happens when a developer creates a scatter plot and decides to color the plotted values by the dimension.  This practice not only does nothing to tell the data story, it actually distracts the user from understanding what the data is actually saying.  Have no fear; there are a couple of solutions to this problem. In this blog, I will discuss one: coloring the dimension by an expression.


Using an expression to color your plotted values


Both QlikView and Qlik Sense allow users to color the plotted values using an expression. In Qlik View, this is achieved by going to the expressions tab. Under the expression simply select Background color and enter the color expression.


QlikView Scatter Properties.png


This expression is telling QlikView to color all plotted values greater than zero as green, else color them red.

QlikView Scatter Expression.png


By coloring the plotted values using green and red, I can now identify which shots were made vs missed.

QlikView Scatter BASKETBALL.png


In Qlik Sense, this is accomplished by navigating to the properties panel on the right and selecting Colors and Legends. Switch the radio button off of the default setting of single color. Then select By expression, enter the color expression and make sure to check the box for This expression is a color code.

Qlik Sense Scatter Properties.png

The expression defined here tells Qlik Sense to color all players from Army as gold, else color the players as blue.

Qlik Sense Expression.png


Here is how Qlik Sense will render the chart once the color expression has been defined. I can easily identify the players from Army versus the players from Duke.

Qlik Sense Scatter.png


One word of caution, coloring by expression is best suited for small groups of colors. I would say 3-4 colors maximum. Otherwise you start to create your own controlled Skittles effect. It is also best practice to let the user know what the colors represent. Here I utilized Qlik Sense’s subtitle field to define the colors for the users.


Happy Qliking!

Charles Bannon

Responsive Design Tips

Posted by Charles Bannon Mar 13, 2015

The responsive design of Qlik Sense allows apps to be developed once and deployed anywhere. This makes the developer’s life easier but there are still a couple of things to keep in mind when you are building your app.


Object Positioning – On a small device like a phone, Qlik Sense will redisplay the page by ordering objects in a single column. The order of displayed objects is determined by a top to bottom, left to right fashion. Meaning that the object that sits in the top left corner of the full dashboard will be displayed first, next the object to the right of the first object will be displayed. So if you are going to build out a dashboard, it makes sense to build it horizontally and not vertically.




Chart Titles and Subtitles - Using chart titles and subtitles ensures that objects can be interpreted by the users who are looking at your chart as a single object.


Titles and SubTitles1.png      Titles and SubTitles2.png


Text and Image Objects - I would recommend that you use text and image objects wisely. In small device mode, images and text can look out of place as they shift positions to accommodate the viewing area of a small device.


Text and Image.png       Text and Image bad.png


So if you keep small devices in mind when you are building your app, you can be sure that all users will have the best user experience possible. Happy Qliking!

I am sure everyone has already downloaded Qlik Sense Desktop, so I wanted to help you get comfortable with navigating within an application. Understanding how to get to where you want to go is half of the battle.


Top left navigation

left nav.png

1.   Navigation Menu – This menu allows you to navigate to different areas of Qlik Sense desktop.

          a.    App Overview – Go back to the main screen within the current app and view the sheet layout of the app.

          b.    Data load editor – Create or modify the data load script.

          c.    Data model viewer – See how the tables are structured within the app and preview the data.

          d.    Open hub – Go back to the area where you can see all of the available apps.


2.    Menu – This menu offers options to learn more about Qlik Sense Desktop as well as the option for the Quick data load.

          a.    Quick data load – Allows you add new data to the app or add files to replace the existing data.

          b.    Help – This is where you can find answers to all Qlik Sense related questions.

          c.    About – This will tell you which version of Qlik Sense Desktop you are running.


3.    Save – Saves changes made to the app.


4.   Information about the app – View the app thumbnail, title, last loaded date, file name, and the app description.


5.    Step back – Reverse the last selection made within the app.


6.    Step forward – Reapply the last selection.


7.    Clear all selections – this will remove all current selections.



Top right navigation

right nav.png

8.   Take a snapshot – This allows you to  take a snapshot of the different objects within the app. Snapshots can be used within



9.    Storytelling – This area allows you build a story about your data. Use images, snapshots, objects and text to create a

        compelling story about your data.


10.   Bookmarks – This allows you to create and save different selection states to be used at a later time.


11.    Edit / Done – This button allows you to edit the sheet. Create visualizations, measures, etc. Once you are finished in edit

        mode, select done to see your how your changes respond to user interaction. 


12.   Sheets – This allows the user to move around from sheet to sheet within the app.


13.  Search Tool – Selecting this option will allow you to search your data for a particular value.


14.   Selections Tool – This option allows you to view and select from all of the data fields available as well as view the current



15.    Sheet navigation - Navigate to the next sheet or the previous sheet.


Now that you know where the navigation options are located and what they allow you to do, go ahead start developing your Qlik Sense apps! Happy Qliking!

In my last blog I explained how, using conditional expressions, a developer could enhance the experience of a user.  And based on some feedback, I have decided to continue the discussion of conditional expressions. In this blog, I want to discuss another area within QlikView where a developer can use conditional expressions to his/her advantage.


Using Conditional Expressions to Show/Hide Sheets


There are times when, as developers, we need to tailor the user experience based on a device (i.e. Desktop vs Mobile). In the GPS – Store Finder app on demo.qlik.com, we do just that. Based on the values of conditional expressions on the sheet properties, we can give the user a more desired experience.


The version of the app on the demo site uses a mobiledetect extension that checks to see through which device type the user is accessing the application. It then sets a variable (vStyle) to either Mobile or Desktop.


The app also uses non-traditional navigation by hiding the Tabrow.


Setting the conditional show sheet expression to only show when the variable vStyle=’Mobile’ hides the sheets designed for the Desktop and allows the user to experience the Mobile version of the app.


Mobile Version

This is set up to fit nicely on a mobile phone with a vertical scroll and larger fonts to assist in better navigation.


Conversely, setting the conditional show sheet expression to only show when the variable vStyle=’Desktop’ hides the sheet designed for Mobile and allows the user to experience the Desktop version of the app.


Desktop version

Designed with a more traditional approach with the list boxes on the left and the viewing area set within the 1024x768 screen resolution.


By taking advantage of the conditional expression for a sheet, we were able to customize the user experience and, in essence, create one application to handle multiple client types. Another example of the use of conditional expressions to show/hide sheets based in device is the Insurance Demo which can also be found on demo.qlik.com.

As QlikView developers, one of our main focuses is on the user experience. There are many instances where objects only need to be shown upon the user’s request. An example of this is list boxes (filters). With the use of conditional expressions, a QlikView developer can make the list boxes available when the user needs them.


I have seen too many applications where list boxes take up most of the design area available to the developer. Invariably you get the same response, “It is what the users want”. And while I am sure they “need” all of the list boxes, they just do not need them on the screen the entire time.  So how do you get around this demand for list boxes? Create a “Filters” button and have the user show and hide the list boxes whenever they want.


For this first example, I will reference the Workforce Management demo. On the dashboard below there are no visible list boxes. The lack of list boxes opens up more space to the developer. Instead of making the list boxes available at all times, we have a Filters button which, when invoked, will display the filters over top of the visualizations. Once the user makes the appropriate selections, he/she can close the filter panel and continue with his/her discovery.  This is done by setting a variable and conditionally showing the objects based on the current value of the variable. The beauty of this approach is that the user gets to have the filters that he/she needs and the developer does not lose any display area.


Here you can see the filter button with a conditional show. When invoked, there is an action to change the variable to 1 which makes the filters button disappear and makes the filter objects appear.



All of the filter objects have the same show conditional. When the user selects the close button, the variable is changed back to 0 which makes the filter objects disappear and the Filters button reappear.



This approach can also be used if you need to show/hide help/information pop-ups as shown here in the IT Asset Management demo.




If you would like to see how the filter and the help/information buttons were developed, the QVWs can be downloaded from the demo site. As was stated earlier, the filter example referenced the Workforce Management demo and the Help/Info button referenced the IT Asset Management demo. Happy Qliking!

As QlikView Developers, we are often asked to supply the source data files for applications on the demo.qlik.com site. Since most applications are able to be downloaded locally, I figured I would let you in on a little secret. You can create the data files yourself and it is a fairly simple process. The QlikView scripting statement that will help you achieve your desired results is the STORE statement.


QlikView help states that a QVD or a CSV file can be created by a store statement in the script. The statement will create an explicitly named QVD or CSV file. The statement can only export fields from one logical table. The text values are exported to the CSV file in UTF-8 format. A delimiter can be specified, see Load. The store statement to a CSV file does not support BIFF export.



Store mytable into xyz.qvd (qvd);

Store * from mytable into xyz.qvd;

Store Name, RegNo from mytable into xyz.qvd;

Store Name as a, RegNo as b from mytable into xyz.qvd;

store mytable into myfile.txt (txt);

store * from mytable into myfile.txt (txt);

(The two first examples have identical function.)


So let’s walk through the process.

1.    Open a new QVW and binary load the original app.

2.    Identify the table(s) that you would like to extract. (Table viewer).

3.    In the script you will either have to enter a Store statement for every table that you would like to extract.

Blog Post.png

Or you could use this snippet of script that will extract all tables from the original app

Blog Post2.png


4.    Reload the application.


After the reload, the extracted files will be placed in the same folder as the reloaded QVW. Now go ahead and try it for yourself.

Happy Qlikking!

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 = Red

Need To Call = Yellow

Recent Call = Green

Not Targeted = Grey


The 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!!

Most people who create a QlikView application do so to help analyze their business data. And while business is the main focus when analyzing data, there is an equally important segment of data that gets overlooked and that is personal or recreational data. Many times this data is captured in an Excel spreadsheet and beginner QlikView users might need to know how to load their spreadsheets into QlikView. In this blog I want to run through two scenarios of how I used QlikView to help me analyze some recreational data.


Scenario 1 - In my personal life I am a coach. I coach youth football, basketball and baseball. This past spring, while coaching my son’s 9-10yr old baseball team, I had the grand idea to analyze the team’s game data in QlikView.  So, after each game, I took the data that I captured on our scorebook and entered it into an Excel spreadsheet. Each game had its own tab within the spreadsheet. After entering the data in to Excel, I would then load it into QlikView. Once the data was in QlikView, I created calculations that analyzed the productivity of my hitters. I tracked On-Base %, Batting Average, In-Play% and K/AB%. 


As the season progressed, and I had more game data to analyze, I started using QlikView to create my batting order. My mindset was that my top on-base hitters batted 1-3, my top in-play hitters batted 4-7, and my weaker hitters batted 8-11. It was a youth version of Moneyball and I was the team’s Billy Beane. So what did all of the data crunching get us? Well, while we averaged over 9 runs per game during the season, we did not win the championship. My next order of business is to see how QlikView can help my team improve our fielding.



Scenario 2 – As I mentioned previously, I volunteer my time with youth sports organizations. Many of you know that youth organizations are administered by volunteers, many of whom have full-time jobs and families. So any amount of added help is greatly appreciated. Many of these organizations use Excel spreadsheets to keep track of the finances. Trying to analyze that data takes lots of time and energy from volunteers who are already stretched too thin. This is where QlikView can help. QlikView allows the administrators to visualize expenses and revenues to get a better understanding of the money flow within the organization. I helped one youth organization complete this task and it was a real eye-opener to see just how much money was brought into the organization and where it was all spent.


Those are just two ways that I have utilized QlikView to help me analyze recreational data. And for those of you wondering how I loaded my Excel spreadsheets into QlikView, I wrote a technical paper that will explain the process. 

As a QlikView developer I am often asked to load images into QlikView. In some instances the images are associated to other data fields and in other instances they are to be loaded in to the application to help convey a message. For example, let’s say that you need to bring in flags of countries that are to display when the corresponding country is selected and you need to display an icon that indicates whether sales for a country are above or below a predefined threshold. It sounds like an easy enough task; so how do you do it?


Well, QlikView offers the developer the Bundle function that can be added to the Load statement. Bundle Load statement allows the developer to load the image files directly into the QlikView application for portability.


The Bundle Process is a very simple scripting process. The syntax for the Bundle Load should look like this:



The image file should contain two fields:

  • The first field is an identifier such as an index number, image name, a key field that associates the image back to other data files, etc.
  • The second field should contain the path to the image.


This file contains the CountryID so that I can associate the flags with the countries



This file contains just an image name because I am just using these icons as a reference and they are not associated to any fields in other files.



Once the images are loaded into QlikView we can now reference them using the INFO() function and a standard IF Statement:




I am using the INFO() function to display the flags so whenever a Country is Selected, QlikView will know to go grab the corresponding image based on the CountryID. If we needed to, we could do the same thing with the symbols by simply adding a list box for Image Name and selecting an image from there. For this example though, I am using a standard IF Statement to display the symbol for the country sales.


Here is what it looks like when the user selects a country. The Info() function displays the image associated with CountryID =1 and because Sales were above the threshold the green square is displayed.



One note of caution, when images are loaded into a QlikView application using the Bundle Load statement, both the amount of RAM and the size of the application increase so consider both the size and the amount of image files before deciding to use the Bundle LOAD statement.

I wrote a technical brief that outlines these steps in more detail. You can access it here.


Happy Qliking!

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 Similarities


  • Both 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 Differences


  • Previous() 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!

Do you need to display the top performance person’s name in the dashboard?  For example, you need to display the name of top salesperson.  It is easy to do so with a table displaying the list of sales reps with sales amount, sorted from the highest value, but you just need to display the top sales person’s name in your dashboard. Here is how to do it.


As is almost always the case with QlikView, there is more than one way to accomplish this task. The first way is to use the firstsortedvalue and aggr functions. 


firstsortedvalue( Salesperson, -Aggr(sum([Sales Amt]),Salesperson))  Top Salesperson.png


The expression first calculates the Sum([Sales Amt]) and aggregates it by Salesperson. The firstsortedvalue function then looks at the all the Sales Amt values and, because we are using a minus sign in front of the sort value, displays the Salesperson with the top Sales Amt. 


Another way to accomplish the task is to use the functions Rank, Aggr and Only.


only(if(aggr(Rank(Sum([Sales Amt])),Salesperson)=1,Salesperson))


This approach is a little more complicated but equally as effective. First, the expression takes the Sum([Sales Amt]) and aggregates that by Salesperson. Next, a rank is created based on the aggregated Sales Amt. The expression then takes only the Salesperson with a rank of one and displays it in the textbox.


Really, it is just that easy. I wrote a technical brief that outlines this process in more detail. Along with the technical brief I included a QVW and a small Excel file and a video.  You can access the files here,


Good Luck and Happy Qliking!

Today’s corporate environment is global. QlikView application developers are required to support many regions and many languages. So how does a QlikView application developer handle multiple languages?  Well, that depends on how the data is captured and the overall goal of the application developer. Let’s look at two scenarios.


Scenario 1:  The developer only needs to translate the labels and textboxes within the application and not the actual data.  blog picture 1.png


  1. Create a translation spreadsheet that contains an index column to be used for reference and columns for every language that each label and text entry that needs to be translated.
  2. Fill in the English language column with the text to be translated and have translators fill out their language. 
  3. In the script load the translation spreadsheet and create an inline table that contains all of the language translations. Then create a variable, say vDataLanguage,  that reads the Language field.
  4. Using set analysis, create an expression that references the index number and the language to display

=Only({<Index={12}>} [$(=vDataLanguage)]) 

for all textbox and object captions that need to be translated.   



Scenario 2:  The data from which the QlikView application developer is developing is recorded in English but he/she needs to have some of the actual data translated into other languages.

blog picture 2.png

  1. The first thing that he/she needs to do is determine the fields that need to be translated and create a translation spreadsheet that contains a worksheet for each of the fields to be translated. For example, we need to translate the Industry data. Now we need to load the data translation file into QlikView as a Mapping Load for each language. For this example we need to translate into German and French so we’ll create a mapping file for French_Industry and a mapping file for German_Industry.
  2. Create an inline table that contains all of the language translations.
  3. Create a variable that reads the Language field (vDataLanguage). 
  4. In the script, find the Industry Field that we need to translate.  Using the Applymap function we can translate the English data into the different languages and be sure to name the translated field as Fieldname_Language (e.g. Industry_French) .
  5. We now need to create a variable for each translated field. So for industry we’ll create vIndustry. The expression in vIndustry is  
         =chr(91)&'Industry' &chr(95) & '$(vDataLanguage)' & chr(93) .
  6. Now to get the correct language to display in a list box you need a calculated dimension using this expression,  which is



That’s it! Easy, right? Well, it is a bit time consuming if you are trying to translate data in fields but translating text and labels is really not that difficult. I created a technical overview that walks you through the two scenarios mentioned in this blog as well as a third scenario which can be found here. This resource includes a How-To, sample data files and 2 QVWs.


Good luck with the translations!  Lycka till med översättningarna!  翻訳で頑張ってください!

Recently, I was asked to create an application in which a user would be able to search for videos for the demos on demo.qlik.comVideoPlayer1.png. After reviewing the task at hand, I decided that I would need to incorporate a QlikView extension in order to have the videos play inside the QlikView application. The extension that I chose was the webpageviewer.qar. My dataset included the URLs for the videos. The videos were previously uploaded to YouTube. By uploading the videos to YouTube, it allowed me to embed the videos in the webpage viewer so that only the video would show up in the webpage viewer instead of the entire webpage. 


Another aspect of the application was to give the user the ability to select tags in order to filter the data and find the desired video to play. As a team we decided that, because there were so many tags to display, it would be better to show them as a popup over the other objects on the page. The user could invoke this popup by selecting the “View all video tags” button. The user also had the ability to remove the popup from the screen by selecting the close button.  Easy enough, right? All it entailed was creating a couple of variables, a text box and a list box for tags and layer them all accordingly. This was standard QlikView show/hide. Not so fast my friend! Did you know that the webpage viewer takes layer precedence over all other objects on the screen? That’s right; you cannot layer over top of the webpageviewer because of the flash used for the videos. The webpageviewer always comes to the top. OK, so now what?


I decided that I needed to somehow put a show/hide condition on the webpageviewer and hide it when the popup was selected. Only one problem, the original webpageviewer did not have an area to write a conditional show expression. ARGH! Well since I sit next to the extension guru, I asked him if he could tweak the extension and give me the ability to show/hide the extension. After a couple of minutes of tweaking, I was in business. I was able to hide the video player when the “View all video tags” button was selected by creating a variable named vShowTags. When the “View all video tags” button is selected, there is an action that sets the vShowTags variable to YES. When that happens, the webpageviewer extension is hidden. And when the user selects to close the popup another action is triggered that sets the vShowTags variable to NO which allows the webpageviewer extension to show on the page.

There are many ways to accomplish desired tasks in QlikView. The challenge is sorting through all of your options until you find the best solution. Click here for the zip file that contains the QVW, Technical Brief and the extension object used in the creation of the Video Player demo. You can also see the Video Player demo on demo.qlik.com Happy Qliking!

Filter Blog

By date:
By tag: