Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

% Returning users

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

1 Solution

Accepted Solutions
Not applicable
Author

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!

View solution in original post

9 Replies
adamdavi3s
Master
Master

Hi Ivo,


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

Not applicable
Author

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.

rahulpawarb
Specialist III
Specialist III

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

adamdavi3s
Master
Master

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!

Not applicable
Author

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!

adamdavi3s
Master
Master

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.

adamdavi3s
Master
Master

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?

adamdavi3s
Master
Master

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%')

Not applicable
Author

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!