Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor II

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
Highlighted
Partner
Partner

Re: Counting changes to field values by dimension compared to last month

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;
Highlighted
New Contributor II

Re: Counting changes to field values by dimension compared to last month

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.

Highlighted

Re: Counting changes to field values by dimension compared to last month

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)

Highlighted
Digital Support
Digital Support

Re: Counting changes to field values by dimension compared to last month

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.