Cohort Analysis - Display Rentention as Percentage from Month 0

Fri, 11 Dec 2015 07:47:03 GMT

Hello gurus,

I am trying to create a chart which will allow the users to better understand if retention on cohort months 1,2,etc is improving every month.

To better achieve this instead of displaying a simple COUNT DISTINCT on CustomerID I would like to show a percentage of how many players are still active in cohort month N versus the ones that began in month 0.

Below I have attached a simple example of what I am trying to achieve in table format.

Registration Year Month	Month Cohort	Active Customers
2015-01	0	100%
2015-01	1	50%
2015-01	2	45%
2015-02	0	100%
2015-02	1	60%
2015-03	0	100%

Registration Year Month and Month Cohort are precalculated and exist already in the Data model.

To achieve this I am trying to create a temporary second column which no matter the Month Cohort will always return the number of active customers in Month Cohort 0 for users of that registered period. Table below shows what I am trying to achieve for now.

Registration Year Month

Month Cohort

Active Customers

Active Customers (Month 0)

2015-01

1000

2015-01

500

1000

2015-01

450

1000

2015-02

2000

2015-02

1200

2000

2015-03

3000

Active Customers is calculated using the simple expression "COUNT(DISTINCT ActiveCustomer)"

I attempted to achieve the second column using the expression "COUNT({<[MonthCohort]={0}>} DISTINCT ActiveCustomer)" but this is not having the desired result. The result I am getting can be seen below.

Registration Year Month

Month Cohort

Active Customers

Active Customers (Month 0)

2015-01

1000

2015-01

500

2015-01

450

2015-02

2000

2015-02

1200

2015-03

3000

I am trying to calculate the second column so then I can just do the division to get the precentage of retained customers.

I appreciate any help any of you can give me.

Thanks,

Malcolm

Re: Cohort Analysis - Display Rentention as Percentage from Month 0

Fri, 11 Dec 2015 10:50:07 GMT

Solved it myself by using the TOTAL expression

Expression used is below:

COUNT(TOTAL <CUSTOMER.RegYearMonth> {<MonthCohort={0}>} DISTINCT ActiveCustomer)

topic Re: Cohort Analysis - Display Rentention as Percentage from Month 0 in QlikView

Cohort Analysis - Display Rentention as Percentage from Month 0

Re: Cohort Analysis - Display Rentention as Percentage from Month 0