Count customers that purchased x months after first purchase in a specific timeframe

[JeromeS]

Dear Community,

I am struggling to calculate the rate of customers that purchase again within 3 months after the first purchase in a specific timeframe (here 2017, which is flagged in the field #Time Flag for ease and that can be used in the expression)

Calculations must be made entirely with Set Analysis

Please find below an example table as well as the desired output.

Any help would be appreciated

Thank you !

Table
Customer	Booking Date	#Booking	#Time Flag
1	01/02/2016	1	0
2	02/02/2016	1	0
7	03/02/2016	1	0
25	04/02/2016	1	0
5	05/02/2016	1	0
7	01/02/2017	1	1
14	01/02/2017	1	1
14	04/04/2017	1	1
7	04/06/2017	1	1
25	04/05/2017	1	1
27	04/05/2017	1	1
25	07/05/2017	1	1
Desired Result
Customer	Buy Within 3 Months after Initial Purchase
7	0
14	1
25	1
27	0

[NitinK7]

Hi Jeromes,

see below expression and screenshot

take customer as dimension and write expression like

=if(Interval#( DATE(MAX([Booking Date]),'DD/MM/yyyy')-DATE(MAX([Booking Date],2),'DD/MM/yyyy'),'DD')<=90 and
Interval#( DATE(MAX([Booking Date]),'DD/MM/yyyy')-DATE(MAX([Booking Date],2),'DD/MM/yyyy'),'DD')>=1,1,0)

[nisha_rai]

Hi,

First you have to calculate the Next Booking Date/Previous Booking Date , then Number of Month Between dates.

Test:
Load * Inline [

Customer, Booking Date, #Booking, #Time Flag
1, 01/02/2016, 1, 0
2, 02/02/2016, 1, 0
7, 03/02/2016, 1, 0
25, 04/02/2016, 1, 0
5, 05/02/2016, 1, 0
7, 01/02/2017, 1, 1
14, 01/02/2017, 1, 1
14, 04/04/2017, 1, 1
7, 04/06/2017, 1, 1
25, 04/05/2017, 1, 1
27, 04/05/2017, 1, 1
25, 07/05/2017, 1, 1
];

NoConcatenate
Test2:
Load *

Resident Test
Order by Customer;

NoConcatenate
Test1:
Load *,
((year([Booking Date])*12)+month([Booking Date])) - (((year(PBD)*12)+month(PBD))) as NumOfMonth;

Load Customer,
Date(Date#([Booking Date],'DD/MM/YYYY'),'DD/MM/YYYY') as [Booking Date],
Date(DAte#(if(previous(Customer)=Customer,previous([Booking Date]) ) ,'DD/MM/YYYY'),'DD/MM/YYYY') as PBD

Resident Test2

;

Drop Table Test,Test2;

Hope this will help.

[JeromeS]

Hi,

Thank you for your replies

You are correct but calculations cannot be made in the script as the user must be able to filter on time 

In production the timeflag is replaced with time variables based on user selections

Here is the solution i created but i dislike it since it uses aggr if 

SUM(Aggr(If(SUM({1<[Booking Date]={">=$(=min({1<#Time Flag={'1'}>}[Booking Date]))<=$(=addmonths(min({1<#Time Flag={'1'}>}[Booking Date])),1) "}>}#Booking)>1, 1, 0), Customer))

I wonder if the same result cannot be achieved with P function

I use this function to display a chart of the rate of rebuying customers that purchased again after x months their first purchase (within the time horizon selected by the user for both the MIN booking date and the number of bookings), segmented by time intervals (1 month, 2 months...)

as AGGR cannot be used with fieldvalue. I had to create a calculated dimension that follows the same logic as the above mesure and a simple count(distinct customer) divised by the count of distinct rebuying customers on the total time interval