When UX professionals get together to redesign a service or product a common practice they will employ will be the use of personas. Personas are representations of different kinds of people / users of the thing you are redesigning. They are the archetypal kinds of people using the thing you are looking to improve. They are the novices, the savvy experts, the casual users, the die-hard fans, the skeptics, etc. You give them names, you describe what their current needs are, how they use the product today, as well as their expectations for the future.While you can't please all the people all the time, this exercise forces you to account for a variety of users and to remember that you are designing for a variety of needs and expectations and not just your own. It pushes you to step outside of yourself. Personas have the added benefit of putting a face to a series of requirements. It helps everyone involved keep organized by having a name to go with a list of features & requirements.So where to begin? You can start a few ways. If you have the luxury of time & access you can interview stakeholders in the project from a variety of levels. From the hands-off to the hands-on users, from the executives down, you will gain insights from people all along the way. If you don't have that sort of time, brainstorm the kinds of users you are designing for. What will the executive level user want or do with your application? What about the person who is doing the day-to-day maintenance of the app? Create lists of the kinds of users, how they are doing things today, what they want in a future design, what their roll will be, their level of technical sophistication, etc. From here you can begin to design for a variety of consumers.A word of caution.Personas aren't real people. It seems obvious but it is worth mentioning. Personas are a great way to get organized and remember to account for a variety of users but they aren't the same as actually testing your design with real people. You should still validate your ideas with a variety of people and see what's working and what isn't. A persona will guide you to a solution but you still need to confirm, with real people, if that solution works. ...View More

Last week I wrote about how to create reference dates for exchange rates and warehouse balances: How to populate a sparsely populated field. There is however also a second case where you want to create reference dates, but the data model is quite different. It is when you have a list of contracts with validity periods: Rental contracts, Insurances policies, Healthcare commitments, etc. Each contract has a begin day and an end day. The analysts of an insurance company would probably want to ask the question: “How many valid insurance policies did we have on this specific day?” In other words, he wants to click on a reference date to see the count of policies that are associated with this date – even though this date doesn't exist in the source data. The solution is to first load all policies in one table. Then load a second table that has one record per policy and date (Policies_x_Dates). This second table can in turn link to a master calendar. But the middle table does not exist in the source database. Instead, you must generate it using a While loop that loops over each record in the Policies table, i.e. each source record will be loaded several times. This way, you can load not only the “From” date and the “To” date, but also all dates in between: Policies: Load PolicyID, BirthDate, PolicyAmount, FromDate, ToDate, OtherPolicyAttribute From Policies; Policies_x_Dates: Load PolicyID, Age( FromDate + IterNo() – 1, BirthDate ) as Age, PolicyAmount / (ToDate - FromDate + 1) as DailyAmount, Date( FromDate + IterNo() – 1 ) as ReferenceDate Resident Policies While IterNo() <= ToDate - FromDate + 1 ; Note that the Policies table has exactly one record per insurance policy, and the newly created Policies_x_Dates table has exactly one record per combination of policy and date. Note also that there are other fields that should be put in the Policies_x_Dates table, e.g., the age of the insured person, since this depends on the reference date. Further, it is possible to break up a cost or an income into daily amounts, which is useful when you want to show the correct amount distributed over the year. The While loop is a very useful tool whenever you need to create additional tables in the data model. It is often a better option than the IntervalMatch. Read more in Generating Missing Data In QlikView. HIC ...View More

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)) 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! ...View More

Sometimes when you load data into QlikView you find that a field is sparsely populated, i.e. it has discrete enumerable values where some values are missing. It could be like in the table to the right where you have three dates, each with some kind of conversion rate. The table only contains the dates where the conversion rate changed, not the dates between the changes. However, the user will want to ask the question: “What was the status on this specific day?” In other words, the user wants to be able to click on a reference date to see the number that is associated with this date – but the date might not exist in the source data. In such a situation, you need to generate the missing dates between the changes as individual records and use the “Rate” value from the previous date. There are several ways to do this in QlikView, and all of them involve some script programming, using temporary tables. One algorithm is Load the source table containing the rates (below called “Rates”). Find largest and smallest date in the “Rates” table. Generate all dates between the largest and smallest dates (below called “Dates”).. Join the “Dates” table (outer join) onto the “Rates” table. Sort the resulting table according to date. Propagate the value of “Rate” downwards to all records that have NULL in the “Rate” field, using the Peek() function. Visually, the join and peek steps of the algorithm look like this: In the QlikView script, the algorithm would look like the following: TempTable_Rates: Load Date, Rate From Rates ; MinMaxDate: Load Min (Date ) as MinDate , Max (Date ) as MaxDate resident TempTable_Rates; Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1; Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ; Join (TempTable_Rates) Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate; Rates: NoConcatenate Load Date, If( IsNull( Rate ), Peek( Rate ), Rate ) as Rate Resident TempTable_Rates Order By Date ; /* so that above values can be propagated downwards */ Drop Table MinMaxDate, TempTable_Rates; Problem solved! This method can be adapted for most situations when you need to insert additional records in existing data: Warehouse balances, Exchange rates, etc. See more in the Technical Brief: Generating Missing Data In QlikView. HIC ...View More

