Pivot Table Calculations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neel - 21 Feb 2007 22:43 GMT

I am trying to create a Pivot Table to show the WOS (weeks of stock)
by week over 2005, 2006 and 2007 for certain product families.

WOS = OH/AWS where OH = On Hands (available data) and AWS = Average
weekly sales (also available data). I did the WOS calculations myself
on Excel and used the data to throw it in a pivot table.

When I'm displaying an individual SKU from a certain product line of a
certain product family my numbers are right. The problem is whenever I
collapse it further to see the whole product line (or the whole
product family), I see a sum of my WOS for all the SKU's in that
certain category as opposed to its average.

Is there anyway to rectify this issue? Any help in this matter would
be appreicated.

Reply to this Message

Jim Thomlinson - 22 Feb 2007 21:01 GMT

To come up with what you want you need to calculate your WOS using
calculation right in the pivot and not in the external data source. Depending
what your data looks like it will either be a calculated item or a calculated
field... Are OH and AWS seperate fields in the source data or is there a
category column that specifies wheter the amount is going to be OH or AWS...

1. Seperate Fields - Calculated Field
Item    OH    AWS
This    100    200
That    50    70

2. Category - Calculated Item
Item    Cat    Amount
This    OH    100
This    AWS    200
That    OH    50
That    AWS    70

Signature

HTH...

Jim Thomlinson

> I am trying to create a Pivot Table to show the WOS (weeks of stock)
> by week over 2005, 2006 and 2007 for certain product families.
[quoted text clipped - 11 lines]
> Is there anyway to rectify this issue? Any help in this matter would
> be appreicated.

Reply to this Message

Neel - 22 Feb 2007 21:59 GMT

Jim,
Thanks a lot. I haven't used Pivot tables extensively enough to
understand the data set-up format. I have all the data separated out
in mutiple sheets so I will probably use the first set-up and run with
it where I have them as separate fields in the source data.
All I ahve to do is run a couple of vlookups and I should have the
data set up right. Once again, thanks for your help.

NC.

On Feb 22, 4:01 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:

> To come up with what you want you need to calculate your WOS using
> calculation right in the pivot and not in the external data source. Depending
[quoted text clipped - 18 lines]
>
> Jim Thomlinson

Reply to this Message