Movement of Employees across Departments

[Anonymous]

Hi Everybody,

I have a scenario where requirement is to show the YoY (Year over Year) movement of Employees across Departments.

Some scenarios: Consider we are doing 2013 vs 2014 comparison

* An Employee was at Dept level D1 in 2013. In 2014, he/ she moved to D2

* An Employee was at same Dept level in both 2013 to 2014

This has to be depicted in the below format.

Dept (2013 / 2014)	D1	D2	D3	D4
D1	<count of Emp who stayed in D1>	<count of Emp who moved from D1 to D2>
D2
D3
D4

Request your inputs in solving this.

Thanks,

Vijit

[morganaaron]

It will depend on your data model.

You could create two fields to pick up the department of each employee at the set point you want to look at, have the fields read D1, D2, D3 or D4 for each year and then use these two fields as your dimensions and a count(employee) as your expression in a pivot table.

[avinashelite]

if your storing the dept movement data in DB i.e the dates of the movements , then you can easily do the comparison based on the dates and previous dept reference ...If you dont have dates than you need to capture the data first then you you need to work on this 

[Anonymous]

Hi Avinash,

I have the dates with me. What do you mean by the previous dept reference?

Regards,

Vijit

[Anonymous]

Hey Aaron,

Could you help me with some more details onto this?

Regards,

Vijit

[morganaaron]

Yeah sure - can you share your model? Or at least explain what tables you've got and how you hold the data?

[avinashelite]

reference means previously he was working in which department and currently which department he is working .

can you please share the sample data so that we can help you

[Kushal_Chawda]

Hi,

As it is difficult to achieve from front end for me I have done in back end. I have hard coded the year for current vs prev year comparison in the script. Also I have considered the data at year level

Rows increased are only the rows for which Employees dept changed. So for 3 Years if 10 Employees changed the Dept, 10 rows will be increased including current records.

[Anonymous]

Hi Vijit,

Please find attached application.

Simple additions in script and using pivot table the appropriate way gives the result.

[Anonymous]

Hi Vijit,

Is the solution correct for your scenario?