Pivot Table Calculated Items - suppress blank lines

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

graemeW - 30 Oct 2006 17:25 GMT

Hi there,

I've just used calculated items for the first time to calculate a
variance between two specific ledger types (Ledger Type being one of my
column fields).

This works nicely, but it fills in a zero for all rows regardless of
whether they have any data or not. The net result is that rows that
were previously nicely supressed due to no data pop back up with a 0 in
the variance column.

Any ideas?

Reply to this Message

Roger Govier - 30 Oct 2006 18:33 GMT

Hi Graeme

Not sure whether this will stop the suppressed rows appearing, but it
will suppress the zero's.
Wrap your item formula with an IF()
something like
=IF(AND(ISNUMBER(S/L),ISNUMBER(P/L)),S/L-P/L,"")

Signature

Regards

Roger Govier

> Hi there,
>
[quoted text clipped - 10 lines]
>
> Any ideas?

Reply to this Message

graemeW - 31 Oct 2006 12:28 GMT

thanks Roger, nice try....but I guess that because its totalling its
expecting something numeric, and i just get #value where i previously
had 0.

> Hi Graeme
>
[quoted text clipped - 23 lines]
> >
> > Any ideas?

Reply to this Message

Roger Govier - 31 Oct 2006 13:08 GMT

Hi Graeme

I assumed that what we were talking about was numeric data.
How can you be calculating variance in a PT on text values?

Signature

Regards

Roger Govier

> thanks Roger, nice try....but I guess that because its totalling its
> expecting something numeric, and i just get #value where i previously
[quoted text clipped - 30 lines]
>> >
>> > Any ideas?

Reply to this Message