Summarize Pivot Tables

[Shamoun]

Default options in Excel for summarizing Pivot tables is Sum, average, max, min, product etc. Is it possible to summarize a pivot table by the last value in the given columns?

 

[Fazza]

Hi. Please, can you give an example of what is wanted? Thank you.

 

[Shamoun]

Hi.
i downloaded Quarterly Earning Per Share for 600 companies for last 5 years and made a pivot table. I have companies and quarters in the rows section and years in the column section. So any companies given data looks like following

CompanyXYZ 2006 2007 2008 2009 2010 2011
Q1 1.1 1.2 1.3 1.4 1.5 2.0
Q2 2.3 2.4 2.5 2.6 2.7
Q3 3.4 3.6 3.8 4.0 4.2
Q4 5.0 5.3 5.5 5.8 6.0

Currently what the pivot table does is summarize the "years" column either on the basis of sum, average, max, min etc.

What i would like it to is summarize by the basis of last entry in that year. So the summary should look like following:

2006 2007 2008 2009 2010 2011
5.0 5.3 5.5 5.8 6.0 2.0

Notice the last entry in 2011 was in Q1, so i want it to pick that value up.

Thank you for your help.

 

[Fazza]

Maybe, drag the "quarter" field to the top LHS where it becomes a 'page' field and then select Q1.

Or, where it is right now, hit the little drop down that should show field entries and deselect all, then select Q1.

Would that be OK or are you after something else? Or automatic? Or ??

 

[Fazza]

Or maybe it could be a different quarter for each different company?

If you can explain it, it likely can be done somehow...

 

[Shamoun]

Its a different quarter for each company, so the page field/selection does not work.

I understand what you are suggesting, and yes it would work manually where i select relevant quarter for different companies. But i want something automatic.

The only i way figured out was that in the original source data, i added a field and with the help of a function, was able to pull the last entry of each year (for the relevant company) in that column (field). Then i made a pivot table using that field. It gets me the summary i wanted, but then i do not have the entire data in table for comparison.

 

[Fazza]

OK, I understand the particular quarter will be specific to each company.

I can understand you could identify that by an extra field. (And, BTW, that could be done without an extra field by using a different approach if need be.)

I don't understand the last sentence, "It gets me the summary i wanted, but then i do not have the entire data in table for comparison."

I also don't 100% understand the result from your second post, #3 in the thread.

What i would like it to is summarize by the basis of last entry in that year. So the summary should look like following:

2006 2007 2008 2009 2010 2011
5.0 5.3 5.5 5.8 6.0 2.0

Can you give some further explanation, please, of exactly what is wanted?

In the quoted section, why are years 2006-2010 the Q4 results and 2011 Q1?

Is the rule: for other than the last year give Q4 results and for the last year, the last Q available?

 

[Shamoun]

if i can explain it simply (forgetting the example of my dataset)- As per default the pivot table can summarize giving you either SUM, MAX, MIN, AVERAGE etc of the values. All i want is the pivot table to summarize the data by last value.

 

[Fazza]

Well, we've gone back to the original question.

All i want is the pivot table to summarize the data by last value

Which I don't understand.

And now we're wanting to forget the example dataset.

On this basis I can't help. Sorry. I don't understand the requirement.

Like I wrote earlier, "If you can explain it, it likely can be done somehow..."

Regards

 

[Claymationator]

if i can explain it simply (forgetting the example of my dataset)- As per default the pivot table can summarize giving you either SUM, MAX, MIN, AVERAGE etc of the values. All i want is the pivot table to summarize the data by last value.

I am not sure if that is possible in the Pivot Table without changing the underlying data. The Pivot is summarizing the data based on the year, quarter, and company. Each of the values that you have listed there corresponds to one company, but different quarters/years for each specific value. You say that you only want to show the last value (assuming the last value input for any specific year), but if your Pivot is summarizing it this way, then you have defined the 2.0 value as being in Q1 of 2011. So, if you are going to show quarters, it will only correspond with Q1 of that year. However, if you take the quarters out, you will then have a SUM, AVERAGE, COUNT, etc. of all of the previous years...2011 would be correct in this case as there is only one quarter, but all other years would not show the desired result.

If that is correct, and I understood your question correctly, I am not sure how you would display it with the way you have it currently set up. You could re-classify the data as Q4, but that isn't quite correct.

If the data always follows the same pattern that you have here, you could show it as a MAX as Q4 is always the highest number of each year...so that could be a workaround if your data always keeps that pattern.