D

#### Dave Zed

I have a tricky little problem that I need to solve and I really need some

help.

I'm trying to study attrition rates and get the data onto a graph, the way I

want it.

So I need it set up in the sheet the way I want it before the graph Wizard

gets a look at it.

I have a version of this that I do manually and it has encountered a fair

level of success so now the boss is requesting the same thing with all sorts

of other criteria mixed in and updated monthly so I really need now to get

this automated yet get the data into shape as I have it now.

I don't like macros and I don't like VBA and I have used neither since I got

my head around auto-updating Pivot tables. But I can't crack this one. I'll

go back to VBA if I have to.

Here goes,

My data records the date of the first payment and latest payment for each

client.

First payment is equivalent to recruitment date.

Latest payment is the Client's most recent sign of life in my base.

If a client has not made a payment for the last 12 months I can consider him

inactive.

My external data is in a text file and, to simplify, I have these columns:

Client ID

First payment Date

Latest payment Date

Days betwen First and Latest payment Date

I could add in other stuff if necessary.

I can group the dates into Years and Quarters so I end up with a Pivot table

like this

where I am counting

the number of Client IDs

whose First payment was in particular Quarter and

whose Latest payment was in a particular Quarter.

Latest payment Quarter

2000/Q1 2000/Q2 2000/Q3

2000/Q4 .

First

2000 - Q1 10 12 22

41 .

2000 - Q2 74 52

63 .

2000 - Q3 9

15 .

2000 - Q4

2001 - Q1 etc.

So far so good. but not good enough. Two things are still wrong with this.

Firstly the cell data is sliding away to the right as we descend in the

table.

That's normal because Clients can't make a payment before their First

payment.

So the way around this is to avoid calculating calendar dates for the

payments.

I calculate the number of days between the First and Latest payment which I

then convert into number of Quarters, by dividing by 91 and retaining the

whole number.

So now we have the number of Quarters between the First and Latest payment,

relative to each Client.

Latest payment in Client's Quarter

1st 2nd

3rd 4th .

First

2000 - Q1 10 12 22

41

2000 - Q2 74 52 63

2000 - Q3 9 15

2000 - Q4

2001 - Q1 etc.

Here too, we have a hole in the table, the data is now sliding away to the

left. That's normal and OK, because a client can't make his last payment in

his third Quarter if he's only been a client for two Quarters.

Things are easier to read too: we can now compare cells that are in the same

column, stacked vertically instead of having to read diagonally! We're

dealing now with the Client's "age" expressed in Quarters. Age in my base,

that is.

But now here's the second problem.

Let's compare 2000/Q1 to 2000/Q2.

If you look closely you'll see that the figures are a lot higher in Q2. So

you'd be right in thinking that more Clients recruited in 2000 - Q2 than in

2000 - Q1 made their Latest payment in their 1st Quarter, 74 instead of 10.

True, but what you don't know is that in Q2 there had been a massive

marketing effort and the number of people who became Clients and made their

First payment in 2000 - Q2 was pretty massive too. So it is normal that the

data be consistently higher than the preceding Quarter because recruitment

had gone up since the preceding Quarter too.

To be able to compare the data regardless of the number of Clients

recruited, I need to have the percentage of people making their Latest

payment any given Quarter of their life in my base. But percentage of what?

Percentage of the total number of Clients who made their First payment in

any given Quarter.

And I can't get Excel to do that in a Pivot Table.

I can the numbers presented as percentages of the total for the line but

that is not enough.

What I need is this :

Latest payment in Client's Quarter

Total 1st 2nd

3rd 4th .

First

2000 - Q1 85 10 12 22

41

2000 - Q2 189 74 52 63

2000 - Q3 45 30 15

2000 - Q4

2001 - Q1 etc.

and then this :

Latest payment in Client's Quarter

Total 1st 2nd

3rd 4th .

First

2000 - Q1 100% 12% 14% 26%

48%

2000 - Q2 100% 39% 28% 33%

2000 - Q3 100% 67% 33%

2000 - Q4

2001 - Q1 etc.

And that would look great in a line graph. Each group of Clients starts off

as 100% of itself and will eventually trail off as they drop out, move on

and shrink!

So, can a Pivot table handle that or will I have I have to improve my data

file, go back to VBA, get into OLAP cubes or What ??!!!

Thanks in advance, and if you got this far, thanks for your patience,

Dave