Excel - Hide calculated field resulting in zero within a pivot table

[markwilliams2004]

Hi again,

I have some calculated items that I have inserted into a pivot table that are resulting in zero values for intersections where there is no valid data.

Is there a way to suppress this calculated item when there is no data to calculate? I have tried hiding zeros etc.

 

[SkipVought]

Use a column outside the PT to referenc the Total column in the PT.

AutoFilter on this new column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

The problem is that the zero I am trying to suppress is the column and not a row in the column if I understand your response properly.

Is that what you were saying Skip? Thanks for your help by the way.

 

[SkipVought]

Could you post an example, with enough data to demonstrate the issue. and explain what you want to happen referring to the example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

OK on this field there are no values for 11/30/2007. I do not want that column to show. It is only showing up because of the net profit/loss and total expense calculated items below. I cannot categorize it into revenue or expense totally (I do not think) since # of boxes is neither. For other fields 11/30/2007 is a valid period with valid data. For this one it should not appear as there is no data to show/calculate.


Field 108BP

Sum of Amount Period
Account 11/30/2007 12/31/2007
SALES - -
# of Boxes - -
Freight - -
Packing - -
Equipment - 624.29
Fertilizer - -
Herbicide - -
Insecticides - -
Fungicides - -
Irrigation - -
Labor - 182.70
Diesel - -
Irrigation Equipment - -
Miscellaneous - -
Hauling - 13.23
Spraying - -
Harvesting - -
Pruning - -
Watchmen - -
Land Prep - 287.56
Business Tax - -
Trees - -
Lease Payment - -
Total Expenses - 1,107.78
Net Profit/(Loss) (US$) - (1,107.78)

 

[SkipVought]

It seems that the problem might be in the way you queried your data for the source table.

Why are you including Accounts with zero associated values into your source table?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

There is no data for that field for 11/30/07. The pivot table is set to display nulls as zero.

This is a from historical table and there are other fields that have valid data for 11/30/07.

The problem that I see is if you create a calculated item then it applies and calculates whether there is data for that field or not.

All I am looking to do is suppress that column if there is no data but the PT is still trying to calculate.

Perhaps it can't be done from what I'm seeing on line and I'm not sure how else to explain it.

 

[SkipVought]

...which is one of the reasons that I suggested to add a column to the Source and forget the calculation in the PT.

If there is NO data for 11/30/2007 in the accounts you are displaying, it should not appear.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

Not sure I understand what you are exactly proposing. I would need to somehow calc Total Expenses and Net Profit in the source then. What are your thoughts on that or can you give me an example, if it's not too much trouble?

Thanks again.

 

[SkipVought]

Please post a small sample of your SOURCE data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

Month Year AccountingNumber Account Amount
12 2007 08210 SALES
12 2007 08210 # of Boxes
12 2007 08210 Freight
12 2007 08210 Packing
12 2007 08210 Equipment 624.29
12 2007 08210 Fertilizer
12 2007 08210 Herbicide
12 2007 08210 Insecticides
12 2007 08210 Fungicides
12 2007 08210 Irrigation
12 2007 08210 Labor 182.70
12 2007 08210 Diesel
12 2007 08210 Irrigation Equipment
12 2007 08210 Miscellaneous
12 2007 08210 Hauling 13.23
12 2007 08210 Spraying
12 2007 08210 Harvesting
12 2007 08210 Pruning
12 2007 08210 Watchmen
12 2007 08210 Land Prep 287.56
12 2007 08210 Business Tax
12 2007 08210 Trees
12 2007 08210 Lease Payment
1 2008 08210 SALES
1 2008 08210 # of Boxes
1 2008 08210 Freight
1 2008 08210 Packing
1 2008 08210 Equipment 292.69
1 2008 08210 Fertilizer 400.59
1 2008 08210 Herbicide 90.44
1 2008 08210 Insecticides 85.85
1 2008 08210 Fungicides 452.50
1 2008 08210 Irrigation 8,581.10
1 2008 08210 Labor 738.58
1 2008 08210 Diesel 494.00
1 2008 08210 Irrigation Equipment 205.94
1 2008 08210 Miscellaneous 565.55
1 2008 08210 Hauling 179.41
1 2008 08210 Spraying 37.70
1 2008 08210 Harvesting
1 2008 08210 Pruning
1 2008 08210 Watchmen 328.41
1 2008 08210 Land Prep 85.32
1 2008 08210 Business Tax
1 2008 08210 Trees 6,962.03
1 2008 08210 Lease Payment 211.46

 

[SkipVought]

Please use TGML Tag TT when posting sample data and take the time to line up each field properly.

If you do not know what a TGML Tag TT is, then search this page for TGML and learn how to use some of these basic codes. I am spending too much time trying to PARSE your example data.

ALSO, you did not post data that in any way related to the FIRST example you were acking about -- eliminating the column with ZEROS. Where is THAT Source???


