topic Re: Cohort Analysis in QlikView

Cohort Analysis

n1ef5ng1 — Fri, 08 Jan 2016 02:39:34 GMT

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_YYYYMM	0	1	2	3
201510	2	1	0	31
201511	1	7	97	0
201512	12	399	0	0
201601	133	0	0	0

Re: Cohort Analysis

n1ef5ng1 — Fri, 08 Jan 2016 03:11:31 GMT

Desired output

Cohort_First_YYYYMM	0	1	2	3
201510	31	0	1	2
201511	97	7	1	-
201512	399	12	-	-
201601	133	-	-	-

Re: Cohort Analysis

jonathandienst — Fri, 08 Jan 2016 04:55:50 GMT

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.

Re: Cohort Analysis

n1ef5ng1 — Fri, 08 Jan 2016 05:59:13 GMT

Attached.

Desired output is

Cohort_First_YYYYMM	Mth:0	Mth:1	Mth:2	Mth:3
201510	2	0	1	2
201511	2	1	1	0
201512	33	12	0	0
201601	133	0	0	0

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

thanks

Re: Cohort Analysis

jonathandienst — Fri, 08 Jan 2016 06:30:44 GMT

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

Re: Cohort Analysis

n1ef5ng1 — Fri, 08 Jan 2016 06:35:46 GMT

Attached with the data and qw

Re: Cohort Analysis

jonathandienst — Fri, 08 Jan 2016 07:13:02 GMT

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:

Re: Cohort Analysis

n1ef5ng1 — Fri, 08 Jan 2016 08:07:25 GMT

Thanks! genius