Trend of place visited by same customers

[#1]

Hi All

i have a very long list of dataset and below is a subset of it. Appreciate if you could advise the scripts over here as i am using personal edition.

Based on the question table, i would like to know the repeat customers trend in their place visited. for example, for the same customer A, based on their earliest date of visit, they went to Europe and subsequently went to  China. with that i would like to have a table indicated below to sum up these trend of events by duplicate customer.

Please assist .

Question
Customer	Place visited	Date of visit
A	Europe	10/4/2015
B	China	14/9/2015
C	Singapore	18/11/2015
A	China	20/9/2015
B	Europe	13/1/2015
C	Singapore	12/1/2016
D	Malaysia	13/2/2016

Answer	Visited place after their first visited place
Repeated Customer visited place based on earliest date	Europe	China	Singapore	Total
Europe		1		1
China	1			1
Singapore			1	1

[alexpanjhc]

I think the answer should be below ?

Repeated Customer visited place based on earliest date	Europe	China	Singapore	Total
Europe		2		2
Singapore			1	1

see the attached file.

[#3]

yes you are right. As i am using personal edition, can you paste your scripts here?

[alexpanjhc · Accepted solution]

A:

LOAD * INLINE [

Customer, Place visited, Date of visit

A, Europe, 10/4/2015

B, China, 14/9/2015

C, Singapore, 18/11/2015

A, China, 20/9/2015

B, Europe, 13/1/2015

C, Singapore, 12/1/2016

D, Malaysia, 13/2/2016

]

;

temp1:

load *,

if (Customer=Previous(Customer),1,0) as customer_visit_seq,

if (Customer=Previous(Customer),Previous([Place visited]),'') as Last_Places

Resident A

order by Customer,[Date of visit];

drop table A;

in the chart, you can use the last_places and place visited as dimension and then sum(customer_visit_seq) as expression.

HTH