Pivot table problem

[jps1x2]

Hi all,

I have one problem showing stock column on a pivot table, because i
don't know how to configure a calculated field correctly, i don't know
what functions use to lookup up (or sum) the correct stock and put it
on the Stock column.

This is the data of my PivotData sheet of my excel book:

And the data will be:

Rep|Region|Date|Item|Colour|Units|Unit Cost|Total|Stock
Gill|Ontario|15/01/2006|Binder|Beige|46|8,99|413,54|15
Gill|Ontario|16/01/2006|Binder|Beige|46|8,99|413,54|15
Gill|Ontario|17/01/2006|Binder|White|46|8,99|413,54|10
Gill|Ontario|10/09/2006|Pencil|Black|7|1,29|9,03|50
Gill|Ontario|11/09/2006|Pencil|White|7|1,29|9,03|60
Gill|Ontario|12/09/2006|Pencil|Black|7|1,29|9,03|50

Actually, Binder Beige has 15 in stock, White 10, Pencil Black has 50
and white 60.

in row section of the pivot tableit will be following fields:

Rep|Region|Date|Item|Colour

and in data section fields:

Units|Unit Cost|Total|Stock

Column stock is there because i need to know how many available stock
i have now of this item now. When i have expanded at all (Rep|Region|
Date|Item|Colour) the stock is ok, but when i have agreggated not at
all level (Rep|Region| Date|Item) it would be interesting that Stock
field to summarize, for each item, all the stock of different colours
of that item.

Example, when i see agreggated at Rep level correct would be:

Rep Reg Item Colour Date Units Cost
Total Stock
Gill 159
30,84 1267,71 135
General total 159
30,84 1267,71 135

But it shows the following, because it sums all stock:

Rep Reg Item Colour Date Units Cost
Total Stock
Gill 159
30,84 1267,71 200
General total 159
30,84 1267,71 200

if i see aggregated at Rep|Region|Date|Item level correc would be:

Rep Reg Item Colour Date Units Cost
Total Stock
Gill Ontario Binder 138 26,97
1240,62 25
Pencil 21
3,87 27,09 110
Total Ontario 159
30,84 1267,71 135
Total Gill 159
30,84 1267,71 135
Total general 159
30,84 1267,71 135

But it shows the following, because it sums all stock:

Rep Reg Item Colour Date Units Cost
Total Stock
Gill Ontario Binder 138 26,97
1240,62 40
Pencil 21
3,87 27,09 160
Total Ontario 159
30,84 1267,71 200
Total Gill 159
30,84 1267,71 200
Total general 159
30,84 1267,71 200

I have tried all functions of calculated fields Field settings (Sum,
Count, Average, Max, Min, Product), but it doesn't work i need to know
how to configure that field and what function or formula i need.

Thank you very much / Muchas gracias

Jorge

 

[Debra Dalgleish]

The stock totals are directly related to the rest of the information in
the row, because the stock is not specific to a rep or a region.

If you add that field to the pivot table you can use Max to show the
highest stock quantity for the item and colour. But if Colour isn't
visible, the quantity will be incorrect.

Perhaps you can create a separate pivot table of stock data, from
another table of data with unique stock item entries.