4 Replies Latest reply on Sep 3, 2013 2:11 PM by Jim Wahl

How to calculate survival curve / measure as % of total from other count

I'm trying to calculate a survival / death / lifetime curve from source data similar to what's below:

SOURCE DATA:

Customer IDConversion dateCancel date
11/1/2013NULL
21/2/2013NULL
31/3/2013NULL
41/4/2013NULL
51/5/2013NULL
61/6/20132/1/2013
71/7/20133/1/2013
81/8/20134/1/2013
91/9/20135/1/2013
101/10/20136/1/2013
112/1/2013NULL
122/2/2013NULL
132/3/2013NULL
142/4/2013NULL
152/5/2013NULL
162/6/20132/1/2013
172/7/20133/1/2013
182/8/20134/1/2013
192/9/20135/1/2013
202/10/20136/1/2013

DESIRED OUTPUT:

Conversion classOriginal class size% remaining
1/1/20132/1/20133/1/20134/1/20135/1/20136/1/2013
1/1/201310100%90%80%70%60%50%
2/1/20131090%80%70%60%50%

I can calculate both the count of original class size and a count of customers remaining by month separately, but cannot for the life of me figure out (a) how to get them to show up at the same time, and (b) how to create the formula to calculate the percentage surviving, i.e. a count of users remaining by month over the original conversion class size.

Any help is much appreciated - sample data above attached.

• 1. Re: How to calculate survival curve / measure as % of total from other count

This is as far as I can get it.  If anyone can help I'd very much appreciate it.  Thanks!

• 2. Re: How to calculate survival curve / measure as % of total from other count

Hi Tim,

Below is a rough draft. I created three calculated fields:

Note that when you enter these formulas, it's a good practice to set the Default Compute Using. In this case it should be set to Cancel date, which is the dimension the table calcs are being compute over below.

Original Class Size =

```// When set to compute using Cancel date, this will count the
// number of customer IDs, regardless of Cancel date.
TOTAL(COUNT([Customer ID]))
```

Remaining =

```[Original Class Size] - RUNNING_SUM(COUNT([Customer ID]))
```

Remaining (%) =

```( [Original Class Size] - RUNNING_SUM(COUNT([Customer ID])) ) /
[Original Class Size
```

Now you can drop these into a view. Remaining will have a Null column. Drag this to the end and select hide (not exclude, since you need these rows to calculate the original class size).

Jim

• 3. Re: How to calculate survival curve / measure as % of total from other count

Jim - This was a tremendous help.  As a noob I didn't (possibly don't) fully understand the subtleties of the measure names/measure values shelves and table calculations.  Your post of a functioning workbook and clear explanation of the necessary functions was a huge help in my understanding of this.

For anyone reading this down the line, the only challenge I had to the above was moving the "Null" column to the right of the table, so the running sum wouldn't pick up still active customers.  Being relatively ignorant, for awhile I was wondering why the "Remaining" calculation was off.  Obviously it wasn't!

Thanks again!

• 4. Re: How to calculate survival curve / measure as % of total from other count

You're welcome. Thanks for the additional clarification on the null column.