Comparing 2 Rows in Pivot Respecting Dimensionality

[#1]

I have a list of numbers that are broken down by year.  The numbers are sums() of a field in my data set constrained by a set analysis in the sum().  I can use the below function to find the difference between the sum in the current row and the sum in the row below.  However, when I add in a dimension (ex: Department), this breaks.  If I use NoOfRows() to offset the below(total) function it works for that first dimension but the next dimension I add, causes the expression to return incorrect results.  It is returning the results as of the physical number of rows and not the dimensional values.

Is there an expression that allows you to find the difference of sums between two of the same dimensional values?  So I want to be able to subtract the sums of Year 1 and Year 2 no matter how many dimensions I add in and the subtractions must be between the same dimensions (ex. same departments). 

Imagine a pivot that showed the number of goods sold by Year, Department and Sales Person.  The expression should be showing the difference of number of sales between the same sales person from Year 1 to Year 2.  If I drill up to Department, the expression should show the difference of number of sales between the same department from Year 1 to Year 2.

Any ideas?  Thanks.

James

[whiteline]

  I want to be able to subtract the sums of Year 1 and Year 2 no matter how many dimensions I add

Hi.

It seems that it could be done with set analysis, but of course it depends on your data model structure.

[#3]

May be I simplified things too much.  I tried to translate my data problems into data that I thought most would understand but I may have simplified it too much.  I'm acutally working with Student enrolment data to determine retention rates of students.  Retention is determined by if the student returns to the university the next year.  This is done by assigning each student a cohort based on their starting year.  So if you attended our university for the first time in 2012, your cohort is 2012.  You are kept in that cohort until you change faculties or leave the university.

The data I am using contains one record for each student attending the university for each year they are here.  This makes it easy to tell if the student has returned or not.  If there is a record, they came back.  This is culculated on data load and then summarized in a pivot to look like this (data is a mock up):

Cohort	Faculty	Y1	Y2	Y3	Y4	Y5
2012	Arts	1000
	Science	500
2011	Arts	900	500
	Science	450	400
2010	Arts	850	420	380
	Science	400	350	320

Each Year Here (Y1 to Y5) is a separate expression that uses set analysis to determine the number of students.  The basic formula appears as follows:

=sum( {<YearHere={1}>}StudentCount)

Therefore, the retention rate is calculated still within the Cohort dimension by doing:

=sum( {<YearHere={2}>}StudentCount)/sum( {<YearHere={1}>}StudentCount)

The retention rates would looks like the following pivot:

Cohort	Faculty	Y1toY2	Y2toY3	Y3toY4	Y4toY5
2012	Arts	-
	Science	-
2011	Arts	55.6%	-
	Science	88.9%	-
2010	Arts	49.4%	90.5%	-
	Science	87.5%	91.4%	-

What I want to do is determine what change in retention rate was between 2011 and 2010 for each faculty or any other dimension breakdown.  So for Arts, the retention rate increased by 6.2% for Y1toY2 (55.6% - 49.4%).  However, because I wrote a macro that allows the user to dynamically change the number of dimensions at any time, the only dimension I can count on being in the pivot is Cohort and they can have many levels of dimensions in any order they choose using my macro.  So the formula for calculating the change in retention rates needs to be dynamic too.  I'm either over complicating it and not seeing the easy route or there isn't one.

Thanks.