Help needed

[piekepotloed]

  This is a small  part of my data:

Deelnemer	Peildatum
70208	1-10-2015
70208	1-10-2014
70208	1-10-2013
25245	1-10-2015
25245	1-10-2014
25245	1-10-2012
25245	1-10-2008
25245	1-10-2007
65894	1-10-2015

I need to count the moments in peildatum. If the gap between the years is bigger than 3, the counting is stopped, A missing year between two records, needs to be counted.. So the result I need is in this table.

Deelnemer	Number of years
70208	3
25245	4
65894	1

Who has a solution for me, I ran out of idea's.

Thanks

[swuehl]

How do you get to 4 for 25245?

[piekepotloed]

I've to count the missing year also, if the gap between two years is less than or equal to 3, so that's why i get to 4.

[swuehl · Accepted solution]

Ok, maybe something like this?

Set DateFormat = 'D-M-YYYY';


INPUT:
LOAD Deelnemer,
    Peildatum,
    Year(Peildatum) as Peilyear
FROM
[https://community.qlik.com/thread/220801]
(html, codepage is 1252, embedded labels, table is @1);


TMP:
LOAD Deelnemer,
  If(Previous(Deelnemer) <> Deelnemer, 1, Rangesum( Peek('YearCount'), If(Peilyear >= Previous(Peilyear)-2, Previous(Peilyear)-Peilyear))) as YearCount,
  If(Previous(Deelnemer) <> Deelnemer, 0, Rangesum(Peek('StopFlag'),If(Peilyear < Previous(Peilyear)-2,1))) as StopFlag
Resident INPUT
ORDER BY Deelnemer, Peilyear desc;


Result:
LOAD Deelnemer,
  Max(YearCount) as YearCount
Resident TMP
WHERE StopFlag = 0
GROUP BY Deelnemer;


DROP TABLE TMP;

Deelnemer	YearCount
25245	4
65894	1
70208	3

[piekepotloed]

Thanks for your quick response...it works