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.