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!
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
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!
@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