Sorry Fazza, for being unclear.
So in my dataset, the year is actually financial year.
So all companies by December 2010, have not announced their Q4 earnings for financial year 2010. so thats why from 2006-2010, i have all the Q1 to Q4 EPS for each year.
Now for the year 2011. Those companies that have closing in March 2011.. at the end of December this year, announced their Q3 EPS. Those companies having closing in June 2011, announced their Q2 earning. Those companies that have closing in September 2011, announced their Q1 earning. Companies closing in December 2011, have no EPS data as yet. So for each company as you can see the quarter is different.
What i did with my data source was create a new column and with help of a formula, pick up the last data for each year. So from 2006-2010, this was always the Q4 earning for all companies. For 2011, for various companies it was different (for some Q1, Q2, Q3). Then i created a pivot table based on these values. The problem with this is, when i create the pivot, i get the summary of last reported EPS per year (which is what i want). But when i expand the "company field" for example i wont get the EPS of company-XYZ Q2 for 2008 (which i also need for comparison). I will only get the Q4 for 2008.
Hope i made some sense.
