Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Add only consecutive month's data using flag

Hi guys,

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

Screenshot_1.png

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.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution might be;

QlikCommunity_Thread_283924_Pic2.JPG

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

View solution in original post

3 Replies
MK9885
Master II
Master II

What is output = 3 and 2?

Never mind, got it....

Anonymous
Not applicable
Author

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

Hi,

one solution might be;

QlikCommunity_Thread_283924_Pic2.JPG

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