Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

anni_zhang
New Contributor II

Retention Summary in Pivot table: Ignore one dimension or Use a Dimension in Set analysis

Hi Everyone,

I am building a student retention summary table. The data structure is like below, it shows the student ID, starting year and registered year (please assume the data structure is like this and cannot change)

StudentID    Starting Year Registered Year
120032003
120032004
120032006
120032007
220032003
220032005
220032006
220032007
320032003
320032004
320032005
320032006
420042004
420042005
420042006
520042004
520042006
520042007

   

So what I need is to build a table summarize the retention of each starting year.

Starting YearEnrollmentRetained Students after 1 year (this year could change, could be 2, 3 ,4 years,etc.)
20033 (count (studentID))

2 (has problem)

20042 (count (studentID))1 (has problem)

In this case, I have problem calculating retained students, I tried below formulas:

1. If([registered year]=[starting year]+1, count(studentID))

It doesn't work. I check the formula, it seems this logic ([registered year]=[starting year]+1) is always false.

2. count({$< [Registered year]={$(=[starting year]+1)}, [starting year]=>} studentID)

It doesn't work. The results are 0. Because I didn't make any selection on [Starting year], if I made selection on starting year , it will be calculated correctly. But I don't want to make any selections as it is a summary table.


So I tried another way by adding a Retained Year column, Retained year is Registered Year = Starting Year +1 (this 1 could change, it could be 2,3,4 years.)


Starting Year

Retained Year

EnrollmentRetained Students after 1 year (this year could change, could be 2, 3 ,4 years,etc..)
200320043 (has problem)

2 (count (studentID))

200420052 (has problem)1 (count (studentID))

in which Retained Year = Starting Year +1

So now the problem is the Enrollment.

I tried this:

count ( total<[starting year]> studentID)

or

count ({$< [Registered year]=>} total<[starting year]> studentID)

or

count ({$< [Registered year]=>} studentID)

None of them work. the results are the same as Retained Students.

Is there anyone could help please? Any suggestions or guidance are welcome. i've been trying for a couple of days but no luck yet!

Regards,

Anni

1 Reply
anni_zhang
New Contributor II

Re: Retention Summary in Pivot table: Ignore one dimension or Use a Dimension in Set analysis

A bit of update:

If I expand the retained year

Starting Year

Retained Year

EnrollmentRetained Students
200320043

2 (count (studentID))

2003200532
2003200633
200320073 2

Then I can use

Enrollment: count ( total<[starting year]> studentID)

However, I still want to show 1 year retention only, which means the retained year = starting year + 1. In this case, the enrollment formula is not working..

Any other suggestions?

Community Browser