topic Re: Create FirstOrderMonth and OrderMonth in QlikView

Create FirstOrderMonth and OrderMonth

madhuqliklondon — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Create FirstOrderMonth and OrderMonth

YoussefBelloum — Tue, 19 Dec 2017 11:30:38 GMT

Hi,

try this for the first order month:

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

Re: Create FirstOrderMonth and OrderMonth

madhuqliklondon — Tue, 19 Dec 2017 11:42:26 GMT

Hi Youssef,

I am trying this at script level.

Thank you

Re: Create FirstOrderMonth and OrderMonth

Anil_Babu_Samineni — Tue, 19 Dec 2017 11:49:05 GMT

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];

Re: Create FirstOrderMonth and OrderMonth

sunny_talwar — Tue, 19 Dec 2017 12:21:43 GMT

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?

Re: Create FirstOrderMonth and OrderMonth

madhuqliklondon — Tue, 19 Dec 2017 13:01:47 GMT

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.

Re: Create FirstOrderMonth and OrderMonth

sunny_talwar — Tue, 19 Dec 2017 13:05:37 GMT

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];

Re: Create FirstOrderMonth and OrderMonth

madhuqliklondon — Tue, 19 Dec 2017 13:25:56 GMT

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.

Re: Create FirstOrderMonth and OrderMonth

sunny_talwar — Tue, 19 Dec 2017 13:29:51 GMT

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

Re: Create FirstOrderMonth and OrderMonth

sunny_talwar — Tue, 19 Dec 2017 13:43:17 GMT

On the FR Sales tab?

Re: Create FirstOrderMonth and OrderMonth

madhuqliklondon — Tue, 19 Dec 2017 13:46:33 GMT

I have to do this for tabs : J ,uk, Mir,Uk_arch,ems and FR.

Re: Create FirstOrderMonth and OrderMonth

sunny_talwar — Tue, 19 Dec 2017 13:54:46 GMT

You are doing concatenate, but to do this, you will have to first create a temptable and then left join to the temptable and then concatenate

TempTable:

LOAD....

FROM ...;

Left Join (TempTable)

LOAD ...

Resident TempTable;

Concatenate (...)

LOAD *

Resident TempTable;

DROP Table TempTable;

Re: Create FirstOrderMonth and OrderMonth

madhuqliklondon — Tue, 19 Dec 2017 14:15:30 GMT

I will try that Sunny.