Create FirstOrderMonth and OrderMonth

[madhuqliklondon]

Hi all,

I have datefield "Orderdate" from this I would like to create two columns "customerfirstordermonth" and "customerordermonth" .What I am trying to do is see how customers are buying behavior over the time from their first order. Could you please guide me .

Example:

Customer no	Ordernumber	Orderdate
1	123	01/01/2017
2	124	05/01/2017
3	125	10/01/2017
4	126	15/01/2017
1	320	10/02/2017
2	325	15/02/2017
3	330	20/02/2017
4	335	25/02/2017

In the above example customer no 1 shopped in 1st Jan so I would to track it as his"customerfirstordermonth" - Jan2017  and order again in Feb track it as "customerordermonth" - Feb2017.

Thanks in advance.

[YoussefBelloum]

Hi,

try this for the first order month:

=aggr(max( month( Orderdate)),[Customer no])

[madhuqliklondon]

Hi Youssef,

I am trying this at script level.

Thank you

[Anil_Babu_Samineni]

May be this?

Sample:

Load [Customer No], Ordernumber, Orderdate, Max(Orderdate) as Month From Table;

Left Join (Sample)

Load [Customer No], Max(Month) as Month Resident Sample Group By [Customer No];

[sunny_talwar]

Are you hoping to create two new columns where customerfirstordermonth will always be populated with the first order month for a particular customer (Jan2017 for Customer 1 for both instances of customer 1?) and the other field, customerordermonth will show the monthyear related to the order date?

[madhuqliklondon]

Hi Sunny,

Yes , I am trying to create new two columns , "customerfirstordermonth" will be always  where customer ordered for very first time. "customerordermonth"  will be monthyear related to order date.

[sunny_talwar]

Try this

Table:

LOAD *,

MonthName(Orderdate) as customerordermonth;

LOAD * INLINE [

    Customer no, Ordernumber, Orderdate

    1, 123, 01/01/2017

    2, 124, 05/01/2017

    3, 125, 10/01/2017

    4, 126, 15/01/2017

    1, 320, 10/02/2017

    2, 325, 15/02/2017

    3, 330, 20/02/2017

    4, 335, 25/02/2017

];

Left Join (Table)

LOAD [Customer no],

MonthName(Min(Orderdate)) as customerfirstordermonth

Resident Table

Group By [Customer no];

[madhuqliklondon]

I Have created "customerordermonth" by  using the below

Monthname(Date(Floor([Order Date])))as Ordermonth

for Customerfirstordermonth  if I use

Monthname(Date(Floor(Min([Order Date]))))as customerfirstordermonth  error is invalid expression.

[sunny_talwar]

Min needs a group by statement... that is why I did a left join... do you have a left join?

[sunny_talwar]

On the FR Sales tab?