D
Domenick
Lets say I have a pivot table that groups sales totals by company and product
(rows) and years (columns). I want to add a calculated ITEM (a calculated
FIELD wont do the trick) that determines the difference between two
particular years sales. The calculated item performs the calculation as
expected, however now I get rows for products that the company doesn't sell.
Example:
Original Pivot Table:
Sales
Year
Company Product 2007 2008 2009
Florida
Oranges 100 200 250
Lemons 150 250 600
New Jersey
Tomatoes 100 200 300
Corn 400 200 300
California
Tomatoes 500 600 700
Oranges 300 250 200
Then I add the calculated ITEM (2009 - 2007) and the table changes to:
Sales
Year
Company Product 2007 2008 2009 2009-2007
Florida
Oranges 100 200 250 150
Lemons 150 250 600 450
Tomatoes 0 0 0 0
Corn 0 0 0 0
New Jersey
Oranges 0 0 0 0
Lemons 0 0 0 0
Tomatoes 100 200 300 200
Corn 400 200 300 -100
California
Oranges 300 250 200 -100
Lemons 0 0 0 0
Tomatoes 500 600 700 200
Corn 0 0 0 0
As you can see, I now have every single product listed for each company even
though there are no such records for those products in my table. With my real
data, this turns a simple one page pivot table into 390 pages - most of which
is filled with zeros. There does not appear to be a way to supress the
products which have no value.
i have scoured Google and have found many people asking this question and
NONE of them have a solution posted. If ANYONE can tell me how to solve this,
it would be greatly appreciated. Thanks.
(rows) and years (columns). I want to add a calculated ITEM (a calculated
FIELD wont do the trick) that determines the difference between two
particular years sales. The calculated item performs the calculation as
expected, however now I get rows for products that the company doesn't sell.
Example:
Original Pivot Table:
Sales
Year
Company Product 2007 2008 2009
Florida
Oranges 100 200 250
Lemons 150 250 600
New Jersey
Tomatoes 100 200 300
Corn 400 200 300
California
Tomatoes 500 600 700
Oranges 300 250 200
Then I add the calculated ITEM (2009 - 2007) and the table changes to:
Sales
Year
Company Product 2007 2008 2009 2009-2007
Florida
Oranges 100 200 250 150
Lemons 150 250 600 450
Tomatoes 0 0 0 0
Corn 0 0 0 0
New Jersey
Oranges 0 0 0 0
Lemons 0 0 0 0
Tomatoes 100 200 300 200
Corn 400 200 300 -100
California
Oranges 300 250 200 -100
Lemons 0 0 0 0
Tomatoes 500 600 700 200
Corn 0 0 0 0
As you can see, I now have every single product listed for each company even
though there are no such records for those products in my table. With my real
data, this turns a simple one page pivot table into 390 pages - most of which
is filled with zeros. There does not appear to be a way to supress the
products which have no value.
i have scoured Google and have found many people asking this question and
NONE of them have a solution posted. If ANYONE can tell me how to solve this,
it would be greatly appreciated. Thanks.