topic Add only consecutive month's data using flag in QlikView

Add only consecutive month's data using flag

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

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.

Re: Add only consecutive month's data using flag

MK9885 — Mon, 04 Dec 2017 20:14:17 GMT

What is output = 3 and 2?

Never mind, got it....

Re: Add only consecutive month's data using flag

Anonymous — Mon, 04 Dec 2017 20:29:11 GMT

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.

Re: Add only consecutive month's data using flag

MarcoWedel — Tue, 05 Dec 2017 01:40:42 GMT

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