
Re: Comparing 2 Rows in Pivot Respecting Dimensionality
whiteline _ Nov 10, 2012 3:06 AM (in response to James Rozee)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.

Re: Comparing 2 Rows in Pivot Respecting Dimensionality
James Rozee Nov 13, 2012 10:46 AM (in response to James Rozee)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.