Here's your modified source data; one added column AND rows without AMOUNTS have been DELETED...
[tt]
Month Year AccountingNumber Account Amount AcctCat
12 2007 08210 Equipment 624.29 Exp
12 2007 08210 Labor 182.7 Exp
12 2007 08210 Hauling 13.23 Exp
12 2007 08210 Land Prep 287.56 Exp
1 2008 08210 Equipment 292.69 Exp
1 2008 08210 Fertilizer 400.59 Exp
1 2008 08210 Herbicide 90.44 Exp
1 2008 08210 Insecticides 85.85 Exp
1 2008 08210 Fungicides 452.5 Exp
1 2008 08210 Irrigation 8,581.10 Exp
1 2008 08210 Labor 738.58 Exp
1 2008 08210 Diesel 494 Exp
1 2008 08210 Irrigation Equipment 205.94 Exp
1 2008 08210 Miscellaneous 565.55 Exp
1 2008 08210 Hauling 179.41 Exp
1 2008 08210 Spraying 37.7 Exp
1 2008 08210 Watchmen 328.41 Exp
1 2008 08210 Land Prep 85.32 Exp
1 2008 08210 Trees 6,962.03 Exp
1 2008 08210 Lease Payment 211.46 Exp[/tt]
Here's the PT off this source...
[tt]

Sum of Amount AcctCat Year
Exp
2007 2008
Account 12 1
Diesel 494
Equipment 624.29 292.69
Fertilizer 400.59
Fungicides 452.5
Hauling 13.23 179.41
Herbicide 90.44
Insecticides 85.85
Irrigation 8581.1
Irrigation Equipment 205.94
Labor 182.7 738.58
Land Prep 287.56 85.32
Lease Payment 211.46
Miscellaneous 565.55
Spraying 37.7
Trees 6962.03
Watchmen 328.41
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also, how did you get descrete dates...
[tt]
Account 11/30/2007 12/31/2007
[/tt]
when your source data has ...
[tt]
Month Year AccountingNumber Account Amount
[/tt]
copied straight from your posts!

Please get your story together and present an ACCURATE reqiurement that is truely representative of your problem! Otherwise we are wasting time!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

Skip,

I'm sorry if I did not follow protocol within this forum but find that your tone extremely condescending.

I will gladly follow protocol and will do so in the future. Please feel free to point me to the guildelines so I can be comliant in the future.

As for the bolded "copied straight from you posts!" comment, I grabbed some data thst I have been working with as this discussion occurred which yes had been modified for date vs period/year as that was not what the question was and is really unimportant to what I was trying to solve for. The question was whether there was a way around a calculated item from showing when there was no data for a period. Whether it was a discrete date or period/year is insignificant.

You presented an alternative to classification of the accounts which would possibly assist but as I mentioned earlier I had already tried that and because there were statistical accounts (ie non-financial) within the source data, your solution would not work. You presented the same exact solution after criticizing me.

As I said, please point me in the direction of forum protocol and I will gladly follow it.

Sorry you feel the need to critique and in my opinion berate someone asking a question. The tone is clearly one of annoyance directed at someone new to this forum and asking for assistance.

I'm sorry you feel as though you wasted your time and please do not feel obligated to answer any of my time-wasting, non-conforming posts in the future.

Thanks for your time on this issue and I'll seek out forum posting guidelines. Have a pleasant day.

 

[SkipVought]

This is how it can work for your Rev, Exp & Oth categories of your Chart of Accounts...
[tt]
Month Year AccountingNumber Account Amount AcctCat
12 2007 08210 Equipment 624.29 Exp
12 2007 08210 Labor 182.7 Exp
12 2007 08210 Hauling 13.23 Exp
12 2007 08210 Land Prep 287.56 Exp
1 2008 08210 Equipment 292.69 Exp
1 2008 08210 Fertilizer 400.59 Exp
1 2008 08210 Herbicide 90.44 Exp
1 2008 08210 Insecticides 85.85 Exp
1 2008 08210 Fungicides 452.5 Exp
1 2008 08210 Irrigation 8,581.10 Exp
1 2008 08210 Labor 738.58 Exp
1 2008 08210 Diesel 494 Exp
1 2008 08210 Irrigation Equipment 205.94 Exp
1 2008 08210 Miscellaneous 565.55 Exp
1 2008 08210 Hauling 179.41 Exp
1 2008 08210 Spraying 37.7 Exp
1 2008 08210 Watchmen 328.41 Exp
1 2008 08210 Land Prep 85.32 Exp
1 2008 08210 Trees 6,962.03 Exp
1 2008 08210 Lease Payment 211.46 Exp
12 2007 08210 SALES 12000.00 Rev
12 2007 08210 # of boxes 5 Oth
[/tt]

[tt]
Sum of Amount AcctCat Year
Exp Rev Oth
2007 2008 2007 2007
Account 12 1 12 12
Diesel 494
Equipment 624.29 292.69
Fertilizer 400.59
Fungicides 452.5
Hauling 13.23 179.41
Herbicide 90.44
Insecticides 85.85
Irrigation 8581.1
Irrigation Equipment 205.94
Labor 182.7 738.58
Land Prep 287.56 85.32
Lease Payment 211.46
Miscellaneous 565.55
Spraying 37.7
Trees 6962.03
Watchmen 328.41
SALES 12000.00
# of boxes 5
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markwilliams2004]

thanks....

 

[MrSnid]

Mark,

Which version of Excel are you working in ? I didnt see it (doesnt mean it wasnt there, just that I didnt see it =)

And did the solution finally click for you ?

If you are working in 2007... I only just discovered you can have the values in both the Data section while simultaneously having them in the Report Filter section (( from there you can filter out the zeros ))

If you are working in 2003, i might have to get back to you...

Good Luck !
Leon