Get the Maximum Number of Consecutive Periods - Qlik Community

microwin88x · ‎2014-04-07

Hello!

I have a table with Accounts and Periods (YYYY/MM) and I need to get the maximum number of consecutive Periods for each Account. For example, in the following table:

The result would be:

For Account 1000000001, the result is 5 because it obtains the Periods: 201306, 201307, 201308, 201309 and 201310.

(201301, 201302 and 201303 are not considerated because despite they are consecutive, the count would be only 3).

The same happens to the other Accounts.

Notes:

I would need this as an expression in any table object instead of in script because the Accounts and Periods are dynamic (they vary) for a corresponding selection in the application.

Thank you!!!

maxgro · ‎2014-04-07

SCRIPT

Temp:

Load * Inline

[

Account, Period

1000000001, 201301

1000000001, 201302

1000000001, 201303

1000000001, 201306

1000000001, 201307

1000000001, 201308

1000000001, 201309

1000000001, 201310

1000000002, 201301

1000000002, 201302

1000000002, 201303

1000000002, 201304

1000000002, 201310

1000000002, 201311

1000000002, 201312

1000000003, 201301

1000000003, 201305

1000000003, 201307

];

NoConcatenate

load

*,

if(Account=Peek(Account)

and

(

(Period=(Peek(Period)+1))

or

(

left(Period,4)=left(peek(Period),4)+1

and Right(Period,2)='01'

and Right(peek(Period),2)='12'

)

),

peek(NumConseq)+1, 1) as NumConseq

resident

Temp

order by Account, Period;

The script shoul work also wiith different years like (bold)