How to save "Empty" as Pivot Table calculated item result, or else...??

  • Thread starter Thread starter G Lykos
  • Start date Start date
G

G Lykos

Not sure if this is the right forum, but -

Have a pivot table in which I've created a calculated item. The data set
contains several different data groups, and I use an attribute field as a
page field to select the group of interest. So far, so good.

Then I create a calculated field of the type Profit by subtracting Cost from
Price. When I do so, the page field no longer selects the group of
interest - rather, the whole data set shows up, regardless of page field
election.

In looking at the calculation results, if I subtract Cost [when tests True
for Empty] from Price [also when tests True for Empty], I nonetheless get 0
for the resulting Profit. In other words, using basic formula Profit -
Cost, I get 0 as a result when Profit and Cost are empty. This appears to
be causing every record to appear when using a page field, presumably
because every record has a non-empty element.

Using the formula (Price + Cost <> 0, Price - Cost, ""), I get Error# for
Profit when Price and Cost are empty, and every record still appears when
using a page field. One thought was that perhaps there is a way to set up a
calculated item formula such that it stores "Empty" rather than 0 or "".
However, if there is, I can't find it anywhere. I could perhaps write some
code to do a record by record check for the error and make that item
invisible, but would much prefer to resolve this using standard methods
rather than adding a custom work-around.

Ideas??

Thanks,
George
 
Hi G,

If all you want to do is suppress the display of Errors you might try
choosing Pivot Table, Table Options, and check For error values show, and
leave the box to the right of it empty.

If this doesn't work you could send me a sample file with the problem and an
explaination of what you want to see.
 
That's one of the disadvantages of using calculated items.
Instead, in the source data, create a Profit column.
Enter a formula to test for Price or Cost, and multiply by the amount.
For example, with amount in column J:
=J2*(IF(D2="Price",1,-1))
Remove the calculated item, and add the Profit field to the pivot table.

G said:
Not sure if this is the right forum, but -

Have a pivot table in which I've created a calculated item. The data set
contains several different data groups, and I use an attribute field as a
page field to select the group of interest. So far, so good.

Then I create a calculated field of the type Profit by subtracting Cost from
Price. When I do so, the page field no longer selects the group of
interest - rather, the whole data set shows up, regardless of page field
election.

In looking at the calculation results, if I subtract Cost [when tests True
for Empty] from Price [also when tests True for Empty], I nonetheless get 0
for the resulting Profit. In other words, using basic formula Profit -
Cost, I get 0 as a result when Profit and Cost are empty. This appears to
be causing every record to appear when using a page field, presumably
because every record has a non-empty element.

Using the formula (Price + Cost <> 0, Price - Cost, ""), I get Error# for
Profit when Price and Cost are empty, and every record still appears when
using a page field. One thought was that perhaps there is a way to set up a
calculated item formula such that it stores "Empty" rather than 0 or "".
However, if there is, I can't find it anywhere. I could perhaps write some
code to do a record by record check for the error and make that item
invisible, but would much prefer to resolve this using standard methods
rather than adding a custom work-around.

Ideas??

Thanks,
George
Click to expand...
 
Back
Top