Pivot table question, comparisons for years

[anationalacrobat]

Say I've got data that looks like this:

Customer...Date...OrderAmount

And I have multiple orders per customer and multiple dates and of course varying amounts.

I want to create a pivot table that sums my customer orders by year and compares year to year.

So far I have the first part just fine -- I have two years of orders, I can see customer 1 spent $2000 in 07 and $3000 in 08. What I'd like to be able to do is also compare year to year and see if he's up or down.

Now I know I can reference data within a pivot table in regular cells but would I do that or is this something that should also be included in the pivot table? What I'm ultimately looking to work towards is having cells that show me for region 1 I had x new, y increase, z, decrease, a skip and b flat. These will of course be year to year comparisons between customers. I'm just not sure which is the smartest approach here. I'm wondering if I might need to perform those calculations in the data dump phase and then make the pivot table after that point.

Thanks!

 

[SkipVought]

Hi,

Drag your DATE into the pivot table.

Right click the Date Field in the PT and select Group and outline > Group - Select YEAR

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[anationalacrobat]

The grouping will compare years? Ok, I wasn't seeing that but I'll have to try again. The changes between 2007 and everything that came before, they're killing me. There's such a wealth of Excel data online but the old tutorials are just about useless when looking at 2007. I saw some tutorials for 2003 describing how to do year to year comparisons but everything looks and behaves differently in 2007. Will keep you posted on my success, or lack of it!

 

[SkipVought]

You never mentioned that you were working in Excel 2007. But, already having a PT in your sheet, it ought to work as previously stated.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[anationalacrobat]

Ok, now it's working! Time to fiddle with it some more now.