Get Previous and next record based on weekend condition

[gurpreet_alteryx]

Hi, I would like to get the previous and next record based on condition. I have dataset that has date and day field, I need to check for 20th each month and select the previous and next record based on weekend. 

For example, if 20th is on Friday , then I need to take 19 and 20th, but if 20th is on Saturday then need to take 19th (Friday) and 22 (Monday) as flag. I have created a index column to select particular date. Please help me on this I tried to do using multi row formula, but it's not working. 

Thanks 

[KGT]

The Multi-row is what you're after if I'm understanding correctly. I'm assuming you have a row for each day. This solution could be made smaller, but I'll separate the elements.

• RecordID
• Create a field for Day of Week: DoW = DateTimeFormat([Date], '%A')
• Create a field for Day of Month: DayOfMonth = DateTimeFormat([Date],'%d')
• Create a field for additional row in a multi-row formula:
  • First bump up the number of rows to 3 instead of the default 1 (Top left of configuration tab). 
  • IF DayOfMonth=="20" && DoW=="Saturday" THEN [Row-1:Value]+[Row+2:Value]
    ELSEIF DayOfMonth=="20" && DoW=="Friday" THEN [Row-1:Value]+[Value]
    ELSEIF DayOfMonth=="20" && DoW=="Sunday" THEN [Row-2:Value]+[Row+1:Value]
    ELSEIF DayOfMonth=="20" && DoW=="Monday" THEN [Row-3:Value]+[Value]
    ELSE [Value]+[Row-1:Value]
    ENDIF

This should give you an idea of the operations and then you can shorten the whole thing in to one formula if you really want.

[gurpreet_alteryx]

Hi KGT, Thanks for your reply. Actually I already have index (day_of_month) and name week days in my dataset. For example, I need the flag 1 in 22nd Jan 2024 as 20th Jan is Saturday , so need to consider 19th and 22nd as flag 1. I have attached the workflow and sample data with expected output for your reference. Many Thanks...

[binuacs · Accepted solution]

@gurpreet_alteryx you need to write conditions for each of your requirement in the multil-row tool

[KGT]

So, you just need to flag the relevant rows, not add the values?

Then can't you just write an expression for each day as the days of the week don't change?

IF [DoW]=="Friday" && [Day]>=19 && [Day]<=21

THEN 1

ELSEIF  [DoW] IN ("Saturday","Sunday")

THEN 0

ELSEIF [Day]>=19 && [Day]<=20

THEN 1

ELSE 0

ENDIF

[gurpreet_alteryx]

@binuacs  

Thank you so much, I added couple of conditions and it worked perfectly. Thanks again...

[gurpreet_alteryx]

Thanks @KGT , I tried this way, but unfortunately didn't work. 

[DataPirate26]

@gurpreet_alteryx Please see the below solution and let me know if this is what it was required 

[gurpreet_alteryx]

Thanks, it worked but in some scenario it didn't capture correctly.