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 ...

excel

Formulas and Functions

PeterBartholomew1

Nov 16, 2024

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

Pivoted

To 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

Change

That could be filtered to give non-zero changes only

Filtered

    FILTER(HSTACK(TAKE(DROP(PT,1),,3), change), change)

asanch89

Copper Contributor

Nov 15, 2024

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!

peiyezhu

Bronze Contributor

Nov 15, 2024

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;

asanch89
Copper Contributor
Nov 16, 2024
Thank you Peiyezhu, apologies im not familiar with SQL but the solution above from Peter works!
Thank you for the help as well to you

Forum Discussion

Variance analysis for headcount

Resources