topic Creating a retention table in qlik sense in App Development

Creating a retention table in qlik sense

Thu, 15 Sep 2016 03:10:43 GMT

Hi,

I am trying to make a retention table in qlik sense and I am failing at it with multiple attempts.It would be great if someone could use the attached data and send me a QVF to solve this problem

mto‌ kha‌ isaaclin‌ chooco_co‌

Output (which i want):-

The table says for example 10000 users came in Jan16, out of this 7530 came back in Feb and out of 10K 9K came back in Mar. Basically the numbers on the right are subset of the leftmost number

Note the numbers below are not exact and just a sample output

Retention	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16
Jan-16	10,000	7,530	9,000	5,306	782	6,958	7,113	3,464
Feb-16		9,000	411	1,654	7,379	5,782	4,210	7,634
Mar-16			8,000	2,360	4,931	3,798	102	3,435
Apr-16				8,800	4,656	2,234	1,339	4,235
May-16					9,000	8,775	1,588	5,244
Jun-16						4,500	216	2,008
Jul-16							7,700	747
Aug-16								2,232

Data Available:-

The data is in the form of date and user_id (attached is the excel)

Re: Creating a retention table in qlik sense

Tue, 20 Sep 2016 02:33:53 GMT

I am still figuring this out. It would be great to have a solution or a workaround

Re: Creating a retention table in qlik sense

Wed, 23 Nov 2016 07:20:49 GMT

mto‌ Any help here will be appreciated

Re: Creating a retention table in qlik sense

Wed, 23 Nov 2016 07:47:23 GMT

Hi Vinol,

I use Qlikview, not Qliksense, but I have achieved exactly the same thing by:

1. Creating two tables, one with all the user activity and other with just the first activity per user (like Load user_id, min(date) from [activity table] group by user_id).

2. Then creating two calendars, one for each of the tables, that generate a separate Yearmonth variable for all the activity and the first activity.

3. Create a pivot table with the two yearmonth variables, and count(distinct(username)) as expression.

4. Drag one of the dimensions to columns according to your desired output.

Hope this helps!

Regards,

Jorge