Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

How to do this?

I have this raw table:

  

CustomerIdFirstOrderDate
C0000101/01/2016
C0000201/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.

2016-11-17_16h07_27.png

Many many thank's

Eduard

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: How to do this?

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

View solution in original post

4 Replies
Highlighted

Re: How to do this?

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?

Highlighted
Creator II
Creator II

Re: How to do this?

Hi Peter,

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

thank's

Eduard

Highlighted
MVP
MVP

Re: How to do this?

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

View solution in original post

Highlighted
Creator II
Creator II

Re: How to do this?

yesss, many many thank's Peter!!!