Thursday, 5 March 2015

What is Qlikview?

In short it’s an application that is capable of reporting and projecting information in a manner that can be deemed meaningful and appealing.

The major benefits of Qlikview are:
  • Ability to compile data from multiple databases (SQL, Oracle, excel) and combine it in to a single logical view
  • Drilling down to detail level information has never been easier. Qlikview’s ability to create associations with the data fields in the databases is quite an amazing feature which is helpful more often than not.
  • Graphical representation along with tabular information helps you to customize the information the way you want to.


Qlikview is a very powerful reporting tool. With all its customization features the ability to create effective reports is a lot easier.

How does it work?
Like any development environment, Qlikview also has its own IDE (Integrated Development Environment) for developers to work with. You can download a personal edition free of charge and develop any application as there are no restrictions in terms of the features provided. However, any application you develop using a personal edition will only work with that specific machine the report was developed.

To work with Qlikview, you need to first select the data source. This can be a connection to a database or a simple excel or CSV file. For ease of explanation, I'll select a sample data source which is bundled with Qlikview installation.

We then need to reload the document. Reloading is similar to execute or run command you would find in a typical IDE. At this point, Qlikview will fetch the data from the data source and create a repository within so that you no longer will be working off the data source but only the cached copy.

Can anyone do Qlikview?
Yes! But you would be able to make more progress if you have knowledge of SQL as you need to extract the required data from the data sources. You can find plenty of resources to start your way to building applications.



Friday, 20 February 2015

Qlikview Indirect Set Analysis

Hi All,

I'm no pro in Qlikview but having read a few articles and understanding the concept I thought of sharing my knowledge with those who may be looking for clarity when it comes to Indirect Set Analysis.

Firstly there are two types of indirect set analysis

  1. Possible value - p()
  2. Exclusion value - e()
In simple terms, p() function returns a set of records which will include the specified value(s) in the set expression. Bear in mind that it will not return the rows that only meet the criteria specified
Conversely the e() function will return the records that does not match the specified criteria. 

The functionalities could be better explained with a set of data which will make grasping the concept easier:


Here is the source data I've used to demonstrate the examples:
This is a list of students with marks obtained for each subject and also a field to track whether it has been a repeat or not.


1. Possible Value - p()

We are going to find out the students that have at least one repeat in their course along with the average. 

We will show the results in a table and in the expressions tab the following syntax will have to be specified:

avg({<Student = p({<Repeat = {'Y'}>}Student)>}Marks)

This means Qlikview will evaluate the p({<Repeat = {'Y'}>}Student) and this will filter the records with the field value 'Y'. As we specify the student name that will be retrieved and then used to calculate the average for each student.

This table will yield the following result:

As there are repeat exams for Mike and Sean, it would only be their names that appear in the list along with the respective average.

2. Exclusion Value - e() 

Suppose we need to find out the list of students who have passed all their exams first time for purposes of having two different certificates for the non-repeaters :-) (Let's hope not).

As before we will show the results in the table and in the expressions this time we will use the e() function. What we intend on doing is to exclude all students who have a repeat and list out those who have a successful first-time pass.

Add the following syntax to the expressions tab in the table:

Avg({<Student = e({<Repeat = {'Y'}>}Student)>}Marks)

Very similar to the previous version but the substitution of e() for p() now retrieves a whole different result set.

This expression when evaluated will look as follows:



As mentioned before this was after learning how the logic works and with the thought of helping those who may need some form of clarity I have explained in the simplest of terms possible.

All the best,
Gayan.