Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

alexheyns
New Contributor

Count consecutive months.

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.

examplexls.png

2 Replies
Partner
Partner

Re: Count consecutive months.

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,

sample.png

As you have on your spreadsheet

Re: Count consecutive months.

You can simplify in table like.

Count(TOTAL <ID> YM)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)