Get Previous and next record based on weekend condition

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

Workflow

Regex

Date Time

Dynamic Processing

Accepted answers

binu_acs

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

Dynamic_Day_values_workflow.yxmd

All comments

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.

Dynamic days around weekend.yxmd

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...