D
Dave Zed
Hi,
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
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