I'm facing some difficulties in creating dynamic period. I like to aggregate data in 30 days periods for a specific customer, and I'd like to create a period number for that customer. As an example look at the data structure below. The first period for one specific customer starts with his first appearance. All appearances for that customer withion 30 days is in period 1. Period 2 for that customer should start with his first appearance without his first 30 days period, so that period might start 31 days from the first as well as 300 days from the first
Id
Date
Period
1
2008-03-01
1
1
2008-03-04
1
1
2009-01-14
2
1
2009-03-15
3
2
2009-01-14
1
2
2009-01-15
1
2
2009-01-16
1
2
2009-01-17
1
2
2009-01-18
1
2
2009-01-19
1
2
2009-01-20
1
2
2009-01-21
1
Does anyone have a solution to this problem, please let me know!
,if(Id<>previous(Id) or Date-peek(PeriodStart)>30,Date,peek(PeriodStart)) as PeriodStart ,if(Id<>previous(Id),1,peek(Period)-(Date-peek(PeriodStart)>30)) as Period
It probably needs a little explanation, though. Previous() looks at the previous record in the SOURCE table. Peek() looks at the previous record in the table you are currently creating. Conditions evaluate to -1 for true, 0 for false, so when I subtract (Date-peek(PeriodStart)>30), this is shorthand for adding if(Date-peek(PeriodStart)>30,1,0).
,if(Id<>previous(Id) or Date-peek(PeriodStart)>30,Date,peek(PeriodStart)) as PeriodStart ,if(Id<>previous(Id),1,peek(Period)-(Date-peek(PeriodStart)>30)) as Period
It probably needs a little explanation, though. Previous() looks at the previous record in the SOURCE table. Peek() looks at the previous record in the table you are currently creating. Conditions evaluate to -1 for true, 0 for false, so when I subtract (Date-peek(PeriodStart)>30), this is shorthand for adding if(Date-peek(PeriodStart)>30,1,0).
