Get the Max Number of Consecutive Periods

[microwin88x]

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!!!

[MK_QSL]

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

  Account,

  PERIOD,

  IF(Account = Previous(Account) and PERIOD-1 = Previous(PERIOD), RangeSum(Peek('FLAG'),1),1) as FLAG

Resident Temp

Order By Account, PERIOD;

Drop Table Temp;

=========================

Now create a straight table

Dimension = Account

Expression = Max(Flag)