How to update value of the previous row based on conditional statement?

[sheidari]

How can I update/change the value of the previous row based on an IF statement? I'm using a multi-row formula tool to flag records that match on date (if date matches then 1 else 0) but it only flags the current record. I also want to flag the prior matching record as well.  

Is this possible and if so, how??

Thanks!!

Shahab

[messi007]

@sheidari,

Could you share a sample data with the expected output? It will help us to give you the expected solution.

Regards,

[sheidari]

Here you go, @messi007 !

Current Output
Name	Date	Code	SameMonthFlag
John Doe	11/1/2018	1111	0
John Doe	11/2/2018	1112	1
Jack Johnson	12/1/2019	1100	0
Bob Jones	2/1/2020	1024	0
Bob Jones	2/1/2020	1027	1
Expected Output
Name	Date	Code	SameMonthFlag
John Doe	11/1/2018	1111	1
John Doe	11/2/2018	1112	1
Jack Johnson	12/1/2019	1100	0
Bob Jones	2/1/2020	1024	1
Bob Jones	2/1/2020	1027	1

[messi007]

@sheidari,

Here you go:

Attached the workflow,

If this helps please make it as a solution it will help others,

Regards,

[danilang]

Hi @sheidari 

Compare the current row to the row before and also the row after 

If datetimemonth(datetimeparse([date],"%m/%d/%Y")) = datetimemonth(datetimeparse([row-1:date],"%m/%d/%Y")) 
or 
   datetimemonth(datetimeparse([date],"%m/%d/%Y")) = datetimemonth(datetimeparse([row+1:date],"%m/%d/%Y"))  then
	1
else
	0
endif

Dan

[sheidari]

@messi007  and @danilang thanks for the suggestions but maybe i didn't explain the problem clearly. 

Here's a better explanation. The raw data does not have the "samemonthflag" field.

Raw Data
Name	Date	Code
John Doe	11/1/2018	1111
John Doe	11/1/2018	1112
Jack Johnson	12/1/2019	1100
Bob Jones	2/1/2020	1024
Bob Jones	2/1/2020	1027

I created the following multi-row formula to create the samemonthflag field:

But my output resulted in this: as you can see, I only want to flag individuals where they have a duplicate date (i.e. John Doe's 1st record should = 1 and so should Bob Jones 1st record. 

Current Output
Name	Date	Code	SameMonthFlag
John Doe	11/1/2018	1111	0
John Doe	11/1/2018	1112	1
Jack Johnson	12/1/2019	1100	0
Bob Jones	2/1/2020	1024	0
Bob Jones	2/1/2020	1027	1

The final result should look like this but i can't seem to figure out how to make the prior record a '1' if there's a duplicate date for that individual:

Expected Output
Name	Date	Code	SameMonthFlag
John Doe	11/1/2018	1111	1
John Doe	11/1/2018	1112	1
Jack Johnson	12/1/2019	1100	0
Bob Jones	2/1/2020	1024	1
Bob Jones	2/1/2020	1027	1

[afv2688 · Accepted solution]

Hello @sheidari ,

You can use the following formula:

IF [Row+1:Date] = [Date] OR [Date]=[Row-1:Date] THEN 1 ELSE 0 ENDIF

Regards

[messi007 · Accepted solution]

@sheidari,

Oh my bad yes, I updated the formula accordingly

Attached the workflow,

Regards,