Pivot table and Calculated item field
Posted by Jon H on January 15, 2002 8:02 AM
I have a large pivot table that has a calculated item field (in my case, the calc item is col.1-col.2).
When I use the calculated item field, it lists every calculated item, regardless of whether there is actually data in the row it is calucalting. So I end up with the pivot table listing a lot of zero info, and it makes for a table a lot longer than it needs to be.
Is there a way the extra lines with zero in the calc item field ca be made to be hidden?
Thanks!
Posted by Mark W. on January 15, 2002 8:56 AM
I'm struggling to understand your problem. As I
understand it you've added a column to your
PivotTable. This new column is a Calculated Item
based on the difference of the 1st and 2nd Pivot-
Table columns. Somehow the addition of this
new column has made your PivotTable "alot longer".
I don't see how this can happen since the new
Calculated Item will only produce a value for
existing rows. Please explain further or provide
a simple example that illustrates your problem.
Posted by Jon H on January 15, 2002 10:05 AM
You would think the pivot table would only produce values for existing rows, but it calculates a value for every line in the pivot table, regardless of whether there is data in the line item. For example, say the pivot table is calcing the following data:
Acct Num Actual Budget
61100- 10 5
61200- 0 0
The result of a pivot table would only list the first row (the second would not show, as there is no actual data to summarize on the report). When I add a calculated field (budget-actual), ALL rows show up on the table, even those that start out as not showing. It looks like the calc item now has a "0" value (becuase 0-0=0). And that is causing all account numbers to show, even when there is no value in them.
Thanks for your response.
Jon
Posted by Mark W. on January 15, 2002 11:28 AM
I still cannot reproduce your configuration. Is
'Acct Num' 61200 included on a row in your data
list or database? By chance have you checked the
"Show items with no data" option for the 'Acct Num'
field?
