Forum Discussion
Solved
Variance analysis for headcount
Dears, I've been struggle to find a way to do a variance analysis on the employees of my company. The aim is that I can dynamically select two periods (month 3>March vs month 1>January) and identify who has joined the company, left the company or change departments. I don't know what the best option is to accomplish this. I have a dummy table called FTE table and the output could look as a sort of pivot table where i can select somewhere the Current Period and the Comparator period. Similarly could be done with some helper columns in the original table which can then later be filtered perhaps? Really struggling to get this. I've tried some countifs, asked chat gpt for and recommended the use of the LET function but didn't work out. Appreciate the help as I've been already more than 10hrs on this and unsuccessful.
The dummy data has a column for employee code (unique identifier), name, surname, month, department and FTE (usually 1 if the employee worked full time). The data is downloaded every month.
This requires Excel 365 insider at present. The formula
= LET( employee, CHOOSECOLS(Table2[#All], 1, 5, 2), month, Table2[[#All],[Month]], FTE, Table2[[#All],[FTE]], PT, PIVOTBY(employee, month, FTE, SUM,,0,,0), PT )will create a pivoted array
PivotedTo calculate the difference between month 1 and month 3, adds to the length of the formula
= LET( employee, CHOOSECOLS(Table2[#All], 1, 5, 2), month, Table2[[#All],[Month]], FTE, Table2[[#All],[FTE]], PT, PIVOTBY(employee, month, FTE, SUM,,0,,0), onroll, DROP(PT, 1,3), counts, IF(onroll<>"", VALUE(onroll), 0), change, TAKE(counts,,-1) - TAKE(counts,,1), HSTACK(TAKE(DROP(PT,1),,3), change) )giving
ChangeThat could be filtered to give non-zero changes only
FilteredFILTER(HSTACK(TAKE(DROP(PT,1),,3), change), change)
This requires Excel 365 insider at present. The formula
= LET( employee, CHOOSECOLS(Table2[#All], 1, 5, 2), month, Table2[[#All],[Month]], FTE, Table2[[#All],[FTE]], PT, PIVOTBY(employee, month, FTE, SUM,,0,,0), PT )will create a pivoted array
PivotedTo calculate the difference between month 1 and month 3, adds to the length of the formula
= LET( employee, CHOOSECOLS(Table2[#All], 1, 5, 2), month, Table2[[#All],[Month]], FTE, Table2[[#All],[FTE]], PT, PIVOTBY(employee, month, FTE, SUM,,0,,0), onroll, DROP(PT, 1,3), counts, IF(onroll<>"", VALUE(onroll), 0), change, TAKE(counts,,-1) - TAKE(counts,,1), HSTACK(TAKE(DROP(PT,1),,3), change) )giving
ChangeThat could be filtered to give non-zero changes only
FilteredFILTER(HSTACK(TAKE(DROP(PT,1),,3), change), change)The file for any who can use it
Fantastic Peter! My lord this was pretty incredible. Many thanks!!!!
I'm attaching the dummy view of how the data looks.
Did you consider a pivot table? Example attached.
It allows you to see all changes that took place. People joining, leaving and transferring between departments. And in your example, you even see that Andres and Catherine have the same employee code. Something that would probably not happen in real life.
Riny thanks for your reply. Certainly the code btw Andres and Catherine is my error putting the dummy data. Ideally, a pivot would work but it doesnt display the variance which is the one I'm interested. Furthermore, I have probably 300 people in the company... tks!!!
For instance in the data set I would like to see Aurelie as "changed", Lode as "no change", Michiel "no change", Catherine as "new" and Andres for instance would have left the company so not sure how to capture that one. Appreciate all the help!
SQL:
select * from Sheet1 limit 20;
select f01 changed,group_concat(f03) from Sheet1 group by f01 having(count(distinct(f03)))>1;
create temp table aa as
select distinct f01 from Sheet1 where f02 not in (select max(f02) from Sheet1);
create temp table bb as
select * from Sheet1 where f02 in (select max(f02) from Sheet1);
select aa.f01 leave from aa left join bb using(f01) where bb.f01 is null;
select bb.f01 new from bb left join aa using(f01) where aa.f01 is null;
