Pivot table: copying one item's values into another item's column

[phaedrus1313]

(Sorry, trouble figuring out a good title for this.) Hope the example helps.

I've got sales figures by year & month for both projected and actual sales. I've got a pivot table that looks something like:

2008                      2009
J   F   M   A ...         J   F   M   A ...
P A P A P A P A ...       P A P A P A P A ...

(J, F, etc. are months and P is projected and A is actual.)

What I'd really like though, is this:

2009
J       F       M       A ...
P A LYA P A LYA P A LYA P A LYA ...

Where LYA is Last Year's Actual - that is, the actual sales for that month in 2008.

Effectively, I want to hide 2008 but add a value column to my 2009 display that borrows values from the "same location" in the 2008 item. Is this possible? If not, anything very similar?

thanks very much,
phaedrus1313

 

[hkaplan2]

Can you post a small snapshot of the data?

 

[phaedrus1313]

Sure; this is unfortunately a bit contrived since I can't post the actual data, but hopefully gets the point across. Also, I don't know how to properly format tables / examples here - sorry about that:

Item    Year    Month    Projected    Actual
XYZ     2008    Jan      8000         7000
ABC     2008    Jan      3000         3000
ABC     2008    Feb      3000         3500
QQQ     2009    Jan      9500         11000
QQQ     2009    Feb      9500         9500
XYZ     2009    Feb      4200         4000

I'd like the results to be:

2009
Jan               Feb
P     A     LYA   P     A     LYA
9500  11000 10000 13700 13500 3500

Of course in the real data there are various other properties of the items that are used to make more rows and do various pivots, but this captures the general idea I hope.

thanks,
Lee

 

[hkaplan2]

First thought is to create a new field in the table which looks up the "current year-1" (prior year) and returns that years actual using vlookup or similar. Then you can use it in the pivot table.

 

[phaedrus1313]

Unless I'm misunderstanding, I don't think that will work. The problem is that there isn't any sort of 1-to-1 correspondence between the sales line items for the two years. Any sort of VLOOKUP (or SUMPRODUCTS in this case) will (I think) end up being at the line item level and then when I add the new field as SUM(...) it will end up adding the 2008 actual multiple times.

I tried adding a new column to my raw data table which just calculated the sum of actuals for the same year and month as the current line item, and then added that to the pivot table with "min" (since it's constant for all line items in a given year + month). That actually works, except it doesn't work when I roll up to subtotals and grand totals, as it's still doing MIN instead of SUM at that point.

So I'm still at a loss...

thanks for any further suggestions,
phaedrus1313