How to compare Data from 1st grade last year to 2nd grade this year

[cank]

I'm not really sure how to phrase the question but here is what I want to do:

I have a pivot table that filters data by grade( Data = Count of student, Column = Grade, Row = Month, and then different sorting optoins in Page for time, location, etc).
Here is a sample of my data:

Excel 2003
	A	B	C	D	E	F	G	H	I	J
1	Referral Id	Date	Grade	Time	Location	Problem Behavior	Motivation	Others Involved	Admin Decision	Month
2	22745192	8/17/2011	K	3:30:PM	Bus	PAgg	Ob p attn	Peers	Conf	August-11
3	22742632	8/18/2011	1	10:30AM	Class	Fight	Avoid task	Peers	Conf	August-11
4	22742641	8/22/2011	2	3:30:PM	Bus	Fight	Ob p attn	Peers	Conf	August-11
5	22745072	8/25/2011	4	7:30:AM	Bus	Fight	Ob p attn	Peers	Conf	August-11
6	22745172	8/25/2011	K	11:30AM	Class	Other	Unknown	Peers	Conf	August-11
7	22745270	8/25/2011	4	3:30:PM	Bus	PAgg	Ob p attn	Peers	Conf	August-11
8	22745108	8/25/2011	1	3:30:PM	Bus	Fight	Ob p attn	Peers	Conf	August-11
9	27876733	8/21/2012	4	3:30:PM	Bus	PAgg	Ob p attn	Peers	Bus susp	August-12
10	27876715	8/22/2012	1	11:15AM	Plygd	Inapp lan	Ob itm	Peers	In-sch susp	August-12
11	27901541	8/24/2012	K	11:45AM	Plygd	PAgg	Ob p attn	Peers	Conf	August-12
12	27901363	8/24/2012	K	11:45AM	Plygd	PAgg	Ob p attn	Peers	Conf	August-12
13	27901381	8/24/2012	K	11:45AM	Plygd	PAgg	Ob p attn	Peers	Conf	August-12
Sheet2

This works great but I would like to be able to track student referrals of the Kindergarten class as they move up to 4th grade. So if I picked 4th grade it would return not just data from the current year but show what the 3rd grade did last year, what 2nd grade did 2 years ago and so on.
If I have to set up a different pivot table, I can or if I need to do something different, I will. I'm just not sure what function to look for or exactly how to describe it.

Any advice would be appreciated.
Thanks

 

[venkat1926]

NOT VERY CLEAR TO ME. why not filter k AND 1 copy to another sheet
unfilter and again filer 1 and 2 etch

 

[cank]

That was just an example. Maybe I can try to explain better:-\

I can download a set of data for each school year(either in separate sheets or in one with multiple years combined but I think separate sheets would be better) and I want to be able to track a grade level as they progress each year. So, for instance, I want to compare this years 4th graders to how they behaved last year in 3rd grade and the year before that in 2nd grade and the year before that in 1st grade and finally the year before that in Kindergarten.

Or, if I wanted to look at this years 3rd graders, it would pull up this years 3rd, last years 2nd, the year before's 1st and so on.

I was thinking if I could select a grade from the current year then have some kind of 'if' statement to select the previous years "grade -1", then keep going until Kindergarten. However, the school year doesn't follow the yearly calendar and I would need to set date ranges.

Maybe like you said, I could filter the data to another sheet and then make a pivot table from that?

 

[venkat1926]

you want to know the progress of idnividual students in the successive three grades or only as a group. in your sample first ID is

22745192
there appears no such ID in any other grade. remove unnecessary items like location. how is it relevant

explain with two examples

​

 

[cank]

Excel 2003
	B	C	D	E	F	G	H
1
2	Time	(All)
3	Location	(All)
4	Motivation	(All)
5	Admin Decision	(All)
6	Problem Behavior	(All)
7	Student	(All)
8
9	Count of Student	Grade
10	Month	1	2	3	4	K	Grand Total
11	August-10	1	5	4	2	7	19
12	September-10	11	21	12	7	37	88
13	October-10	8	8	7	5	14	42
14	August-11	5	2		3	8	18
15	September-11	14	14	8	32	15	83
16	October-11	6		6	10	1	23
17	August-12	2	2	1	3	10	18
18	September-12	7	11	7	19	22	66
19	October-12	1	6	4	10	12	33
20	Grand Total	55	69	49	91	126	390
21
22
Table Data

Here is the pivot table data from the last three years for the first three months of school. The data that I posted in the first post was just a small sample of the raw unfiltered data and I deleted info that would identify individual students.

The reason I have the other data is because we use it to narrow down where kids are getting in trouble, or what time of day they are getting in trouble, what the behavior was, and so on.

In the example above I have all variables selected to see the total number of referrals for each grade all three years.

In this next example I selected the Location "Bus" and it shows only those referrals.

Excel 2003
	B	C	D	E	F	G	H
1
2	Time	(All)
3	Location	Bus
4	Motivation	(All)
5	Admin Decision	(All)
6	Problem Behavior	(All)
7	Student	(All)
8
9	Count of Student	Grade
10	Month	1	2	3	4	K	Grand Total
11	August-10	1	1	1		1	4
12	September-10	5	4	5		4	18
13	October-10	3	1	2	3		9
14	August-11	1	1		2	1	5
15	September-11	5	4	1	9	1	20
16	October-11	1		2	2		5
17	August-12		1		2		3
18	September-12	3		3	4		10
19	October-12		1	1	1		3
20	Grand Total	19	13	15	23	7	77
Table Data

What I would like is to have another field with "grade" like this:

Excel 2003
	B	C	D
1	Time	(All)
2	Location	(All)
3	Motivation	(All)
4	Admin Decision	(All)
5	Problem Behavior	(All)
6	Student	(All)
7	Grade	4
8
9	Count of Student
10	Month	Total
11	August-10	2
12	September-10	7
13	October-10	5
14	August-11	3
15	September-11	32
16	October-11	10
17	August-12	3
18	September-12	19
19	October-12	12
20	Grand Total	93
Table Data

But instead of it showing 4th grade for the last three years, I would like it to show 4th grade for 2012, 3rd grade for 2011...
Or if I selected 2nd grade, it would show 2nd grade for 2012, 1st grade for 2011, and Kindergarten for 2010.

I'm sorry if this isn't making sense but I don't know how else to explain it. Thanks for trying to understand.