A few weeks ago a former colleague, who has recently started to work with QlikView, contacted me and was wondering how to start with QlikView and how to help his team to better visualize data, here are some tips I shared with him that could help you to start too: 1 Find inspiration and reuseMake sure you visit QlikView Demo site prior to starting your project. One of the best things about the site is the fact that you are able to download one app and reuse the content for your own. Don’t forget to check the brand new demo image gallery with visualization highlights and samples of QlikView’s unique approach to data viz.If you need advice on when to use which chart, take a look at Data Visualization App, it has specific information and best practices on how to show your data in the most effective ways.2 Identify the needs You need to understand the business process that is behind the analysis first. The more you know about the business needs the more easily you will be able to design for the business by choosing the appropriate KPIs. So your first task is to identify the KPIs for your new dashboard and assign them a relative weight so later on you will be able to use hierarchy to emphasize the heaviest, most important ones - this is key. 3 Use pen and paper To create a QlikView app start by picking a piece of paper and your favorite pencil and drawing your best approach to the dashboard, you don’t need to be very specific, just sketch it out and create the basic layout to emphasize the main KPIs and comparisons that are need in your project. Don’t forget to validate your design with other users and add their feedback to your design. 4 Choose a leitmotiv Think of the fashion designers. They base their collections on a certain decade or style and then they are consistent in applying that style to the entire collection. When working with QlikView you can be a fashion designer in a way, choose a theme, your leitmotiv for the app, and keep it in mind when creating charts and when choosing the color palette. Despite using your corporate branding, it could be a mess if you mix several colors, just keep neutral colors for backgrounds and main elements and use contrast to emphasize the most important KPIs. Some good examples of thematic design could be founded in QlikView Developer Toolkit App, an excellent starting point for your next QlikView App, you will also find many design elements as buttons, grids, shadows and rulers and lines. These are just some basic tips to start working with QlikView but if you want to go further in the design process to developing QlikView applications, then you should review Michael’s technical papers you will find at the bottom of his post called How To Improve Your Design Process Working With Qlikview.Enjoy Qliking! Arturo Muñoz ...View More

In a previous blog post I described the internal data tables and the symbol tables. (See “Symbol Tables and Bit-Stuffed Pointers”). These tables constitute the QlikView internal data model. Then there are the state space vectors that keep track of the user’s selections (See “Colors, states and state vectors”).In addition to these, there are other structures used to calculate the sheet objects. Sometimes a sheet object can use quite a lot of memory, e.g., a chart with many dimensions.Often you need to ask yourself – “What in this application uses a lot of memory? What can I improve or optimize?” Is it the data model itself or is it the symbol tables? Or is there a chart that uses a lot of memory?”To get an answer to these questions, you can use the memory statistics tool. Here follows a basic recipe for a memory analysis:Create a memory statistics file from the application you want to analyze (Document Properties -> General -> Memory Statistics). This will export some memory statistics data to a tab separated file.Create a new QlikView document in which you load the created file.Create list boxes for the fields Class, Type, and Subtype.To understand what these fields display, see the table below.The Class field tells you whether the memory used is part of the internal database (data tables and symbol tables), the state space (the selections), the sheet objects (volatile structures to calculate the sheet objects), or a variable. The Type and SubType fields give you additional information about where memory is used.Create a drill-down group of the fields Class, Type, and Subtype named ">Type".Create a stacked bar chart with Id as first dimension, >Type as second dimension, and Sum(Bytes) as expression.Sort the chart descending according to y-value.Restrict the chart to show only the first 10 bars.You should now have a graph similar to the one below.In this you can see which objects, tables, or fields are consuming memory in your application. The bars to the left are the ones that use a lot.Now you can start to optimize!If most of your memory consumption is made by sheet objects, look at them and see if there is anything you can do. Does this chart have too many dimensions? Is the expression too complex? Do I really need to sort this list box with a very large number of distinct values? Do I need to show it at all?If most of your memory consumption is due to database symbols, look at whether you can use the autonumber function to make these symbol tables use less memory.If most of your memory consumption is made by database tables, you might want to remove columns, or aggregate data. Or maybe realize that optimization isn’t worth wile…One small word of warning: The numbers in the memory statistics analysis should not be trusted too literally – they do not always describe the situation completely. For instance, if a calculation is cached, it will not show up in the memory usage. Further, if an object has not been opened yet, its memory consumption is unknown and will display as zero. But in spite of these shortcomings, the memory statistics function will most of the time help you find where memory is used.HIC ...View More

