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

Counting changes to field values by dimension compared to last month

zakpullen — Sat, 16 Nov 2024 19:20:13 GMT

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.

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

Arthur_Fong — Fri, 10 Jan 2020 03:39:29 GMT

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;

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

zakpullen — Fri, 10 Jan 2020 14:58:36 GMT

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.

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

Kushal_Chawda — Fri, 10 Jan 2020 15:39:16 GMT

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)

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

Brett_Bleess — Mon, 10 Feb 2020 20:38:53 GMT

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