Calculated field in pivot table

Posted by David Kelly on November 10, 2001 4:01 AM

I have an excel list that contains forecast values for each week of the year ( so far the list stretches for over two years a simplistic version of the list looks something like this :-

Forecast Forecast Forecast Forecast Forecast
Date Week period year value
10/1/00 3 10 2000 1000
17/1/00 4 10 2000 1010
..
...
10/1/01 55 10 2001 2500
17/1/01 56 10 2001 2520

I have created a pivot table that will select the data for period 10 to show how the forecast moves for a period week on week.

What i want to do is insert a field into the table that will pull the forecast through for the same week last year ie forecast week - 52. I am guessing that you can insert a calculated field into the table and use some sort of relative reference ie forecast week -52 but I don't know how

Any help would be appreciated

Posted by Mark W. on November 10, 2001 12:09 PM

A Calculated Field wouldn't help... it's used for
a value in the DATA area... you need a modified
item in the ROW or COLUMN area. Just create
create another column in your forecast data
list (I'll call it 'Week2') which tranforms
your 'Week' values to a value between 1 and 52
inclusive. The formula for 'Week2' should be
=IF(MOD('Week',52),MOD('Week',52),52). Now you
can use 'Week2' and 'Year' in your PivotTable
to align your forecast values by week.

Posted by David Kelly on November 11, 2001 8:47 AM

Thanks for answering but I am still at a loss. I have already got a column in my forecast data that refers to the week no. Although what I have done is started at Jan 1 2000 as week 1 and worked up i.e Jan 7 2001 is week 53 Jan 14 week 54 etc.
As the pivot table shows the forecast date accross the columns and the forecast values down the rows I thought I would be able to to somehow say :-
As well as displaying the forecast for the forecast date, show the forecast for 52 weeks earlier.

Any further help would be appreciated