Set analysis help Expression

[saumyashah90]

AsOfDate	EmployeeID	TeamCode	Region	Department	Team
2020-05-31	1111	012C	South	Retail A	Digital A
2020-05-31	2222	132V	North	Retail B	Digital B
2020-06-30	2222	472H	East	Retail C	Digital C
2020-06-30	3333	475U	East	Retail C	Digital F
2020-06-30	4444	413N	West	Retail D	Digital G
2020-07-31	2222	763I	East	Retail D	Digital H
2020-07-31	5555	472H	East	Retail C	Digital C
2020-07-30	4444	472H	East	Retail C	Digital C

This is the employee data, i want create headcount analysis.

I cannot do anything in the script. All data will just get appended anymonth.

User will select 2 months for comparison and 

They want get  New to the company, Retained , Resigned from the company, Internal transfer

this should be valid based on selection of Region, Department and Team

So when i compare June and July Month i  select  only Team (Digital C ), I should get output

New to Bank: 1 (Employee 5555)

Resigned from Bank: 0

Internal Transfer: + 1 (Employee 4444)

Internal Transfer: -1(Employee 2222)

So when i compare June and July Month i  select only Region (East) , I should get output

New to Bank: 1 (Employee 5555)
Resigned from Bank: 1 (Employee 3333)
Internal Transfer: +1 ( Employee 4444 )
Internal Transfer: -0

Note: Any two months can be selected for comparison, Jan Mar, Jan Nov, Feb Apr, June Nov, etc

Hint: Lowest grain is Team Code, If team code of employee is present and not present in last month , he is new to the bank,
if team code of employee is not present this month but present in last month, his has resigned

if team code is present for both month but different and it is same as filter selection, +ve internal transfer,

if team code is present for both month but different and it is  not same as filter selection, -ve internal transfer

I have a composite key of EmployeeID,Teamcode at the backend if useful

[sunny_talwar]

Please attach the sample back with the selections.

[saumyashah90]

Hi Sunny,

Please find attached application.

If i select only "retail C" under department.

I should get 4444 under Internal transfer not new to bank because 4444 was present in "Retail D" before.

Similarly it should work for all 3 selection combination. Currently it looks like working only for Team

[sunny_talwar]

Check now