topic Re: Count consecutive months. in QlikView

Count consecutive months.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Good day everyone.

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

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.

Re: Count consecutive months.

felipedl — Tue, 09 Oct 2018 16:09:32 GMT

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

Re: Count consecutive months.

Anil_Babu_Samineni — Wed, 10 Oct 2018 03:46:11 GMT

You can simplify in table like.

Count(TOTAL <ID> YM)