Zeros in calculated items in pivot tables

[Tim Wheeler]

I have a very simple calculated item in a time dimension of a pivot table (Q1
= Jan+Feb+Mar). However because a lot of the data is has null values my
calculated item returns a zero value which increases the number of rows in my
pivot table by 10 times. Anyone know how I can either not show these zeros or
return a null value when the item is calculated? Thanks Tim

[ShaneDevenshire]

Hi,

Here are a number of possible solutions:
1. With the pt selected choose PivotTable, Table Options, and check the box
for For empty cells, show.
2. Modify your calculated item's formula to read =IF(X+Y=0,"",X+Y) - this
may not meet your needs!
3. Open the filter for the field and uncheck Blanks or whatever it is that
represents your returned results.
4. Double-click the field button for the field in question and uncheck Show
Items with no data, if that is checked.

If none of these work give us more details.

--
Cheers,
Shane Devenshire

[Tim Wheeler]

Hi Shane

Really appreciate your response.

I am sure your option 2, using an IF statement, is the way to go, but when I
try it my pivot table returns #value! for the calculated items. Your other
options are useful but unfortunatley do not work for my example because,
unless I can get some sort of IF working, my calculated items are returning
zeros rather than nulls.

Thanks for trying, kind regards Tim

[ShaneDevenshire]

Hi Tim,

How about this then:
Instead of your calculated Item/Field -
1. In the pivot table select the row or column field where your dates are
and choose the command PivotTable, Group and Show Detail, Group. In the
dialog box choose Quarter (and Month) and click OK. Double-click the field
button for the quarter field and change Summarize by to Automatic.

[geofffreestone]

I too need to get rid of the blanks in my pivot table that appear when I add calculated items. I had tried all of the things suggested before I read this thread; none worked and the data's that unusual that I can't use anything like "qtrly". Any more ideas?

[Don Guillett]

Nice to tie it to the thread you are talking about.......

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"geoff freestone" wrote in message
news:200861112144...@shu.ac.uk...