How to get the two most recent dates of every ID

[pedrobeltramese]

Hi!

 I'm struggling to analyze the following sample table in the following way:

Observation: date format "DD/MM/YYYY"

Step 1 - Input

ID	Training date	Expiration date
1	1/1/2021	1/1/2022
1	1/1/2022	1/2/2023
1	1/1/2023	1/1/2024
2	1/2/2022	1/2/2023
2	1/3/2023	1/3/2024
3	2/3/2023	2/3/2024

With this table I need to rank and filter the two most recent Training dates of every ID, if an ID has less than two register, there's no harm:
Step 2

ID	Training date	Expiration date	Rank training date
1	1/1/2022	1/2/2023	2
1	1/1/2023	1/1/2024	1
2	1/2/2022	1/2/2023	2
2	1/3/2023	1/3/2024	1
3	2/3/2023	2/3/2024	1

And, after that I want to compare the expiration training date ranked as second with the most recent training date and see if every ID joined a new training seven days before the expiration date, like this:
Step 3 - Output

ID	Training date	Expiration date	Rank training date	Renewed before expiration?
1	1/1/2022	1/2/2023	2	0
1	1/1/2023	1/1/2024	1	1
2	1/2/2022	1/2/2023	2	0
2	1/3/2023	1/3/2024	1	0
3	2/3/2023	2/3/2024	1	0

Thank you in advance!

[cjaneczko]

For Step 2 you can sort your data first by ID Ascending, then by Training Date Descending. Then add a multi row formula tool after it. In the tool use the below formula. Then add a filter tool after the multirow tool that filters anything less than or equal to 2. That should give you the data set for Step 2. As for step 3 I don't quite follow the logic. Can you provide more detail? It sounds like a simple IF formula will solve this.

if isnull([Row-1:Rank]) then 1 elseif [Row-1:ID]=[ID] then [Row-1:Rank]+1 else 1 endif

[pedrobeltramese]

Thank you for the fast answer!

 I'll try to explain it better.
 
 In the step 3 I need to I to compare the expiration date ranked as 2 for every ID with the training date ranked as 1 for the same ID, so for exemple:
ID: 4
Most recent training date: 1/3/2023
Second most recent expiration date (the previous training date expiration): 18/4/2023
Did the ID renewed the training 7 days or more before the expiration date? Yes (1) or No (0). In this case, it should be 'yes' because we have more than 7 days of difference between the most recent training date and the previous training expiration date.

Thank you again!

[ed_hayter]

@pedrobeltrameseis this what your looking for?

I parsed the dates and then did some multi-row calcs to rank and then look back and see if the ID was renewed in time.

Note that I parsed the dates as day month but if its month day you'd have to adjust the multi-field formula