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.

Hey Dom

You must be having blank line items relating to the products in you data.
for example you have a row of record for Tomatoes will blanks in all years.

coz i added a calc field in my pivot and didn't get any repitition

..



"Domenick" wrote:

> 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.

No, definitely no blank lines in the data itself. For example, there are
absolutely NO rows associated with "tomatoes" for "Florida. Remember: This is
a calculated ITEM not a calculated FIELD. Calculated fields work fine,
however I need a calculated item to get the specific calculation I need
(difference between two specific ITEMS within a FIELD).

"walrus" wrote:

> Hey Dom
>
> You must be having blank line items relating to the products in you data.
> for example you have a row of record for Tomatoes will blanks in all years.
>
> coz i added a calc field in my pivot and didn't get any repitition
>
> .
>
>
>
> "Domenick" wrote:
>
> > 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.