Multi Row Formula Group ID

[PeterAP]

I've created a multi-row formula having grouped by 2 fields and then using the following expression.

IF DateTimeDiff([Doc Date],[Row-1:Doc Date],'days')<=1
THEN "Yes"
ELSEIF DateTimeDiff([Doc Date],[Row+1:Doc Date],'days')>=-1
THEN "Yes"
ELSE ""
ENDIF

What I want to be able to do is give them a group ID based on whether or not they form part of the same group where a record is within one day of each other.

For example, the current result in the below is "Yes" and that matches the criteria, however, I want to group them relative to the others hits i.e. the 3rd row below is not within 1 day of the 2nd row and therefore a new group (e.g. "Yes2" should be given to it.

Field 1	Field 2	Date	Current Result	Desired Result
A	B	30/06/2020	Yes	Yes1
A	B	01/07/2020	Yes	Yes1
A	B	07/07/2020	Yes	Yes2
A	B	07/07/2020	Yes	Yes2

Is anyone able to help me with this?

Thanks

[fmvizcaino]

Hi @PeterAP ,

I can help you building the expression but I need to understand if something like below happens:

Field 1	Field 2	Date	Current Result	Desired Result
A	B	30/06/2020	Yes	Yes1
A	B	01/07/2020	Yes	Yes1
A	B	07/07/2020	Yes	Yes2
A	B	07/07/2020	Yes	Yes2
A	B	08/07/2020	Yes	Yes2
A	B	09/07/2020	Yes

08/07 is within the second group, but what about the next date and so on? 

Best,

Fernando Vizcaino

[seven · Accepted solution]

Hi,

try this and let me know how it goes.

[PeterAP]

Thanks @seven,

That worked perfectly.

I used a filter from my original multi field formula to extract only those which met my criteria first and then used the multi-field formula you sent to group these.