Ideas for an Excel or PowerPivot approach to aggregation?

Hello All,
I am in the brainstorming stage for calculating the distinct count of students who are attending in a specified year/term, and of those same students, which did not attend the previous year/term.

The dataset does contain multiple records per student ID, one record for each term they have attended.  I can attach the dataset if needed (with sensitive data replaced/removed).

We do have PowerPivot Version: 11.0.3000.0 in Excel 2010, Version 14.0.7143.5000 (32-bit).  I am new to using PowerPivot (but am loving it).

I believe it maybe might be best to steer clear of array formulas if it can be avoided due to the performance, as we will be displaying these results across multiple years in a report summary sheet, however, I am open to any efficient and effective approach!

Any ideas will be much appreciated!

Thanks in advance,

Lindsay
Lindsay_KAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
I'm in agreement with you on this...

I will calculate this by something like this...

[StudentsPrYr] = CALCULATE( distinct([Students]), DATEADD(DimDate[DateKey], -1, YEAR))

Open in new window


This will give you distinct students previous year compare to year whatever you are looking for..

Saurabh...
Lindsay_KAuthor Commented:
Hi Saurabh,
Thank you for your response.  As I was re-reading my question, I discovered I did not clarify well enough and I apologize.  I am trying to calculate "Returner" students, which means I need to be able to identify students who have attended our school in a past year, then didn't come back for one or more years, and then attended again in a subsequent year.  

I have created a PowerPivot table that has a row for each student ID number and a column for every year.  It displays a "1" for each ID and each year that correlate, so I can actually visually see those having missing year(s) between two years that were attended for a specific ID, but after several hours of research I have not been able to identify a coded solution to calculate this in cells for each ID.


The table looks something like this:
*Number of Years not limited to 5

                      2005         2006         2007        2008        2009      (The result I'd like to Produce)      
2345                 1                0               1              1               0                          Returner
2564                 1                1               1              0               0                      Non-Returner
3857                 0                1               0              0               1                          Returner
9274                 0                0               1              1               0                      Non-Returner


I apologize and thank you again,

Lindsay
Saurabh Singh TeotiaCommented:
Can you try using if formula for the same as looking at your data i will say try doing it by if formula...since in powerpivot you can use the if formula as well..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lindsay_KAuthor Commented:
Hi Saurabh,
I have used your suggestion in the form of a helper column in the fact table that utilizes an IF statement that examines the records, each in correlation to the others adjacent to it.  I coded the SQL in the originating data source to sort the records in ascending order for the ID numbers and descending order for the year and term fields.  The code I ended up using analyzes and outputs additional group designations for each record as well.  This is what I ended up using in the helper column:

=IF(AND(S6="00ZZZ"),"NOT APPLICABLE",IF(AND(M6>DATE(Y6,8,31),M6<DATE(Y6+1,9,1),T5<>0),"GRADUATED",IF(AND(A6<>A7,Y6=T6),"INITIAL",IF(AND(A6=A7,A6<>A8,Y6=Y7+1),"RETAINED",IF(AND(A6=A7,AA7="RETURNED",Y6=Y7+1),"RETAINED",IF(AND(A6=A7,A6=A8,Y6=Y8+2),"PERSISTED",IF(AND(A6<>A7,Y6>T6,T6<>0),"RETURNED",IF(AND(A6=A7,Y6>Y7+1,T6<>0),"RETURNED","NOT APPLICABLE"))))))))

Thank you so much for your help Saurabh!

-Lindsay
Lindsay_KAuthor Commented:
Oh yes!  Also, if it is helpful to anyone, I did use CUBEVALUE functions to aggregate these values from the helper column in the report data so I could return distinct unique counts for the ID numbers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.