2012

How normalized should the QlikView data model be? To what extent should you have the data in several tables so that you avoid having the same information expressed on multiple rows?

 

Usually as much as possible. The more normalized, the better. A normalized data model is easier to manage and minimizes the risk of incorrect calculations.

 

This said, there are occasions where you need to de-normalize. A common case is when the source database contains a generic master table, i.e. a master table that is used for several purposes. For example: you have a common lookup table for customers, suppliers, and shippers. Or you have a master calendar table that is used for several different date fields, e.g. order date and shipping date (see image below).

 

DB Relations.jpg

 

A typical sign for this situation is that the primary key of the master table links to several foreign keys, sometimes in different parts of the data model. The OrganizationID links to both CustomerID and ShipperID and the Date field links to both OrderDate and ShippingDate. The master table has several roles.

 

The necessary de-normalization in QlikView is easy. You should simply load the master table several times using different field names, once for every role. (See image below).

 

QV Relations.jpg

 

However, loading the same data twice is something many database professionals are reluctant to do; they think that it creates an unnecessary redundancy of data and hence is a bad solution. So they sometimes seek a solution where they can use a generic master table also in the QlikView data model. This is especially true for the master calendar table.

 

If you belong to this group, I can tell you that loading the same table several times is not a bad solution.  Au contraire – in my opinion it is the best solution. Here's why:

  1. From the user’s perspective it is confusing to have an unspecified “Date” field if there are several dates in the data model. For example, the user will not understand whether the date refers to order date or shipping date.
  2. Without loading the master calendar several times, it will not be possible for the user to make selections that place simultaneous restrictions on several date fields, e.g. “show transactions where the order was placed in September and the items were shipped in November”.

 

In fact, loading the same table several times in QlikView is no stranger than doing it in SELECT statements using aliases, e.g.,

SELECT OrderID FROM Orders
INNER JOIN MasterCalendar AS OrderCalendar ON Orders.OrderDate=OrderCalendar.Date
INNER JOIN MasterCalendar AS ShippingCalendar ON Orders.ShippingDate=ShippingCalendar.Date
WHERE OrderCalendar.Month=9 AND ShippingCalendar.Month=11

In SQL you would never try to solve such a problem without joining the master table twice. And you should do the same in QlikView.

 

So, if you have several dates in your data model – load the master calendar several times!

 

HIC

 

PS. But if you still want one common date field, you should create a Canonical Date.

One of the common business requirements when analyzing the data is limiting the dimension values on a chart. For instance, limiting the chart to the top 10 sales people, or showing only the products that make up 80% of sales. QlikView 11 dimension limits functionality enables the business users to easily create these logics on the charts.

Dimension Limit.PNG

This functionality provides consistency across QlikView charts and more importantly better performance. Prior to QlikView 11, some charts in QlikView could display totals at the expression level, some can limit the number of dimension values to display and some can display an ‘OTHERS’ dimension value to catch those values omitted by the limits. With QlikView 11 dimension limits feature, these functionalities are consistent across chart types. Also, the conditions that are used on the dimension limits are calculated at the engine level, providing better performance and calculation time.

The functionality offers four main options:

  • Limits: This option restricts the dimension values displayed on a chart based on flexible criteria. The criteria are evaluated with the first expression of the chart. It is possible to restrict the dimension values by the largest, smallest, first, greater than or less than an exact value or relative the total.
  • Global Grouping Mode:  With this option, it is possible to treat the dimension values as either ‘local’ to the current upper dimension level in the chart, or ‘global’ to be evaluated across all instance of the upper level dimensions. This option is only applicable to the second or lower level dimensions on a chart.
  • Show Total: This option enables the display of subtotals at the dimension levels.
  • Show Others: This option displays an ‘OTHERS’ dimension value to catch those values omitted by the limits.

Each option has a separate set of applications and can be used in different permutations.   

QlikView 11 dimension limits functionality makes it easier for the business users to customize the QlikView apps based on their own analysis needs with just a few clicks!

Why it is ok to scroll

Posted by Michael Anthony Aug 14, 2012

A popular topic in interactive design is that of scrolling. When is it ok to scroll? Is it ok to scroll? Will people know they can scroll? What information should be above the fold?

 

Above the fold

When web design began the designers were mostly trained print designers since interactive design was a new field and "web" designers didn't exist. The concept of "the fold" is one that print designers imported from newspaper design. The goal of designing "above the fold" was to keep the most important headlines and images in the top half of the newspaper so when it was folded in half at the newsstand the most enticing information would be visible and passers-by would stop and buy the paper.

 

