Help with output

[Tundun]

Hi, I need help with my formula. I am looking for 3 consecutive month by id in my dataset. I used this formula:

IF ([Month]-1=[Row-1:Month] and [Month]+1=

[Row+1:Month]) THEN "3 con" ELSE "non con" ENDIF

but it’s not giving me the output I want.

I want the output of the second attachment but I am getting the output of the first. How can I fix the formula?

[DataNath]

Hey @Tundun, looks like your logic is falling down when looking a row ahead when you get to the max month for each group, as there's nothing to lookup beyond that and so the = check will fail giving the 'non con' flag. Therefore, I've brought in the max Month per ID and built in an extra check within the Multi-Row expression. Are you able to check this against your true dataset?

[Tundun]

T

[Tundun]

Thank you for your reply @DataNath. I tried with my dataset. The logic didn’t work. This is the output I got with my dataset. Is there any other way to get this output?

 

[DataNath]

Hey @Tundun, can I just check what the expected outcome is here? I've had another look but not certain what the output should be, especially with months lacking a full 3 =< month range:

[Tundun]

Hi @dataNath, I want output grouping by id of months with 3 consecutive or greater consecutive months to be 3 con and months with less than 3 consecutive month should be non-con.

like this output 

 

[DataNath]

@Tundun 3rd time lucky? Have included 2 options for you to test:

[Tundun]

@datanath thank you for your help so far but rows with 2 only consecutive months are getting 3 con as output 

[DataNath]

@Tundun are you able to share an example? Your ID 5 has only 2 (consecutive) rows, and I added another column - 9 - with this scenario, and they're all 'non con':