Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
n1ef5ng1
Creator
Creator

Cohort Analysis

Hi Guys,

I am trying to do a cohort analysis in Qlikview. I am getting the numbers but the placement of the data are wrong.

Dimension (201510) refers to the joined date of the new customers. 0 1 2 3 are based on YYYYMM (Current Year Month) to determine. However I would like 0 (expression) to take reference of the YYYYMM from Cohort first YYYYMM data. Meaning 0 in 201510 refers to 201510 and 1 refers to 201511 in YYYYMM. But in 201511 , 0 refers to 201511 and 1 refers to 201512.

Is this achievable through dimensionality.    

Cohort_First_YYYYMM0123
20151021031
20151117970
2015121239900
201601133000
1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

Unfortunately i cannot upload the solution (client security policy), but this is the load script:

Data:

LOAD *,

  Dual('Mth: ' & (OrderSeq - FirstSeq), OrderSeq - FirstSeq) As Vintage

;

LOAD *,

  Year(YYYYMM) * 12 + Month(YYYYMM) As OrderSeq,

  Year(Cohort_First_YYYYMM) * 12 + Month(Cohort_First_YYYYMM) As FirstSeq

;

LOAD Cust_Status,

  Date#(YYYYMM, 'yyyyMM') As YYYYMM,

  Date#(Cohort_First_YYYYMM, 'yyyyMM') As Cohort_First_YYYYMM,

  [Order date],

  Customer_Unique_ID

FROM Cohort.xlsx

(ooxml, embedded labels, table is Sheet1);

Now create a pivot with the dimensions Cohort_First_YYYYMM and Vintage

with the expression Count(distinct Customer_Unique_ID).

Drag the vintage dimension to the top and click on "Suppress When Value is Null" on Chart Properties | Dimension

Set the Null and Missing symbols to 0 in Chart Properties | Presentation if you don't want - for the missing values:


t1.png

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

7 Replies
n1ef5ng1
Creator
Creator
Author

Desired output

     

Cohort_First_YYYYMM0123
20151031012
2015119771-
20151239912--
201601133---
jonathandienst
Partner - Champion III
Partner - Champion III

I suggest that you post a qvw document that inline loads some sample data (or loading from an uploaded file) with some representative data. I think i understand what you want (like vintage curves), but I dont have the capacity to create everything from scratch.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
n1ef5ng1
Creator
Creator
Author

Attached.

Desired output is

   

Cohort_First_YYYYMMMth:0Mth:1Mth:2Mth:3
2015102012
2015112110
201512331200
201601133000

able to paste the expression over here if is able to solve.

thanks

jonathandienst
Partner - Champion III
Partner - Champion III

Please upload the source spreadsheet as well to develop and test the load script.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
n1ef5ng1
Creator
Creator
Author

Attached with the data and qw

jonathandienst
Partner - Champion III
Partner - Champion III

Unfortunately i cannot upload the solution (client security policy), but this is the load script:

Data:

LOAD *,

  Dual('Mth: ' & (OrderSeq - FirstSeq), OrderSeq - FirstSeq) As Vintage

;

LOAD *,

  Year(YYYYMM) * 12 + Month(YYYYMM) As OrderSeq,

  Year(Cohort_First_YYYYMM) * 12 + Month(Cohort_First_YYYYMM) As FirstSeq

;

LOAD Cust_Status,

  Date#(YYYYMM, 'yyyyMM') As YYYYMM,

  Date#(Cohort_First_YYYYMM, 'yyyyMM') As Cohort_First_YYYYMM,

  [Order date],

  Customer_Unique_ID

FROM Cohort.xlsx

(ooxml, embedded labels, table is Sheet1);

Now create a pivot with the dimensions Cohort_First_YYYYMM and Vintage

with the expression Count(distinct Customer_Unique_ID).

Drag the vintage dimension to the top and click on "Suppress When Value is Null" on Chart Properties | Dimension

Set the Null and Missing symbols to 0 in Chart Properties | Presentation if you don't want - for the missing values:


t1.png

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
n1ef5ng1
Creator
Creator
Author

Thanks! genius