QlikView Design Blog

10 Posts authored by: Charles Bannon

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.

ShowFilters1.png

ShowFiltersScreenshot1.png

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.

ShowFilters2.png

ShowFiltersScreenshot2.png

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.

InfoScreenshot1.png

 

InfoScreenshot2.png

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.qlikview.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.

 

Examples:

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.qlikview.com.

 

Scenario 1 – Coloring the background in a table based on an expression result using RGB()

PharmaSales1.png

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.

PharmaSales4.png

 

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.

PharmaSales5.png

 

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.

PharmaSales6.png

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.

PharmaSales7.png

   

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:

image1.png

 

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

image2.png

 

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.

image3.png

 

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

image3a.png

 

 

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.

image4.png

 

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

            =$(vIndustry).

 

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.qlikview.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?

VideoPlayer2.png

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.qlikview.com Happy Qliking!

Filter Blog

By date:
By tag: