Cohort Analysis & Churn in Qlikview

[#1]

Hi i´m facing a Problem:

Let´s say i have following Data:

EventDate	SignUpDate	UserId	CountSessions
01.01.2015	01.01.2015	1	1
01.01.2015	01.01.2015	2	1
02.01.2015	01.01.2015	1	1
03.01.2015	01.01.2015	2	1
....		....	....

with a lot more users and entries in a bigger timerange

what i´m trying to achieve is a cohort analysis, to check for every month if a user has still entries for "CountSessions"

So the Result should be like:

TimeRange	Jan-2015 Count(UserId with "CountSessions")	Feb-2015 Count(UserId with "CountSessions")	Mar-2015 Count(UserId with "CountSessions")	Apr-2015 Count(UserId with "CountSessions")
Jan-2015 SignUp Users	500	450	300	250
Feb-2015 SignUp Users	-	550	450	380
Mar-2015 SignUp Users	-	-	350	300
...	...	...	...	...

So I want to have a Chart where i can See for a given Timerange how many Users, who signed up in a given Month (e.g. Jan-2015 = 500) and how many Users of these have still Session Entries in the following Months.

So for this example the Result is: 50% of the Users who signed in Jan-2015 are still active in April-2015 (250 User/500 User).

I hope somebody can help me

Cheers

Steve

[pokassov]

Hi!

You can use something like this:

1. create new table with UserID and SingUpDate.

2. remove field SignUpDate from your table

3. add pivot table with dimentions MonthName(EventDate), MonthName(SingUpDate) and expression count(distinct UserID)

4. press on expression and write

if( count(distinct UserID)/count({$<SignUpDate=>} distinct UserID)>=0.5, red())

[#3]

Hi Sergey,

thank you for your fast reply.

I only used the color to highlight my issue.

I tried it and it works finde but now i have another question:

i only want to count Users for a given Month who have at least 1 Session in this Month and the following 2 Months

So, for

Jan:  if(user has at least one Session in Jan, Feb, Mar, then count him)

Feb:  if(user has at least one Session in Feb,Mar,Apr, then count him)

Thank you

[#4]

And I only want to count the users for one Row who hat their SignUp in this Month.

e.g. In Row "Jan-2015 SignUp Users": COUNT only the Users for each EventsMonth Column who have the SignUp in Jan

[pokassov]

I don't understand your task completely. But...

t1:

load distinct

     UserID,

     SignUpDate,

     1               As MonthCnt

resident Data;

concatenate (t1)

load distinct

     UserID,

     SignUpDate,

     1               As MonthCnt

resident Data

Where MonthStart(AddMonth(EventDate,-1)=MonthStart(SignUpDate);

concatenate (t1)

load distinct

     UserID,

     SignUpDate,

     1               As MonthCnt

resident Data

Where MonthStart(AddMonth(EventDate,-1)=MonthStart(SignUpDate);

t2:

load

     UserID,

     SignUpDate

where MonthCnt=3;

load

     UserID,

     SignUpDate,

     sum(MonthCnt)     as MonthCnt

resident t1

group by

     UserID,

     SignUpDate;

drop table t1;

[#6]

HI Sergey,

i try to explain: Let´s say we have two users

user1 has SignUp on 01.01.2015

user2 has SignUp on 01.02.2015

assuming Users have Sessions in all Months

So Result should be like:

TimeRange	EventMonth Jan-2015	EventMonth Feb-2015
SignUp Jan-2015	1	1
SignUp Feb-2015	1	1

So User1 must not be counted in Row "SignUp Feb-2015" and User2 must not be counted in Row "SignUp Jan-2015".

I hope it makes it a bit clearer

[pokassov · Accepted solution]

Plz see attachment.

[#8]

thank you for your Help

[dhavalvyas]

Hii Sergey I also want help from you for Cohort Analysis...

PFA link of raw data and output in image..Please help me out with its logic..want output as following image..

Thanks in Advanced.

[dhavalvyas]

https://community.qlik.com/thread/300257