Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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())
;
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?
Hi Peter,
Yes I did the excel calculations too quick, for Customer2 the last period is the number 10
thank's
Eduard
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())
;
yesss, many many thank's Peter!!!