I have assigned all people in my files who have a "Movement Type" to a cycle that corresponds to the month of July as a start till June in the next year.
People without a movement do not have a cycle.
Load *,
YearName("Effective Date", 0, 7) as "Cycle"
;
As Is example:
ID
Status
Name
Effective Date
Movement Type
Cycle
1002
Active
John
20/02/2017
Promotion
2016-2017
1002
Active
John
20/05/2018
Promotion
2017-2018
2054
Active
Patrick
-
-
-
5432
Active
Lena
20/04/2018
Promotion
2017-2018
5432
Active
Lena
20/09/2018
Promotion
2018-2019
3245
Withdrawn
Johnny
20/03/2017
Termination
2016-2017
What I need now is the following when filtered for cycle 2018-2019:
ID
Status
Name
Effective Date
Movement Type
Cycle
1002
Active
John
-
-
2018-2019
2054
Active
Patrick
-
-
2018-2019
5432
Active
Lena
20/09/2018
Promotion
2018-2019
Because John has an active status, he is still part of the cycle 2018-2019, however in this cycle he does not have a movement type.
So when I filter for cycle '2018-2019' I would like him to show up like in the 2nd table.
