topic Re: Cohort Analysis & Churn in Qlikview in QlikView

Cohort Analysis & Churn in Qlikview

Tue, 13 Oct 2015 09:30:31 GMT

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

Re: Cohort Analysis & Churn in Qlikview

pokassov — Tue, 13 Oct 2015 10:08:03 GMT

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())

Re: Cohort Analysis & Churn in Qlikview

Tue, 13 Oct 2015 10:59:30 GMT

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

Re: Cohort Analysis & Churn in Qlikview

Tue, 13 Oct 2015 11:09:07 GMT

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

Re: Cohort Analysis & Churn in Qlikview

pokassov — Tue, 13 Oct 2015 11:21:20 GMT

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;

Re: Cohort Analysis & Churn in Qlikview

Tue, 13 Oct 2015 11:31:12 GMT

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

Re: Cohort Analysis & Churn in Qlikview

pokassov — Tue, 13 Oct 2015 12:53:23 GMT

Plz see attachment.

Re: Cohort Analysis & Churn in Qlikview

Tue, 13 Oct 2015 13:04:41 GMT

thank you for your Help

Re: Cohort Analysis & Churn in Qlikview

dhavalvyas — Sun, 06 May 2018 06:11:31 GMT

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.

Re: Cohort Analysis & Churn in Qlikview

dhavalvyas — Sun, 06 May 2018 06:12:47 GMT

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

Re: Cohort Analysis & Churn in Qlikview

dhavalvyas — Thu, 17 May 2018 07:02:32 GMT

HI Sergey can you help me out please..as I m trying cohort analysis from lsat one months..

Thanks in Advanced