Creating filter column that puts a flag en every record if there were 4 consecutive same categories

[Anonymous]

Hello everyone, I'm trying to create a calculated column in a table to then use it as a filter in a report.

I have two posible categories "A" and "B", I need to put a yes in the calculated column if I have four consecutive A´s for the same name (in each of the rows with the consecutive A's, furthermore, if there are more than 4 consecutive A's, this rows should also get yes as a result. 

Following a table example with the column flag showing the expected result for this calculated column:

date	name	category	flag
01-01-23	Oscar	A	yes
02-01-23	Oscar	A	yes
03-01-23	Oscar	A	yes
04-01-23	Oscar	A	yes
05-01-23	Oscar	A	yes
01-01-23	Juan	B	no
02-01-23	Juan	B	no
03-01-23	Juan	B	no
04-01-23	Juan	A	no
05-01-23	Juan	B	no
01-01-23	Bryan	B	no
02-01-23	Bryan	B	no
03-01-23	Bryan	B	no
04-01-23	Bryan	B	no
05-01-23	Bryan	A	yes
06-01-23	Bryan	A	yes
07-01-23	Bryan	A	yes
08-01-23	Bryan	A	yes
01-01-23	Jessica	A	no
02-01-23	Jessica	A	no
03-01-23	Jessica	B	no
04-01-23	Jessica	A	yes
05-01-23	Jessica	A	yes
06-01-23	Jessica	A	yes
07-01-23	Jessica	A	yes

Thanks very much in advance.

[johnyip]

Do you need to have this calculated column do the calculation based on the row order of your raw data, or the order the records are displayed in a table/matrix vis?

[Anonymous]

hi, the order of the records is based on the date column