topic Re: Get new customers orders movement based on join date in App Development

Get new customers orders movement based on join date

syazwani91 — Sat, 16 Nov 2024 06:55:32 GMT

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!

Re: Get new customers orders movement based on join date

Kalmer — Mon, 07 Jan 2019 09:30:12 GMT

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)

Re: Get new customers orders movement based on join date

Kalmer — Mon, 07 Jan 2019 09:51:56 GMT

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)

Re: Get new customers orders movement based on join date

syazwani91 — Thu, 10 Jan 2019 08:50:06 GMT

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.

Re: Get new customers orders movement based on join date

syazwani91 — Thu, 10 Jan 2019 08:52:47 GMT

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.