Forum Discussion
Iterating and comparing rows
Hi Danextian - thank you, and sorry for the mistake in the sample data, one of the due dates were wrong. I have now corrected this.
Note in the sample data it is sorted by due date, in the output data it is grouped by ID and then sorted by rank.
Hello,
Where can I find the hours?
Determining projected completion dates, based on the sum of the hours of tasks ranked above them
Also, how is the projected end date computed? I am assuming that it is 7 (x rank-1) days from rank1 project.
Hi danextian - thanks for your help.
The hours = 8 hours x number of working days.
The projected completion date is the end of the previously ranked project + working days. For example ID 5, top ranked project finishes on 15/08/22. ID 5 second ranked project then has a completion date of 15/8/22 + 5 working days being 22/8/22 (due to weekend days).
I am confused how the rank is calculated. For ID5, it appears that they're ranked by priority. For 7, they are ranked by Due Date.
Hi danextian,
The logic is to rank by due date, then check if a higher prioirity task is not ontime and if so promote it up the ranks.
For ID 7 they are ranked by due date, and the higher priority task is on time, so no changes are needed.
I've played out the example process for ID 5 below.
STEP 1 - Sort by due date
AssignedToID Due Date Priority (999 = highest) WorkingDaysRemaining 5 1/08/2022 200 5 5 15/08/2022 900 10 5 20/08/2022 500 5 6 1/08/2022 100 5 6 13/08/2022 500 5 6 15/08/2022 500 5 7 5/08/2022 100 7 7 10/08/2022 100 4 7 30/08/2022 900 2 STEP 2 - Iterate and check whether on time, promote if needed.
EG.
Row 2, ONTIME = N, so check whether Row 2 is a higher priority than Row 1.
900 > 200 so move Row 2 up rank.
AssignedToID Due Date Priority (999 = highest) WorkingDaysRemaining ProjectedEndDate Ontime 5 1/08/2022 200 5 8/08/2022 N 5 15/08/2022 900 10 22/08/2022 N 5 20/08/2022 500 5 29/08/2022 N 6 1/08/2022 100 5 8/08/2022 N 6 13/08/2022 500 5 15/08/2022 N 6 15/08/2022 500 5 22/08/2022 Y 7 5/08/2022 100 7 10/08/2022 N 7 10/08/2022 100 4 16/08/2022 N 7 30/08/2022 900 2 18/08/2022 Y Then EG
Row 3, ONTIME = N, so check whether Row 3 is higher priority than Row 2.
Priority 500> 200, so move Row 3 up rank
ONTIME still = No
Check whether higher priority than Row 1
Priority 500 < 900 so do not move higher.
AssignedToID Due Date Priority (999 = highest) WorkingDaysRemaining ProjectedEndDate Ontime 5 15/08/2022 900 10 15/08/2022 Y 5 1/08/2022 200 5 22/08/2022 N 5 20/08/2022 500 5 29/08/2022 N 6 1/08/2022 100 5 8/08/2022 N 6 13/08/2022 500 5 15/08/2022 N 6 15/08/2022 500 5 22/08/2022 Y 7 5/08/2022 100 7 10/08/2022 N 7 10/08/2022 100 4 16/08/2022 N 7 30/08/2022 900 2 18/08/2022 Y Finished iteration of ID5
AssignedToID Due Date Priority (999 = highest) WorkingDaysRemaining Rank ProjectedEndDate Ontime 5 15/08/2022 900 10 1 15/08/2022 Y 5 20/08/2022 500 5 2 22/08/2022 N 5 1/08/2022 200 5 3 29/08/2022 N 6 1/08/2022 100 5 8/08/2022 N 6 13/08/2022 500 5 15/08/2022 N 6 15/08/2022 500 5 22/08/2022 Y 7 5/08/2022 100 7 10/08/2022 N 7 10/08/2022 100 4 16/08/2022 N 7 30/08/2022 900 2 18/08/2022 Y