topic Re: How to do this? in QlikView

How to do this?

ecabanas — Thu, 17 Nov 2016 15:08:24 GMT

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

Re: How to do this?

Peter_Cammaert — Thu, 17 Nov 2016 15:51:48 GMT

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?

Re: How to do this?

ecabanas — Thu, 17 Nov 2016 16:41:47 GMT

Hi Peter,

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

thank's

Eduard

Re: How to do this?

johnw — Thu, 17 Nov 2016 17:22:34 GMT

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())
;

Re: How to do this?

ecabanas — Thu, 17 Nov 2016 17:41:42 GMT

yesss, many many thank's Peter!!!