Count months only if they are in order

RoxanneR

Please help!

How to approach the dataset in the below situation 

Tile Configuration:

Tile Method “Unique Value” 

Unique Column “Status of Consumer”

Grouped by Columns “ID of Consumer”

Example where the tool works fine 

Report Type	ID of Consumer	Status of Consumer	Reporting period log	Time_Num	Tile_SequenceNum
Summary	100	Active	01/08/2023	1	1
Summary	100	Active	01/09/2023	1	2
Summary	100	Active	01/10/2023	1	3
Summary	100	Active	01/11/2023	1	4
Summary	100	Active	01/12/2023	1	5

But in the example below the data shouldn't be grouped like that In Tile because there are gaps in the Reporting period log, so it should calculate only the months if they go in sequence.

Example where logic doesn’t work -

Report Type	ID of Consumer	Status of Consumer	Reporting period log	Time_Num	Tile_SequenceNum
Briefs	100830	Active	01/02/2023	1	1
Summary	100830	Active	01/04/2023	1	2
Summary	100830	Active	01/06/2023	1	3
Summary	100830	Active	01/10/2023	1	4
Summary	100830	Active	01/12/2023	1	5

Expected output: 

If months aren’t in sequence it should be new new Tile num assigned

Report Type	ID of Consumer	Status of Consumer	Reporting period log	Time_Num	Tile_SequenceNum
Summary	100	Active	01/08/2023	1	1
Summary	100	Active	01/09/2023	1	2
Summary	100	Active	01/10/2023	1	3
Summary	100	Active	01/11/2023	1	4
Summary	100	Active	01/12/2023	1	5
Briefs	100830	Active	01/02/2023	1	1
Summary	100830	Active	01/04/2023	2	1
Summary	100830	Active	01/06/2023	3	1
Summary	100830	Active	01/10/2023	4	1
Summary	100830	Active	01/12/2023	5	1

Thanks!

ChrisTX

Is your grouping by the first two fields?

Do you want the Tile to start with 2 below because 1/5/2023 is missing?

What would the expected Tile look like if multiple dates were missing within a group, maybe a missing date range?

I'm thinking you may want to use the Multi-Row Formula tool instead of Tile so you can write code, and you may need the Generate Rows tool.

Summary	100830	Active	01/04/2023	2	1
Summary	100830	Active	01/06/2023	3	1

Chris