Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
zakpullen
Contributor III
Contributor III

Counting changes to field values by dimension compared to last month

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.

 

 

tbl2.JPG

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.

Labels (4)
4 Replies
Arthur_Fong
Partner
Partner

Try this:

MC.PNG

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
Contributor III
Contributor III
Author

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
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.