topic Dynamic periods in QlikView

Dynamic periods

Wed, 28 Apr 2010 12:31:46 GMT

Hi,

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!

Dynamic periods

johnw — Wed, 28 Apr 2010 21:21:29 GMT

This seems to do the trick:

,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).

Dynamic periods

Thu, 29 Apr 2010 07:51:19 GMT

Thanks a lot, It seems to work just perfect.