topic Re: % Returning users in QlikView

% Returning users

Wed, 25 Nov 2020 16:16:04 GMT

Hello everyone,

Does anyone know if there's an option to show the % of returning customers from 2 periods.

I have a period that is from today - 28 days ago and a period that's from 29 days ago - 56 days ago.

I want to compare all users that have been online between these times and see if the users that were online 29 days to 56 days ago, also were online between today and 28 days ago.

Thanks in advance,

Ivo

Re: % Returning users

adamdavi3s — Thu, 09 Mar 2017 07:26:06 GMT

Hi Ivo,

This is certainly possible, can you give us some sample data so we can provide a relevant solution?

Re: % Returning users

Thu, 09 Mar 2017 07:30:48 GMT

Im not allowed to share the data i'm working with, due to secrecy. However, I can make some fake data and send that to you.

Re: % Returning users

rahulpawarb — Thu, 09 Mar 2017 07:31:57 GMT

Hello Ivo,

Assuming below data table I have created two different straight tables with UserId & DateOnline as dimensions and different expressions for each table to list users online status during said time frame.

OnlineData:

LOAD * INLINE [

UserId, DateOnline

U0001, 02/01/2017

U0002, 02/02/2017

U0003, 02/03/2017

U0004, 02/04/2017

U0005, 02/05/2017

U0006, 02/06/2017

U0001, 02/11/2017

U0002, 02/12/2017

U0007, 03/03/2017

U0008, 03/04/2017

U0009, 03/05/2017

U0006, 02/16/2017

];

Expression for Online Between Today - 28 Days:

If(Today()-Date#(DateOnline, 'MM/DD/YYYY') <=28, 'Online')

Expression for Online Between 29 - 56 Days:

If(Today()-Date#(DateOnline, 'MM/DD/YYYY') >=29 AND Today()-Date#(DateOnline, 'MM/DD/YYYY') <=56, 'Online')

Please refer the attached sample application.

As an alternative, you can create two different resident load statements for Online Between Today - 28 Days and Online Between 29 - 56 Days; use these data sets in two different table boxes.

Hope this will help.

Regards!

Rahul

Re: % Returning users

adamdavi3s — Thu, 09 Mar 2017 07:56:26 GMT

Hi Ivo,

Fake would be fine, its just to get an idea of what your data looks like so we can provide an answer that is relevant!

Re: % Returning users

Thu, 09 Mar 2017 08:09:14 GMT

Here is a fake data file I made, its a simple few users that have been online in the past 29 - 56 days and some of them were online in the last 28 days as well. As an extra info point, I added 3 users who weren't online on either of the two periods.

Thanks again for your help!

Re: % Returning users

adamdavi3s — Thu, 09 Mar 2017 08:11:29 GMT

Hi Ivo,

I just used rahulpawarb‌'s very helpful example as dummy data.

The formula required in this example is:

sum({<DateOnline={">$(=today()-28)"}, UserId=p({<DateOnline={"<=$(=today()-29) >=$(=today()-56)"}>}UserId)>}1)

The important part of this is the P() as we're basically saying for our 28 day group, then restrict it for users who appear int he 29-56 day group as well.

Re: % Returning users

adamdavi3s — Thu, 09 Mar 2017 08:20:28 GMT

Ok so in this case it is super simple, you can just use:

=num(sum(Aggr(if(sum(Date_IsLast28Days) >0 AND sum(Date_IsLast28to56Days)>0,1,0),User_Key)) / count(total User_Key),'0.00%')

That is assuming you want the percentage of all users?

Re: % Returning users

adamdavi3s — Thu, 09 Mar 2017 08:23:21 GMT

This would get you what you want I think, so % of users who were onluine >56 and > 28

=num(sum(Aggr(if(sum(Date_IsLast28Days) >0 AND sum(Date_IsLast28to56Days)>0,1,0),User_Key)) / sum(Aggr(if(sum(Date_IsLast28Days) >0 OR sum(Date_IsLast28to56Days)>0,1,0),User_Key)),'0.00%')

Re: % Returning users

Thu, 09 Mar 2017 12:41:42 GMT

Thanks for all the help. I tried to use the sample data and solution, but was not able to create a text box expression with it resulting in a single percentage shown. However, I tried to use a Set Analysis to compare two table and count the output.

Set Analysis:

=Round(sum({1<Date_IsLast28Days= {1}>*<Date_IsLast28to56Days= {1}>} #Debtors)/ Count({< Date_IsLast28to56Days = {1}>} DISTINCT Debtor)*100)&'%'

The Date_IsLast28to56Days and Date_IsLast28Days refer to a dimension noted as:

,	If(Today() - Date <= 28 And Today() - Date >= 0, 1, 0) As Date_IsLast28Days

,	If(Today() - Date <= 56 And Today() - Date >= 0, 1, 0) As Date_IsLast56Days

and in Preceding Load:

,	If(Date_IsLast28Days = 0 and Date_IsLast56Days = 1, 1, 0) As Date_IsLast28to56Days

Thanks for all the help again!