How to compare data with in the same column for different months?

[qlik_j]

Hi Experts, 

I need some help please. I am trying to compare the head count between year-end 2018 (December 2018) with Nov 2019 (Max year/month that I have uploaded). I created a sample table for reference.

Month	Employee_ID	Employe_Name
Dec-18	1100	Adam
Dec-18	1101	Alex
Dec-18	1112	David
Dec-18	1115	John
Jan-19	1100	Adam
Jan-19	1101	Alex
Jan-19	1112	David
etc.	etc.	etc.
etc.	etc.	etc.
etc.	etc.	etc.
etc.	etc.	etc.
Nov-19	1100	Adam
Nov-19	1112	David
Nov-19	1200	Ken
Nov-19	1201	Kevin
Nov-19	1202	Sam

I would like to somehow compare or match Employee_ID for the two months. If Employee_ID appears in Dec-2018 and not in Nov-2019 that means its a Termination.  And if the Employee_ID appears in Nov-19 and not in Dec-18 it is an Addition. End goal is to get the count of Termination and Addition based on the above logic. So comparing the two months (dec 2018 and Nov 2019) we should get 3 additions (Employee_ID: 1200, 1201, 1202) and 2 terminations (Employee_ID: 1101,1115).

I will eventually put this in a waterfall. 

Can someone please help me with it. Thanks 🙂

[dwforest]

This would be easiest in the load script. Something like:

[Emp Temp]:

LOAD

     Employee_ID, Month,Employee_Name,If(Previous(Employee_ID)=Employee_ID,If(isnull(Previous(Month),1) AS Addition;

LOAD

Employee_ID,Month,Employee_Name

FROM Table1

ORDER BY Employee_ID,Month;

[Emp]:

NoConcatenate

LOAD Employee_ID,Month,Employee_Name, Addition, If(Isnull(Previous(Month),1) AS Termination

RESIDENT [Emp Tem]

ORDER BY Employee_D, Month desc;