With the internet the concept of designing web pages with the important information above the fold, keeping key information viewable without scrolling vertically, was met with a new challenge: monitor variety. When a publisher produces a newspaper every customer gets the same sized paper. The content that is above the fold for one reader is the same for every reader. With web design the variety of monitors, browser chromes, and resolutions is so diverse that there is no standard height for where the fold begins. The fold on my phone is alot different than the fold on my 27inch desktop monitor. The ipad has not one but two folds: one in portrait orientation but a shorter fold in landscape orientation.

 

Ignore the fold

People's fear is that important information will never be found if it is below the fold. That somehow people (not themselves of course) don't know how or when to scroll to find additional information so the solution must be to cram as much content at the top of a page as possible. This is ridiculous. Usability tests continue to prove that not only do users scroll but scrolling can actually improve the user experience. If people feel they are on the right track they will continue to scroll for content.

 

Advice:

  • Design pages that are legible and attractive. If your application is well designed it encourages people to explore the document and they will scroll on their own.
  • Vertical scrolling only. While users can scroll vertically as well as horizontally, generally speaking a page should do one or the other and people prefer vertical scrolling. Vertical scrolling is a more standard method of navigating content as well as the simple fact that most scroll wheels move vertically and not horizontally. Vertical scrolling is just easier.
  • Monitor Resolution. To avoid horizontal scrolling you should know your intended audience. I use analytical data to find the largest number of users with the lowest common resolution and design for that. 1024x768 is still a decent standard size as well as being the resolution of the ipad in landscape orientation.

Of the 2 ½ years I have been with QlikTech, I have never had to use the IntervalMatch prefix in any of my scripts but I recently found out how powerful it can be.  I was tasked with creating a Profit and Loss (P&L) statement in QlikView based on the format outlined in this Excel file.

 

PandL in Excel 2.png

 

  • The “s” in the Exec P&L Level column indicates blank rows
  • The “c” in the Exec P&L Calculation column indicates rows where a calculation needs to be performed
  • The Exec P&L Heading column stores the headings that are to be used in the P&L statement
  • The ExecPLStart and ExecPLEnd columns indicate the range of rows that are to be summed

 

Who would have thought something as simple as IntervalMatch would solve my problem?  I have always said that I learn something new about QlikView every day and this day was no different.  I was able to load my master account information, my account balance information and then perform the IntervalMatch, using the reporting code field from the master account table, to get everything in sync so that the correct rows were summed in the P&L statement.  Then all I needed to do was create my chart (a straight table) in QlikView.  In the end my chart looked like the image below and I was quite pleased.

 

pl large.jpg

 

I wrote a technical brief about how I completed this task.  I know this is just one of the many ways IntervalMatch can be used.  I am sure in the future I will learn some of the other ways.

Henric Cronström

The Only Function

Posted by Henric Cronström Aug 3, 2012

There is a little known function in QlikView that hardly anyone uses and that doesn’t do very much, but still has a tremendous impact on many of the calculations made in QlikView.

 

It is the Only() function.

 

It returns the value of a parameter – but only if there is just one possible value. Hence, if you have a one-to-one relationship between the chart dimension and the parameter, the Only() function returns the only possible value back. But if there are several values, it returns NULL.

 

Entity model.png

 

The Only() function is an aggregation function, which means that it uses many records as input and returns one value only. The Sum() and Count() functions are examples of other aggregation functions. Aggregations are used whenever you have a group of records and need to show only one value representing all records.

 

When you think about it, QlikView uses aggregations in virtually all calculations: The expression in a chart, in a sort expression, in a text box, in an advanced search and in a calculated label are all aggregations and cannot be calculated without involving an aggregation function.

 

But what if the user enters an expression that lacks an explicit aggregation function? What does QlikView do then? For example, if the sort expression is set to “Date”? Or if there is an advanced search for customers using the expression “=Product='Shoe' ” (the intent is to find customers that have bought this product)?

 

This is where the Only() function affects the calculation without the user knowing it; if there is no explicit aggregation function in the expression, QlikView uses the Only() function implicitly. Hence, in the above cases, “Only(Date)” is used as sort expression and “=Only(Product)='Shoe' ” is used as search criterion.

 

Sometimes the new expression returns a result that the user does not expect. Both the above examples will work fine for cases when there is only one possible value of Date or Product, but neither of them will work for cases when there is more than one value.

 

Therefore, when you write expressions you should always ask yourself which aggregation you want to use, or: Which value do you want to use if there are several values? If the answer is that you want to use NULL to represent several values, then you indeed want to use the Only() function and you can leave the expression as it is.

 

But if you do not know the answer, then you should probably think again. For numbers, you probably want to use Sum(), Avg() or Min() instead and for strings you may want to use Only() or MinString(). For debugging you can always use something like, “Concat(distinct <Field>, ',')” and analyze the result.

 

But you should not leave your expression without an aggregation function.

 

HIC

 

Further reading related to this topic:

It’s all Aggregations

Use Aggregation Functions!

Filter Blog

By date:
By tag: