Assigning dates a number based on yearmonth order

[HillVi]

Hi!

I am facing an issue I've not been able to resolve.

I have a table like this:

ID	Date
1	2021-01-29
1	2021-01-30
1	2021-03-03
2	2020-12-21
2	2021-01-15
3	2018-07-17
3	2018-08-10

The outcome I'm tryng to achieve is this:

ID	Date	Period_num
1	2021-01-29	1
1	2021-01-30	1
1	2021-03-03	2
2	2020-12-21	1
2	2021-01-15	2
3	2018-07-17	1
3	2018-08-10	2

In words: Dates belonging to the first present yearmonth for each ID should be 1 and dates belonging to the second yearmonth should be 2 and so on.

Does anyone have an idea on how to solve this issue?

Thanks in advance!

[chrismarlow · Accepted solution]

Hi,

If your data is in order (& assume sorted ID the Date ...), maybe;

data:
Load 
	if(RowNo()=1,
		1,
		If(Peek('ID')<>ID,
			1,
			If(MonthName(Date)<>MonthName(Peek('Date')),
				Peek('Period_num')+1,
				Peek('Period_num')
				)
			)
		) AS Period_num, 
	*;
Load * inline [
ID,	Date
1,	29/01/2021
1,	30/01/2021
1,	03/03/2021
2,	21/12/2020
2,	15/01/2021
3,	17/07/2018
3,	10/08/2018
];

Cheers,

Chris.

[HillVi]

Wow, thank you so  much Chris!

That worked like a charm.