Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Unsubscribe rate from week subscription model

Hi everyone,
I have a subscription model by week (each week, user pay).

Here is my data strcture:

Got a billing tab with multiple lines. Each line equals one billing action. Here is all the fields needed in my problem:
for each line:
billing_id: billing identifier (primary k)
subscriber_id = referencing a subscriber from another table (foreign key)

billing_date = date when the payment was made
creation_date = date when the subscriber subscribed. It means that the first billing action for a subscriber was made at this date.
Then inside Qlik Sense I make a Pivot table with on line weekname(creation_date) and column weekname(billing_date)
this gives me a cohort tab by week. Which means for all weeks we group all subscribers that subscribed on the same week, and then we look for every week how much billing has been made (using the count(billing_id)). This gives us some lifetime. For example (from the picture below)

On the Week number 13 of 2016
43 people subscribed
On the week after,  14 people got billed. it means that 14/43 people are still subscribed.
On the week after,  12 people got billed. It means taht 12/43 people are still subscribed

What I want is a representation in pourcentages of this tab, for the same example I used just before:
On the week number 13 of 2016:
100% people subscribed
On the week after, 14 people got billed, it means that 32% people are still subscribed
On the week after, 12 people got billed, it means that 27% people are still subscribed.
But I cannot achieve this !
Thanks in advance and sorry for my english.
Here is a piece of the tab:
Qlik.png

1 Solution

Accepted Solutions
Gysbert_Wassenaar

You'll have to use inter-record functions. Probably the Before() function. If you need more help please post a small Qlik Sense app with some example data.


talk is cheap, supply exceeds demand

View solution in original post

1 Reply
Gysbert_Wassenaar

You'll have to use inter-record functions. Probably the Before() function. If you need more help please post a small Qlik Sense app with some example data.


talk is cheap, supply exceeds demand