Add only consecutive month's data using flag

[Anonymous]

Hi guys,

I have to create a flag as 1 for only consecutive months from the last month, else 0.

For MeterID = 1, output = 3,

For MeterID = 2, output = 2 and

For MeterID = 3, output = 3

Help me to achieve this in script. Also attached the sample data and QVW file.

Regards,

Siva.

[MK9885]

What is output = 3 and 2?

Never mind, got it....

[Anonymous]

For meter ID =1, there are 3 consecutive months from the last month Dec, Nov and Oct

For meter ID =2, there are 2 consecutive months from the last month Nov and Oct and

For meter ID =3, there are 3 consecutive months from the last month Aug, Sep and Oct.

[MarcoWedel · Accepted solution]

Hi,

one solution might be;

table1:
LOAD *,
     If(BillMonth=AddMonths(Previous(BillMonth),1) and MeterID=Previous(MeterID), Peek(ConsecMonth)+1,1) as ConsecMonth
FROM [https://community.qlik.com/servlet/JiveServlet/download/1394617-306414/sample1.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco