Count consecutive months.

[Anonymous]

Good day everyone.

I came across the discussion below and I have a very similar request but the data is slightly different.

Consecutive count

I need to count consecutive months per client. so my data would look like the left two columns and the result must be like the right hand column. I am extremely new to Qliksense and any help would be highly appreciated.

[felipedl]

This code should do it:

data:
load *,
Num(Date#(YM,'MMM-YY')) as [NUM YM];
load * Inline
[
ID,YM
1,Jan-16
1,Feb-16
1,Mar-16
1,Apr-16
1,May-16
2,Jan-16
2,Feb-16
2,Mar-16
2,May-16
3,Jan-16
3,Feb-16
3,Mar-16
];


NoConcatenate
OrderedData:
load *,
if
(
ID=peek(ID),
peek([NUM YM]),
// if
// (
// [NUM YM]=Num(AddMonths(peek([NUM YM]),1)),
// 1,
// 0
// ),
[NUM YM]
) as [Flag],
if
(
ID=peek(ID),
if
(
[NUM YM]=Num(AddMonths(peek([NUM YM]),1)),
1,
0
),
1
) as [Flag2]
Resident data
order by ID,[NUM YM];


drop table data;

It gives me the following,

As you have on your spreadsheet

[Anil_Babu_Samineni]

You can simplify in table like.

Count(TOTAL <ID> YM)