Pivot Table Calculated Items - suppress blank lines

G

graemeW

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

Roger Govier

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,"")
 
G

graemeW

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

Roger Govier

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