Pivot Table Help

[ktr73]

Hi -

Not sure if this is the right spot, but seems somewhat appropriate. I
have a series of data that contains records extracted from a database
like so:

cust# | start date | ... | current_customer? | ...

So the "current_customer?" column contains a "1" if the customer is a
current customer and a "0" otherwise (i.e., they have left). I'm
trying to put together a chart so that we can graph the attrition as a
percentage of total # customers enrolled. So for example, you might
have:

start month | attrition | total customers acq'd | attrition %

Where start month would be the month the customer signed up, attrition
would be the number of customers who have quit thus far (e.g., 5) and
the total customers acquired would be the current number of customers
+ attrited customers (e.g., 100 would mean that 95 customers are still
active). I have a pivot table that breaks this out by the
"current_customer?" flag and the pivot table is a "% of column" so
that the attrition properly shows up as a percentage of total
customers. Problem is, the chart doesn't just show the attrition % -
it also shows the current # of active customers. When I try to "hide"
the current customer total in the pivot table, the "% of row" number
now becomes a percentage of total attrition rather than a percentage
of acquired customers. I've tried hiding the row, but pivot charts
apparently don't follow standard charting habits of MS Excel.

Sorry if this is the wrong spot, but please let me know if you have
any ideas on how I can fix this. Thanks so much!

Kevin

 

[ryguy7272]

Yeah, you can't change a PivotTable, but you can copy/paste special, values
of that PivotTable, using code similar to that below:

Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Then, you can do whatever you want to the 'values' that remain.

Regards,
Ryan---