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

[RoxanneR]

Hi, I am grouping by 2 columns. 

Do you want the Tile to start with 2 below because 1/5/2023 is missing- to reply to this, I want Tile to assign the sequence numbers only when when the status is the same for those records and the months are in the order without the gaps.

What would the expected Tile look like if multiple dates were missing within a group, maybe a missing date range? - as I mention in the example, is there is a gap between the dates, we should treat them as different records. 

The thing is most of my dataset does have the dates in order so the solution with Tile works in like 98% but when there is date missing in between of months the logic isn't working as it counts them all together for the same ID and with the Active status that shouldnt be the case.

To give you some context, later on I am calculating the difference in months and if the Tile tool adds up the months that aren't in order

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

I get the result of 5 months which is wrong because I would expect to see only 1 month (since there are gaps that means no data available)

[ChrisTX]

For the most recent example you posted, how would you get the same Tile number for all of those records, if you are Grouping by the first two columns?

The value in the first column, first row is different from first column, second row.  This would result in a different Tile number.

Chris

[RoxanneR]

You're right , its a typo. The report type is the same for the those records.