Bar charts, pie charts, Speedometer gauges, Traffic Light gauges – These are some of the visualization objects that come to the mind when one think’s of designing a Dashboard to show KPIs. Some are used for their grandeur and others simply because they show the data very clearly. Now, let’s take a step back and rethink on what is the main purpose of Visualization objects within the Dashboard. When there is a large amount of data, it becomes difficult to scan through it in the form of a table and recognize a pattern or select that data which is useful to make sense of situation. This is when visualization objects help the user understand the data clearly in a quick and easy way and enable recognition of the underlying patterns by giving out the big picture and pointing precisely to deviations, outliers and connections.. So, if we take some of the charts that we frequently use in our dashboards and analyze them for their intuitiveness, the answer might not always be positive.Some visualization objects that have proven to be intuitive in showing the data clearly are sometimes embellished with ornate presentation techniques that compromise the ability of the data visualization to focus on the data itself, while other visualization objects commonly used are not intuitive at all in the first place. The Speedometer Gauge is a classic example of a visualization object that is used very frequently in Dashboards, the use of which can be arguable. The speedometer Gauge is drawn as a metaphor in the BI industry from the dashboard of a car. In the dashboard of the car, the speedometer does absolute justice in showing the current state. The driver is only required to know the current situation at any given time. Thus, the speedometer solves the main purpose. In a Business Dashboard however, the user more than often times needs to know a whole lot of other things which support the current state like historical trends and other things for purpose of comparison. In which case, the speedometer gauge, which can show only one data point, fails to show the complete picture. On the other hand, the most simple and extremely popular objects like bar charts and pie charts display the data in a highly intuitive way. They are easy to understand and can inform the user about the patterns and trends. However, if these intuitive charts are not presented well, they can hamper the user’s ability to quickly grasp information and sometimes even mislead the user. As QlikView application Designers, we are always thinking of ways in which we can represent the data in the most simple and intuitive form for our users. As a result we sought to various resources for references and ideas and often times we come across snazzy looking displays, but it might be of great help to take a step back and analyze whether the representation of the data that we put across is easily understandable by the user or not. A detailed description with examples of this excerpt can be found in the technical brief here. ...View More

RecapIn my last article Metadata Management the Customer’s Way – Part 1, I covered a customer’s specific metadata challenge and proposed a QlikView Expressor (QVE) solution to address it. To summarize, our customer needed to know which existing QlikView applications (.qvw) were in compliance with the newly established rules appointed by the organization’s Business Intelligence Competency Center. This article and companion video (below) will provide some details about the proposed solution along with QlikView and QlikView Expressor samples. (attached in this post)We know from the previous article that the customer’s metadata, such as column labels and validation flags, are stored within database tables as part of their metadata management application. For simplicity I will use an Excel spreadsheet to simulate the customer’s metadata repository and focus on the two fields “ColumnName” and “Validated”.Leveraging the QlikView Governance DashboardI've implemented this solution using data files produced by the QlikView Governance Dashboard (QVGD) scanning process and a QlikView Expressor Dataflow. The QVGD working data files (*.qvx) contain QlikView deployment metadata such as column labels, expressions and field names. Typically, this metadata creates the associative data model read by the QVGD’s information sheets. Leveraging these data files with QVE’s QlikView Read operator provides significant advantages when developing custom QlikView Metadata Management solutions such as this. Since we already have the data files from the QVGD scan, there’s no need to create a custom program to extract QlikView label metadata from the QlikView applications.The QlikView Expressor SolutionUsing the QlikView Expressor Design Studio a multi-step Dataflow can be created that will: Extract and load the custom metadata into a QVE Lookup table for later processing Read and join the appropriate QVGD data files on their appropriate keys “Lookup” what labels have been validated or not Provide appropriate business terms to all validated, non-validated and missing labelsCapture the labels that are not in the custom metadata repository and write them back to the repository for later approvalWrite the results to a new QlikView data file to be used for analysis in QlikView Fig. 1 - QlikView Expressor Dataflow that prepares the data for QlikView analysis and updates the metadata repositoryFig. 2 - Simple QlikView application used to analyze the compliance of the lables used in existing QlikView applicationsReview the below video (full screen 480p or higher) to learn more and see the solution in action:If the video does not display - please use this link: http://www.youtube.com/watch?v=IADq2IT1U6Y Regards,Michael TaralloSenior Product Marketing ManagerQlikView and QlikView Expressor@mtarallo ...View More

