SURVIVAL.LIFETABLE OpenBI
An actuarial life table, in intervals of a width.
Group: Survival analysis. OpenBI’s own: DAX has no function of this name.
Syntax
SURVIVAL.LIFETABLE ( <table>, <duration>, <event>, <width> [, <group>] )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to follow, one a subject such as a customer, a policy or a machine: a table of the model or a table expression, as the filters leave it. |
duration | Number evaluated for each row | How long each row was followed: until its event, or until we stopped seeing it. In any unit (days, months, years), the same for every row. |
event | Number evaluated for each row | 1 or TRUE when the row's duration ended in the event (the customer left); 0 or FALSE when it did not, so all we know is that it lasted at least that long (censored). |
width | Number | How long each interval is, such as 1 year or 30 days; above 0. |
group | Expression optional, evaluated for each row | Splits the rows into groups, such as plans or regions, each with its own result. Left out, all the rows are one group. |
Returns
Table. A row an interval, per group. The columns: Group (when grouped); Start and End; Entered, Withdrawn and Events; Exposed; Probability; Survival and its Std error; Density; and Hazard.
Remarks
- The actuarial life table, as R's KMsurv computes it: intervals from 0 of the width given, each from its Start up to, not including, its End. A duration that falls on an End counts in the next interval.
- Entered counts the rows still followed at the interval's start; Withdrawn those censored in it, Events those that had the event in it.
- Exposed is Entered less half the withdrawn, who are taken to leave halfway. Probability is Events over Exposed: the chance of the event within the interval for a row that reached it.
- Survival is the share still without the event at the interval's start, with Greenwood's Std error. Density is the share that has the event in the interval, per unit of time; Hazard the events per unit of time over those at risk in it.
- Every group gets the same intervals, up to the longest duration; in an interval nobody entered, the rates are blank.
- Rows with a blank duration or a blank event are left out. An event other than 0, 1, TRUE or FALSE is an error.
Compared with DAX
An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.
Examples
Five-month intervals
EVALUATE
VAR Customers =
DATATABLE (
"Plan", STRING, "Months", INTEGER, "Left", INTEGER,
{
{ "Basic", 2, 1 }, { "Basic", 3, 0 }, { "Basic", 5, 1 }, { "Basic", 7, 1 }, { "Basic", 8, 0 },
{ "Plus", 4, 1 }, { "Plus", 9, 0 }, { "Plus", 11, 1 }, { "Plus", 12, 1 }, { "Plus", 15, 0 }
}
)
RETURN
SELECTCOLUMNS (
SURVIVAL.LIFETABLE ( Customers, [Months], [Left], 5 ),
"Start", [Start],
"End", [End],
"Entered", [Entered],
"Events", [Events],
"Withdrawn", [Withdrawn],
"Exposed", [Exposed],
"Probability", [Probability],
"Survival", [Survival],
"Hazard", [Hazard]
)| Start | End | Entered | Events | Withdrawn | Exposed | Probability | Survival | Hazard |
|---|---|---|---|---|---|---|---|---|
| 0 | 5 | 10 | 2 | 1 | 9.5 | 0.210526315789474 | 1 | 0.0470588235294118 |
| 5 | 10 | 7 | 2 | 2 | 6 | 0.333333333333333 | 0.789473684210526 | 0.08 |
| 10 | 15 | 3 | 2 | 0 | 3 | 0.666666666666667 | 0.526315789473684 | 0.2 |
| 15 | 20 | 1 | 0 | 1 | 0.5 | 0 | 0.175438596491228 | 0 |
Half-years, by plan
No Basic customer is left after month 12, so that interval's rates are blank.
EVALUATE
VAR Customers =
DATATABLE (
"Plan", STRING, "Months", INTEGER, "Left", INTEGER,
{
{ "Basic", 2, 1 }, { "Basic", 3, 0 }, { "Basic", 5, 1 }, { "Basic", 7, 1 }, { "Basic", 8, 0 },
{ "Plus", 4, 1 }, { "Plus", 9, 0 }, { "Plus", 11, 1 }, { "Plus", 12, 1 }, { "Plus", 15, 0 }
}
)
RETURN
SELECTCOLUMNS (
SURVIVAL.LIFETABLE ( Customers, [Months], [Left], 6, [Plan] ),
"Group", [Group],
"Start", [Start],
"Entered", [Entered],
"Events", [Events],
"Probability", [Probability],
"Survival", [Survival],
"Density", [Density]
)| Group | Start | Entered | Events | Probability | Survival | Density |
|---|---|---|---|---|---|---|
| Basic | 0 | 5 | 2 | 0.444444444444444 | 1 | 0.0740740740740741 |
| Basic | 6 | 2 | 1 | 0.666666666666667 | 0.555555555555556 | 0.0617283950617284 |
| Basic | 12 | 0 | 0 | (blank) | 0.185185185185185 | (blank) |
| Plus | 0 | 5 | 1 | 0.2 | 1 | 0.0333333333333333 |
| Plus | 6 | 4 | 1 | 0.285714285714286 | 0.8 | 0.0380952380952381 |
| Plus | 12 | 2 | 1 | 0.666666666666667 | 0.571428571428572 | 0.0634920634920635 |