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?
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?
T
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?
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:
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 thank you for your help so far but rows with 2 only consecutive months are getting 3 con as output
@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':