Counting changes to field values by dimension compared to last month

[zakpullen]

Hi,

I have a table that lists about 5000 employees for each month. This means the list changes monthly, as employees join or leave. What I want to do though, is be able to count employees that change departments. In the example below, I want the manager of Bits & Bobs to be able to see in a straight table (probably) that 1 employee left their department last month. The manager will have January 2020 selected.

I don't know if there is an efficient way to do this in the front end,  I was thinking of something in the load script.

Any ideas welcome.

Many thanks.

[Arthur_Fong]

Try this:

Script:

Raw:
load * inline[
MonthYear,EmployeeID,Department
Dec-19,12345,B&B
Dec-19,22345,B&B
Dec-19,34567,B&B
Dec-19,45678,O&S
Dec-19,56789,O&S
Jan-20,22345,B&B
Jan-20,12345,B&B
Jan-20,34567,O&S
Jan-20,45678,O&S
Jan-20,56789,O&S
];

NoConcatenate
Data:
load Date#(MonthYear,'MMM-YY')as MonthYear,EmployeeID,Department
RESIDENT Raw
order by EmployeeID asc,
		MonthYear asc;
		
drop table Raw;

Data2:
load *,if(peek(EmployeeID)=EmployeeID and Department<>peek(Department),1,0)as CountLeft
resident Data
order by EmployeeID asc,
		MonthYear asc;


drop table Data;
exit script;

[zakpullen]

Thanks Arthur, it's very close, but what I would want to show is the 1 against the old department (B&B) in Jan 2020 rather than against the new one (O&S). The idea is that the manager of B&B will know how many employees moved to another department.

Best wishes.

[Kushal_Chawda]

Data:
Load *,
     date(Date#(MonthYear,'MMM-YY'),'MMM-YY') as YearMonth;
load * inline [
MonthYear,EmployeeID,Department
Dec-19,12345,B&B
Dec-19,22345,B&B
Dec-19,34567,B&B
Dec-19,45678,O&S
Dec-19,56789,O&S
Jan-20,22345,B&B
Jan-20,12345,B&B
Jan-20,34567,O&S
Jan-20,45678,O&S
Jan-20,56789,O&S
Jan-21,34567,S&S
];

drop field MonthYear;

Final:
Load *,
     if(EmployeeID=Previous(EmployeeID) and Department<>Previous(Department), 1,0) as EmployeeFlag
Resident Data
Order by EmployeeID,YearMonth desc;

Drop Table Data;

Now you can create a report with Dimension  YearMonth and expression as below

=count({<EmployeeFlag={1}>} distinct EmployeeID)

[Brett_Bleess]

Zak, did any of the other posts get you what you needed?  If so, please be sure you return to the thread and use the Accept as Solution button on that post to give them credit for the assistance and to let other Members know what worked.  If you are still working on things, please leave an update, and if you did something else, please consider posting that and then mark that as the solution.

I do have a Design Blog post that I think may be helpful on this one too in addition to the other posts:

https://community.qlik.com/t5/Qlik-Design-Blog/Slowly-Changing-Dimensions/ba-p/1464187

Regards,
Brett