Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
What is output = 3 and 2?
Never mind, got it....
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.
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