Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

Cohort Analysis & Churn in Qlikview

Hi i´m facing a Problem:

Let´s say i have following Data:

EventDate
SignUpDateUserIdCountSessions
01.01.201501.01.201511
01.01.201501.01.201521
02.01.201501.01.201511
03.01.201501.01.201521
............

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 Users500450300250
Feb-2015 SignUp Users-550450380
Mar-2015 SignUp Users--350300
...............

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

1 Solution

Accepted Solutions
pokassov
Valued Contributor

Re: Cohort Analysis & Churn in Qlikview

Plz see attachment.

10 Replies
pokassov
Valued Contributor

Re: Cohort Analysis & Churn in Qlikview

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

Not applicable

Re: Cohort Analysis & Churn in Qlikview

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

Not applicable

Re: Cohort Analysis & Churn in Qlikview

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
Valued Contributor

Re: Cohort Analysis & Churn in Qlikview

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;

Not applicable

Re: Cohort Analysis & Churn in Qlikview

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:

TimeRangeEventMonth Jan-2015EventMonth Feb-2015
SignUp Jan-201511
SignUp Feb-201511

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
Valued Contributor

Re: Cohort Analysis & Churn in Qlikview

Plz see attachment.

Not applicable

Re: Cohort Analysis & Churn in Qlikview

thank you for your Help

dhavalvyas
New Contributor III

Re: Cohort Analysis & Churn in Qlikview

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.

Screenshot (102).png

dhavalvyas
New Contributor III

Re: Cohort Analysis & Churn in Qlikview