When creating a chart in QlikView that has multiple expressions, a legend is created for each expression. Sometimes, I find that the legend takes up too much space and I spend time trying to move it or adjust the wording in my expression labels so that they are not too long just so I can show my chart and gain as much real estate on my sheet as possible. Now I have the option to turn the expression legends off but without them you will have no idea what the chart is showing. So I explored other ways to show a legend without cluttering my sheet. In the Social Media Data Analysis demo, I had several visualizations that compared six companies. If I used the expression legend in the line chart and the bar chart (Tweets over time section), it would have taken up a lot of space and been a little redundant as seen in the image below.So instead, I added a silent legend to the Company List Box by adding Text Objects that corresponded to each company. I set the style of the List Box to LED so that the companies were always listed in the same order and I added actions to the Text Objects so that users could make selections using either the company name or color. This allowed me to remove the expression legends in my line and bar chart enabling me to show more of the data and it gave the charts a cleaner appearance.There are other examples of silent legends in the Asset Management and Help Desk Management demos. In the Asset Management demo, the legend is defined in the asset class headings and coloring is reflected in the pie and bar charts.The Help Desk Management demo uses the List Box as the legend and the respective colors in a straight table versus using the text. This makes the various priorities easier to identify in the list.I have written a technical brief on how a silent legend was created in these three demos. Check it out. ...View More

The color coding – Green, White, and Gray – is the hallmark of QlikView. These are the colors that convey information to the user about which field vales are selected, which are possible and which are not possible. These are the states. If you think about it for a while, you will realize that there are two different states for each field value: One is the input state; the selection that the user has made – whether the field value is selected or not; and the other is the output state: whether the field value is possible or not, given the logical inference of the selection. Two statuses, each with two possibilities. This makes four combinations: Selected possible, Selected excluded, optional and excluded. Hence: There are not just three states – there are four. “Selected excluded?” you may ask. “How can a value be selected and excluded at the same time?” It’s simple. It can first be selected, and then excluded by a selection in another field. An example: Let’s say that you have a sales application and you select Jan, Feb and Mar to get the sales for the first quarter. Then you make a second selection – a product that incidentally was sold just in March. This second selection will then of course exclude Jan and Feb from the possible Month values. Jan and Feb will be selected excluded. The field states are stored in vectors; binary arrays that have the same number of bits as the symbol tables excluding NULL values; the same number of bits as the number of distinct values of a field. There is in fact also a third field state vector that keeps track of alternative field values: the field values that would be possible, had there not been a selection in the same field. The blue color is sometimes used in QlikView to show whether a field is locked or not. But note that this is not a state – it is a flag for the entire field, and has thus nothing to do with the individual field values. Finally, there are state vectors for the binary data tables also - vectors that keep track of which records in the data that are possible and which are excluded. All these vectors are referred to as the state space. The vectors are updated at every selection and used every time QlikView evaluates which symbols to show in an object and which record to include in the calculation. One state space per user and alternate state is created. This way, the state space vectors keep track of which data is relevant right now – they “remember” the user selection. HIC PS. All of the above is of course true for both QlikView and Qlik Sense. Both use the same engine. If you want to read more about QlikView internals, see Symbol Tables and Bit-Stuffed Pointers Logical Inference and Aggregations The Calculation Engine ...View More