Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Creating periods and link last order dates per customer

Hi,

I am trying to create period for the last order date of customers.

Customers may have mulitple pruchases or may not have purchased anything in the periods noted below. Only the last order date per customer is relevant.

The periods are spanning two years:

Period,StartYear,EndYear

1,2000,2001

2,2002,2003

3,2004,2005

4,2006,2007

5,2008,2009

I need to use the values of those periods in a calculation.

First of all how do I get the last order date per customer and how do I then link that to the periods above?

Thanks for your help,

Peter

Tags (2)
1 Solution

Accepted Solutions
lironbaram
Honored Contributor II

Creating periods and link last order dates per customer

hei try to install a script a long this lines

for last order date by customer in the script use:

load max(OrderDate)            as LastOrderDate,

year(max(OrderDate) )  as LastOrderYear,

CustomerID

Resident Orders

group by CustomerID;

Periods:

Period,StartYear,EndYear

1,2000,2001

2,2002,2003

3,2004,2005

4,2006,2007

5,2008,2009

intervalmatch (LastOrderYear)    load StartYear    EndYear resident Periods;

2 Replies
lironbaram
Honored Contributor II

Creating periods and link last order dates per customer

hei try to install a script a long this lines

for last order date by customer in the script use:

load max(OrderDate)            as LastOrderDate,

year(max(OrderDate) )  as LastOrderYear,

CustomerID

Resident Orders

group by CustomerID;

Periods:

Period,StartYear,EndYear

1,2000,2001

2,2002,2003

3,2004,2005

4,2006,2007

5,2008,2009

intervalmatch (LastOrderYear)    load StartYear    EndYear resident Periods;

Not applicable

Creating periods and link last order dates per customer

Hi Liron,

Thanks for your help. That works like a treat!

Peter

Community Browser