Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

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


Re: Calculated field in pivot table

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.

.


Re: Calculated field in pivot table

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.