Pivot Tables & Set Analysis: Survival Triangles

[#1]

So, I have a data set that looks (roughly) like this:

99 90 80 70 60

99 92 83 74 00

95 87 72 00 00

86 81 00 00 00

97 00 00 00 00

Where each new row is a quarter (eg. 2008Q1, 2008Q2), each new column is the # of terms it has survived (0,1,2...) and each data element is the total # of policies remaining that were new beginning in that quarter.

What I want to do is add a field for a policy weighted column average - i.e. (# of policies remaining that have survived X terms) / (# of policies that began in terms at least X terms ago).

Using the top() and first() expressions, I have so far created a pivot table where every field value is the correct answer - for the sample table above, it'd be:

100% 89.9% 80.2% 72.7% 60.6%

100% 89.9% 80.2% 72.7% 60.6%

100% 89.9% 80.2% 72.7% 60.6%

100% 89.9% 80.2% 72.7% 60.6%

100% 89.9% 80.2% 72.7% 60.6%

However, putting an object like the above on a dashboard for publication is ugly, and I want to avoid it. I would prefer a one-line summary, and I think I need a set analysis expression to do it, but I'm having trouble formulating it. How do you formulate (# of policies remaining) / (# of policies that could potentially have gotten this far) without a 2-dimensional table?

[#2]

Specifically, I think I want the result when terms survived = 3 to be:

sum of ( all quarters at terms = 0 where (terms = 3 =/= 0))

if that makes more sense.

[#3 · Accepted solution]

Wound up doing it the ugly way. Got the denominator with:

if(terms_survived = 0, rangesum(last(sum({$<cohort2 = {"<=20"}>} ind),1,21)),

if(terms_survived = 1, rangesum(last(sum({$<cohort2 = {"<=19"}>} ind),1,21)),

if(terms_survived = 2, rangesum(last(sum({$<cohort2 = {"<=18"}>} ind),1,21)),

if(terms_survived = 3, rangesum(last(sum({$<cohort2 = {"<=17"}>} ind),1,21)),

if(terms_survived = 4, rangesum(last(sum({$<cohort2 = {"<=16"}>} ind),1,21)),

if(terms_survived = 5, rangesum(last(sum({$<cohort2 = {"<=15"}>} ind),1,21)),

if(terms_survived = 6, rangesum(last(sum({$<cohort2 = {"<=14"}>} ind),1,21)),

if(terms_survived = 7, rangesum(last(sum({$<cohort2 = {"<=13"}>} ind),1,21)),

if(terms_survived = 8, rangesum(last(sum({$<cohort2 = {"<=12"}>} ind),1,21)),

if(terms_survived = 9, rangesum(last(sum({$<cohort2 = {"<=11"}>} ind),1,21)),

if(terms_survived = 10, rangesum(last(sum({$<cohort2 = {"<=10"}>} ind),1,21)),

if(terms_survived = 11, rangesum(last(sum({$<cohort2 = {"<=9"}>} ind),1,21)),

if(terms_survived = 12, rangesum(last(sum({$<cohort2 = {"<=8"}>} ind),1,21)),

if(terms_survived = 13, rangesum(last(sum({$<cohort2 = {"<=7"}>} ind),1,21)),

if(terms_survived = 14, rangesum(last(sum({$<cohort2 = {"<=6"}>} ind),1,21)),

if(terms_survived = 15, rangesum(last(sum({$<cohort2 = {"<=5"}>} ind),1,21)),

if(terms_survived = 16, rangesum(last(sum({$<cohort2 = {"<=4"}>} ind),1,21)),

if(terms_survived = 17, rangesum(last(sum({$<cohort2 = {"<=3"}>} ind),1,21)),

if(terms_survived = 18, rangesum(last(sum({$<cohort2 = {"<=2"}>} ind),1,21)),

if(terms_survived = 19, rangesum(last(sum({$<cohort2 = {"<=1"}>} ind),1,21)),

if(terms_survived =20 , rangesum(last(sum({$<cohort2 = {"<=0"}>} ind),1,21)),

)))))))))))))))))))))