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

Anonymous — Mon, 12 Dec 2016 12:45:41 GMT

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
1	2003	2003
1	2003	2004
1	2003	2006
1	2003	2007
2	2003	2003
2	2003	2005
2	2003	2006
2	2003	2007
3	2003	2003
3	2003	2004
3	2003	2005
3	2003	2006
4	2004	2004
4	2004	2005
4	2004	2006
5	2004	2004
5	2004	2006
5	2004	2007

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

Starting Year	Enrollment	Retained Students after 1 year (this year could change, could be 2, 3 ,4 years,etc.)
2003	3 (count (studentID))	2 (has problem)
2004	2 (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	Enrollment	Retained Students after 1 year (this year could change, could be 2, 3 ,4 years,etc..)
2003	2004	3 (has problem)	2 (count (studentID))
2004	2005	2 (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)

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

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

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

Anonymous — Mon, 19 Dec 2016 05:55:34 GMT

A bit of update:

If I expand the retained year

Starting Year	Retained Year	Enrollment	Retained Students
2003	2004	3	2 (count (studentID))
2003	2005	3	2
2003	2006	3	3
2003	2007	3	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?

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

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

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