Get new customers orders movement based on join date

[syazwani91]

Hi Qlik Community,

I would like to  know how am I able to track order movement of  new customer  based on customer join date across different time interval, new customer is flag as '1':

1) Join Year Month: +1 month  (i.e. JY +1)

2) Join Year Month: +2 month (i.e. JY +2)

3) Join Year Month:+3 month (i.e. JY +3)

4) Join Year Month: +4 month (i.e. JY +3)

5) Join Year Month: +5 month

  Refer to screenshot as per below:

Note: I had tried As-Of  function, Aggregation (Range sum) &   created  dates range variable however unable to get new customer order movement.

Looking for  potential solution to resolve this problem.

Thanks in advance!

[Kalmer]

I've made a temporary script for you to point you to the right direction.
In script there should be something like this:

ClientsFirstSale:
LOAD
 	ClientID,
//    Year(MinDate) as ClientFirstSaleYear,
//    Month(MinDate) as ClientFirstSaleMonth,
//    Date(MinDate, 'DD.MM.YYYY') as  ClientFirstSaleDate,
	text(Date(MinDate,'YY')) & 'M' & text(Date(MinDate,'MM')) as FirstSale_YearMonth,
	floor((Today()-MinDate)/30) as FirstSalesAgeInMonths
;
LOAD
	ClientID,
	min(Date) as MinDate
FROM [lib://path/Facts.qvd](qvd)
group by ClientID;

Y dimension should be FirstSale_YearMonth
X dimesnion should be ='JY +' & FirstSalesAgeInMonths
Measure should be count(distinct ClientID)

[Kalmer]

Or do it on Facts table:

ClientsFirstSale:
LOAD
 ClientID,
// Year(MinDate) as ClientFirstSaleYear,
// Month(MinDate) as ClientFirstSaleMonth,
 Date(MinDate, 'DD.MM.YYYY') as ClientFirstSaleDate,
 text(Date(MinDate,'YY')) & 'M' & text(Date(MinDate,'MM')) as FirstSale_YearMonth,
 floor((Today()-MinDate)/30) as FirstSalesAgeInMonths
;
LOAD
 ClientID,
 min(TransactionDate) as MinDate
FROM [lib://path/Facts.qvd](qvd)
group by ClientID;

MAP_FirstSaleDate:
Mapping load distinct
 ClientID,
 ClientFirstSaleDate
Resident ClientsFirstSale;

Facts:
Load
 *,
 round(interval(Applymap('MAP_FirstSaleDate', ClientID, null()) - TransactionDate, 'D')/-30) as PeriodFromFirstBuy
;

Y dimension should be FirstSale_YearMonth
X dimesnion should be ='JY +' & PeriodFromFirstBuy+1    - i added the +1 since it starts from 0
Measure should be count(distinct ClientID)

[syazwani91]

Hi Kalmer,

Thank you for your quick response!

To clarify  further if I  would like to get  total numbers of orders( i.e. say +2 months since join) made by New customer whom join in e.g. 18M01?

My objective: To tracked the movement of orders made by respective "New customers" join group year month & see how these customer orders had evolve since the YearMonth/ period that they join.

[syazwani91]

Hi Kalmer,

Thank you for your quick response!

To clarify  further if I  would like to get  total numbers of orders( i.e. say +2 months since join) made by New customer whom join in e.g. 18M01?

My objective: To tracked the movement of orders made by respective New customers join group year month & see how these customer orders had evolve since the YearMonth/ period that they join.