Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
syazwani91
Contributor
Contributor

Get new customers orders movement based on join date

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:Qlik refence.JPG

 

 

 

 

 

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!

Labels (4)
4 Replies
Kalmer
Partner - Creator
Partner - Creator

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
Partner - Creator
Partner - Creator

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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

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.