Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
syazwani91
New 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!

4 Replies
Partner
Partner

Re: Get new customers orders movement based on join date

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)

Partner
Partner

Re: Get new customers orders movement based on join date

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

Re: Get new customers orders movement based on join date

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

Re: Get new customers orders movement based on join date

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.