how to compare status with last period

[Masi_Sahargahi]

 Hi all

I have a table which shows all personnel information for each months

I want to make a report as below:

The number of the employees which are retired this year. But I don't have retire date

So I have to get the number of employees which their status is "retired" in the selected year but their status in last month of last year is "working"

How can I handle that?

Fields:

Employee_id

Employee_status

Calender_year

[Gysbert_Wassenaar]

In the load script sort the data by employee, year and month. Then check using the Previous() function if the status of the previous month was 'working' and the status of the current month is 'retired'. If you find the month that matches those conditions you know in which month the employee was retired. You can flag those months in a new field for that purpose. Then you can use that flag field to count the retired employees