Skip to main content
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
Showing results for 
Search instead for 
Did you mean: 
Not applicable

User Churn Chart

Hi everyone!

I'm new to the QlikView Community, so forgive me if this subject has been covered. I can't seem to find the answer I'm looking for.

I'm trying to calculate a churn report by taking the amount of unique users that have logged on to our site during a 30 day period, and finding the amount of those that have logged on again in the next 30 day period. The catch is that I want to calculate this every week using WeekStart. For e.g.:

5 unique users in the WeekStart-60 to WeekStart-30 range (a.k.a. "A"), 4 of those are still in the WeekStart-30 to WeekStart range (a.k.a. "B"), therefore churn would equal (A-B)/A or (5-4)/5 = 20%.

Here's some test log in data to give you an idea of how the log file is structured:




I hope this make sense, it's been driving me crazy looking at ARRG() functions, Set Analysis, etc. Not sure what to use or how to use it.

Thanks in advance for any help you can give me!


4 Replies

See attached qvw

talk is cheap, supply exceeds demand
Not applicable

Hi Gysbert,

Thanks very much for the reply. Unfortunately I don't have the paid version of QlikView yet. I'm still waiting on approval and a budget to get it, which could take months. Could you please explain how you did it? Maybe with examples of the code?

Sorry if it's a hassle, really appreciate it.




I think there might be an issue with the suggested approach, because it does check that group B is a subset of A, instead it just compares the distinct count of users in both periods.

For example, if you replace your second last line with:

7, 01/02/13

the distinct count won't change and the result is the same, -20%, while user 7 has not appeared in previous period yet and should be disregarded.

I think you can potentially create a frontend only solution, but it will be probably very complex and resource extensive.

If possible, try creating a solution in the script, e.g. you can check for User appearance in the requested periods in script already.

Maybe like this (you can probably optimize this a lot):

SET DateFormat='DD/MM/YY';



    User_ID, Date_Logged_On

    1, 01/01/13

    2, 01/01/13

    3, 01/01/13

    4, 01/01/13

    5, 01/01/13

    2, 01/02/13

    3, 01/02/13

    1, 01/02/13

    5, 01/02/13

    2, 01/02/13


Let vStart  = num(makedate(2013,1,1));

Let vEnd     = num(makedate(2013,3,1));


LOAD Date(weekstart($(vStart),iterno()-1)) as ChurnRateDate,

     Date(weekstart($(vStart),iterno()-1)-30) as Date30,

     Date(weekstart($(vStart),iterno()-1)-60) as Date60

AutoGenerate 1

while weekstart($(vStart),iterno()-1) <= weekstart($(vEnd));


JOIN LOAD distinct User_ID Resident T1;   


Let vNumDates = NoOfRows('Churn');

for i = 0 to vNumDates-1


    LOAD peek('ChurnRateDate',$(i),'Churn') as ChurnRateDate,

         peek('User_ID',$(i),'Churn') as User_ID,

         if(count(Date_Logged_On),1,0) as Flag30

    Resident T1

     where User_ID = peek('User_ID',$(i),'Churn')

         and Date_Logged_On <= peek('ChurnRateDate',$(i),'Churn')

        and Date_Logged_On > peek('Date30',$(i),'Churn');



    LOAD peek('ChurnRateDate',$(i),'Churn') as ChurnRateDate,

         peek('User_ID',$(i),'Churn') as User_ID,

         if(count(Date_Logged_On),1,0) as Flag60

    resident T1

      where User_ID = peek('User_ID',$(i),'Churn')

          and Date_Logged_On <= peek('Date30',$(i),'Churn')

          and Date_Logged_On > peek('Date60',$(i),'Churn');    

next i

Left Join (Churn) LOAD * Resident TMP1;

Left Join (Churn) LOAD * Resident TMP2;

drop table TMP1;

drop table TMP2;

Then, in the frontend, just create a straight table chart with dimension ChurnRateDate and as expression:

= (count({<Flag30 = {1}, Flag60={1}>} User_ID) - count({<Flag60={1}>} User_ID))

          / count({<Flag60={1}>} User_ID)

Hope this helps,


Not applicable

Hi! I have a smiliar problem so i tag along here .

I want to look at month for when a customer "churned". I have used your example, swuehl, and changed it so that i can se customer that has not bought anything in the past 12 moths. My problem is that if a customer bought at two different dates, that customer will have 2 churn dates. i only want to established when that customer first (or maybe last(?)) churned according to my definiton when looking at a time period.

My problem is if i look at Orgno 1 you can se that it had activity 2012-01-04 and 2012-09-20 so when i look at a period of time this orgno "churns" at both 2013-01 and 2013-09 but since that customer has come back in a 12 month period the churn should only be at 2013-09. how can i solve this?

Thanks in advance!