Churn & Retention Calculation using the ABOVE function

[#1]

Hi,

I'm attempting to calculate customer retention for a forecast on a dataset over 5 investment periods. The intention here is to calculate the number of customers retained over 5 years based on a % rate of churn/losses (AS_CHURN).

The formula i'm using in my expression is:

(IF(INVESTMENT_PERIOD=1, 1
, IF(INVESTMENT_PERIOD=2, (1-ABOVE(AVG(AS_CHURN),1,1))
, IF(INVESTMENT_PERIOD=3, (1-ABOVE(AVG(AS_CHURN),1,1))*(1-ABOVE(AVG(AS_CHURN),2,1))
, IF(INVESTMENT_PERIOD=4, (1-ABOVE(AVG(AS_CHURN),1,1))*(1-ABOVE(AVG(AS_CHURN),2,1))*(1-ABOVE(AVG(AS_CHURN),3,1))
, IF(INVESTMENT_PERIOD=5, (1-ABOVE(AVG(AS_CHURN),1,1))*(1-ABOVE(AVG(AS_CHURN),2,1))*(1-ABOVE(AVG(AS_CHURN),3,1))*(1-ABOVE(AVG(AS_CHURN),4,1))
, 0
)
)
)
)
))

This formula does calculate retention correctly however when this expression is required to calculate over a large amount of data the performance and memory use is extremely poor. I have analysed this and it seems to be purely down to the use of the ABOVE function. Can anyone think of an alternative method? (Note for my model it won't be helpful merely to move this calculation into the load script!).

Regards,

Chris

[jonathandienst]

Chris

2 things I would try, as I expect that the performance drain is either IFs, or the AVG(AS_CHURNs),

1. Try changing the expression to a PICK statement:

PICK(INVESTMENT_PERIOD, 1, (1-ABOVE(AVG(AS_CHURN),1,1)), etc etc....)

2. Try something like this:

(1 - If(INVESTMENT_PERIOD > 1, ABOVE(AVG(AS_CHURN),1,1))) *
(1 - If(INVESTMENT_PERIOD > 2, ABOVE(AVG(AS_CHURN),2,1))) *
(1 - If(INVESTMENT_PERIOD > 3, ABOVE(AVG(AS_CHURN),3,1))) *
(1 - If(INVESTMENT_PERIOD > 4, ABOVE(AVG(AS_CHURN),4,1)))

(check brackets)

I don't know if these will help you, but give them a try.

Jonathan

[jonathandienst]

Chris

Another idea is to create a hidden column (straight table) or invisible bar/line (bar/line chart) for AVG(AS_CHURN), named (for example) AVE_AS_CHURN, and change the expression so that instead of calulating the AVE each time, it is getting ABOVE(AVE_AS_CHURN,n,1).

Just some ideas

Jonathan

Can't do this in a pivot table though.

[#4]

Thanks for the reply Jonathan,

I like the pick function, makes the code clearer at least. However it doesn't seem to perform any better than the nested IF

I'll try out a few other ideas and post back if i solve it.

Cheers,

C.