topic Combining Sets in Set Analysis in QlikView

Combining Sets in Set Analysis

Tue, 16 Nov 2010 10:42:32 GMT

Hello!

I am new to QlikView and struggle with using sets. I am using QlikView to analyze data from our Student Admin System. One table keep track of at what time the students graduate, it looks like this:

%StudentId	%StudentStatId	Programme	Year	Term	Status	Graduate
1	1-AA-2010-1	AA	2010	1	1	0
2	2-AA-2010-1	AA	2010	1	1	1
3	3-AA-2010-1	AA	2010	1	1	0
1	1-AA-2010-2	AA	2010	2	1	0
4	4-AA-2010-2	AA	2010	2	1

Status=1 indicates that the student has registered that year and term.

Graduate=1 indicates that the student has graduated that year and term.

Now I try to find out how many students I loose from one term to another. I am looking for the students that were registered 2010 term 1, minus those who graduated the same year and term. In the example above that would be two students (student 1 and 3). Of those students, I find only one in term 2, meaning I have lost student 3 (the student was active in semester 1 and he/she has not graduated, so I would expect the student to register in term 2, but he/she did not, so I lost him/her)

I tried to define a set consisting of the registered students in 2010 term 1 minus those who graduated the same year and term, and another set consisting of those who registered in 2010 term 2. Finally I made an intersection between the two sets, but for some reason this do not work.

Can anyone tell me how I can get a solution to my problem?

Best regards

Paul Johansen

Combining Sets in Set Analysis

hdonald — Fri, 19 Nov 2010 19:35:29 GMT

Hi,

This looks a bit like the 'lost customer' issue, good solutions for which can be found on several posts in the forum,

such as http://community.qlik.com/forums/t/31112.aspx

Adapting the code from 'What's New in Qv9.qvw' for a straight table expression for your data may get the result you're after.

if( Sum( {$< Term={$(=Max(Term)-1)}, %StudentId=P({<Term={$(=Max(Term)-1)},Graduate={0}>}) >} Status) > 0

and Sum( {$< Term={$(=Max(Term))}, %StudentId=P({<Term={$(=Max(Term)-1)}>}) >} Status)<= 0 ,'Missing',

if( Sum( {$< Term={$(=Max(Term)-1)}, %StudentId=P({<Term={$(=Max(Term)-1)},Graduate={0}>}) >} Status) > 0

and Sum( {$< Term={$(=Max(Term))}, %StudentId=P({<Term={$(=Max(Term)-1)}>}) >} Status)> 0 ,'Continued',

if( Sum( {$< Term={$(=Max(Term)-1)}, %StudentId=P({<Term={$(=Max(Term)-1)},Graduate={0}>}) >} Status) = 0

and Sum( {$< Term={$(=Max(Term))}, %StudentId=P({<Term={$(=Max(Term))}>}) >} Status)> 0 ,'New Start','Left')))

The element function P( ) returns a list of students that registered last term, which can be combined with a restriction on the current term to generate a value used in a conditional statement.

Other solutions include flagging missing students/customers in the script.

A mini-chart using the 'Term' dimension might also be a handy attendance record - if you summed the 'Status' and 'Graduate' Count

you'd get a bigger bar if the student graduated in a given term e.g.

If you've got v10 you could maybe add the mini chart to a list box of student ID and not bother with the set analysis at all,

Regards,

Combining Sets in Set Analysis

Mon, 22 Nov 2010 09:04:17 GMT

Thank you for a very good explanation! This really helped me a lot.

Best Regards

Paul