topic Re: Cohort Analysis in QlikView

Cohort Analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hi Community,

I need to do a Cohort Analysis. Primarily I'm trying to work out the difference with the dollar amounts I'm very grateful to Sunny for providing insights on this report thus far.

Please provide assistance,

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 13:21:56 GMT

Are you still trying to fix the left numbers? I did provide a update for that. Did you see that?

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 13:28:10 GMT

Have a look here:

Re: Difference between two months‌

Also, I just want to reiterate, please only mark those posts as helpful which you actually found helpful. I am not looking to mis-use this feature by influencing you to put each and every response of mine as helpful. I really appreciate your understanding

Best,

Sunny

Re: Cohort Analysis

Tue, 05 Apr 2016 13:42:55 GMT

Thanks Sunny this is some enthralling piece of art from you. These formulas have the syntax Above in 'em which out of my limited knowledge are only efficient for straight tables. I tried to copy and paste the formulas in list boxes to actually view the IDs which left us or change in existing (increased our business) or new customers but it failed. Is there anyway I can actually use these formulas in list boxes so I can have the flexibility to drill down to the Customer IDs?

Thanks,

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 13:52:14 GMT

We might be able to, but the issue is which month change would you want to see in the list box?

Re: Cohort Analysis

Tue, 05 Apr 2016 14:08:39 GMT

I want to make multiple list boxes for each of the columns in the straight table and get to be filtered out by the MonthYear list box. Once the end user clicks on the MonthYear the list boxes ( new, left, change in existing, etc..) will get filtered out as well.

Thanks for the assistance.

Re: Cohort Analysis

Tue, 05 Apr 2016 15:05:21 GMT

Hi Sunny,

I've no prior experience with this. Any help will be appreciated!!!!!!

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 15:12:39 GMT

Wow this is some complicated stuff. Is it okay to do this in the script or will it need to change based on selections?

Re: Cohort Analysis

Tue, 05 Apr 2016 15:18:11 GMT

Hi Sunny,

Do as you please. I need your assistance in tackling this stuff. I've no prior experience with this.

Re: Cohort Analysis

Kushal_Chawda — Tue, 05 Apr 2016 17:00:56 GMT

With Your attached data I can build something like this. Is this what you are looking for?

Table:
CrossTable (Month, Data,4)
LOAD [Customer Internal ID],
Customer,
Average,
[% of total],
[2015 09],
[2015 10],
[2015 11],
[2015 12],
[2016 01],
[2016 02]
FROM
[STK.xlsx]
(ooxml, embedded labels, table is Thisisit);

Data:
NoConcatenate
LOAD *,
date(date#(Month,'YYYY MM'),'MMM-YYY') as MonthYear
Resident Table
where [Customer Internal ID]<>'#N/A';

DROP Table Table;

New:
LOAD *,
if(RowNo()=1 or [Customer Internal ID]<>Previous( [Customer Internal ID]), MonthYear,
if([Customer Internal ID]=Previous([Customer Internal ID]), Peek('MONTHYEAR'))) as MONTHYEAR
Resident Data
Order By [Customer Internal ID], MonthYear asc;

DROP Table Data;

Re: Cohort Analysis

Tue, 05 Apr 2016 17:33:55 GMT

Hi Sunny,

Do you've any further insights?

Re: Cohort Analysis

Tue, 05 Apr 2016 17:35:06 GMT

Hi Khushal,

Amazing work! I'm mesmerized. For some odd reason I can't open the Test QVW above it fails after it finishes downloading.

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 17:36:02 GMT

Kush, I am unable to open it as well. Can you repost it

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 17:36:42 GMT

Lets wait and check if Kush's solution does what you are looking for. If it does, then you won't need my help any more

Re: Cohort Analysis

Tue, 05 Apr 2016 19:46:03 GMT

Hi Sunny,

Do you've any updates?

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 19:49:09 GMT

Is what Kush provided not the right result you are expecting? If it isn't, I can start looking at his results and start making changes there

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 20:19:34 GMT

Only because Kush is not available, I am going to post my version of his code. All credit to him for getting this to work.

Re: Cohort Analysis

Tue, 05 Apr 2016 20:19:59 GMT

His scripting is ridiculously phenomenal but there's this little issue with the highlighted numbers. Up to Nov the numbers are great but once we hit Dec; the Sep-15 numbers are getting calculated twice. 3910 - 88 is 3822 but what the formula (I don't know since I can't open the test QVW) is doing its also subtracting the customers we lost from Oct and Nov (180 - 3 = 177 and 169 - 2 = 167) into 3822 + 5 (3 from Oct and 2 from Nov) tallies 3827 total customers lost. This trend continues until Feb 2016.

The table should be somewhat like below.

Please provide assistance!!!!!!!!

Re: Cohort Analysis

Tue, 05 Apr 2016 20:36:01 GMT

Thanks Sunny but the same issue exists here (from Dec) the top number also takes under consideration the numbers from Oct. The top number in Dec should be close to 3823 and the one in Jan 3745 and in Feb 3625. Is there anyway to stabilize the top figure in each month to show the difference from the original loss of customers (the second image) please? 4054 - 71 - 72 - 88 - 78 - 119 = Top Feb number should be 3626.

Re: Cohort Analysis

sunny_talwar — Tue, 05 Apr 2016 20:37:49 GMT

I am looking at the issue now. Can you also comment about the other rows? All other row numbers look good, except the top row?