Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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:
Desired output
Cohort_First_YYYYMM | 0 | 1 | 2 | 3 |
201510 | 31 | 0 | 1 | 2 |
201511 | 97 | 7 | 1 | - |
201512 | 399 | 12 | - | - |
201601 | 133 | - | - | - |
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.
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
Please upload the source spreadsheet as well to develop and test the load script.
Attached with the data and qw
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:
Thanks! genius