How to do this?

[ecabanas]

I have this raw table:

CustomerId	FirstOrderDate
C00001	01/01/2016
C00002	01/03/2016

And I want to create periods of 28 days each, where FirstorderDate is the date that our customer starts with us. The starting period is the FOD and the last date period is Starting Period + 27 days......till end of the present year.

Many many thank's

Eduard

[Peter_Cammaert]

The logic is clear when looking at example C00001. But for customer C00002, the periods run well into 2017, though you state that they should run ".till end of the present year." And they only cover 11 calendar months.

Something is missing in your definition?

[ecabanas]

Hi Peter,

Yes I did the excel calculations too quick, for Customer2 the last period is the number 10

thank's

Eduard

[johnw · Accepted solution]

Perhaps like this?

ExpandedTable:
LOAD
CustomerID
,FirstOrderDate
,iterno() as PeriodNumber
,FirstOrderDate+(iterno()-1)*28 as StartPeriod
,FirstOrderDate+iterno()*28-1 as EndPeriod
RESIDENT RawTable
WHILE FirstOrderDate+iterno()*28-1 <= yearend(today())
;

[ecabanas]

yesss, many many thank's Peter!!!