Forum Discussion

asanch89's avatar
asanch89
Copper Contributor
Nov 15, 2024
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 ...
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    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's avatar
asanch89
Copper Contributor
Nov 15, 2024

I'm attaching the dummy view of how the data looks.

 

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Nov 16, 2024

    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.

     

    • asanch89's avatar
      asanch89
      Copper Contributor
      Nov 16, 2024

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

  • asanch89's avatar
    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's avatar
      peiyezhu
      Bronze Contributor
      Nov 16, 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's avatar
        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

Resources