Zeros in calculated items in pivot tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim Wheeler - 25 Jun 2007 16:39 GMT

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

Reply to this Message

ShaneDevenshire - 25 Jun 2007 20:46 GMT

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.

Signature

Cheers,
Shane Devenshire

> 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

Reply to this Message

Tim Wheeler - 26 Jun 2007 08:07 GMT

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

> Hi,
>
[quoted text clipped - 15 lines]
> > 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

Reply to this Message

ShaneDevenshire - 26 Jun 2007 14:18 GMT

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.

Signature

Cheers,
Shane Devenshire

> Hi Shane
>
[quoted text clipped - 27 lines]
> > > 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

Reply to this Message

geoff freestone - 11 Jun 2008 17:14 GMT

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?

Reply to this Message

Don Guillett - 11 Jun 2008 20:55 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

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

Reply to this Message

Rate this thread: