2 Replies Latest reply on Feb 26, 2018 9:58 AM by John Powell

    Accurate head count for KPIs

    John Powell

      Hi all

       

      I'm really struggling with this but I feel there must be a way of achieving it that I'm missing!

       

      What I need is an accurate head count per month / week / day based on activity.  Then we take the measures from the SQL (i.e. how many CV's sent out, jobs logged etc.) and divide that by the head count number.

       

      Ideally what I need is the head count monthly to calculate against daily measures.  I've tried using a date scaffold in weeks and months but because the data coming out of the CRM is daily (which it will need to be) I can't tie the 2 together.  I've got a spreadsheet with the correct head counts per week and month but again I can't get the dates to tie together even if I expand that out into days.

       

      The work book that is attached is not the one I'm using as haven't got far enough with it to add here but it is using the same data source.  I've used a calculated field as:

       

      IF [1stInterviews] > 0 THEN '1'

      ELSEIF [2ndInterviews] > 0 THEN '1'

      ELSEIF [3rdInterviews] > 0 THEN '1'

      ELSEIF [BD Emails] > 0 THEN '1'

      (There are more here but don't want to overcrowd the post)

      ELSE '0'

      END

       

      I.E. if any one of those measures is greater than 0 then they must be active and so give it a 1 and then I count that for headcount.  However what it's then doing is counting a 1 for every day of that month so I get an inaccurate number.  I'm getting results of 44,857 active people for say Jan 2017 when I know that to be more like 450.  I tried breaking that down to 1 measure I know is used daily but get the same name (albeit rather unsurprisingly).

       

      There must be a way to drill down to this level but I'm just missing it entirely.

       

      Can anyone help?

        • 1. Re: Accurate head count for KPIs
          John Powell

          For those seeing his post this was the how I did it in the end.

           

          First to work out if someone was active on a given day (names ActiveGET6):

          IF [1stInterviews] > 0 THEN [Consultant Ref]

          ELSEIF [2ndInterviews] > 0 THEN [Consultant Ref]

          ELSEIF [3rdInterviews] > 0 THEN [Consultant Ref]

          ELSEIF [BD Emails] > 0 THEN [Consultant Ref]

          ELSEIF [Candidate Calls] > 0 THEN [Consultant Ref]

          ELSEIF [Candidate Meeting] > 0 THEN [Consultant Ref]

          ELSEIF [Candidates Added] > 0 THEN [Consultant Ref]

          ELSEIF [Candidates Added Contacted] > 0 THEN [Consultant Ref]

          ELSEIF [Contract Extensions] > 0 THEN [Consultant Ref]

          ELSEIF [Contractor Monitor Calls] > 0 THEN [Consultant Ref]

          ELSEIF [Emails] > 0 THEN [Consultant Ref]

          ELSEIF [First Interviews Added] > 0 THEN [Consultant Ref]

          ELSEIF [Interviews Created] > 0 THEN [Consultant Ref]

          ELSEIF [Jobs] > 0 THEN [Consultant Ref]

          ELSEIF [Jobs Qualified] > 0 THEN [Consultant Ref]

          ELSEIF [Jobs1 3CandidateInterview] > 0 THEN [Consultant Ref]

          ELSEIF [Jobs1PlusCV] > 0 THEN [Consultant Ref]

          ELSEIF [Jobs1PlusInterview] > 0 THEN [Consultant Ref]

          ELSEIF [Jobs1PlusOffer] > 0 THEN [Consultant Ref]

          ELSEIF [Jobs3PlusCV] > 0 THEN [Consultant Ref]

          ELSEIF [Jobs4PlusCandidateInterview] > 0 THEN [Consultant Ref]

          ELSEIF [JobsExactly0CV] > 0 THEN [Consultant Ref]

          ELSEIF [Left Messages] > 0 THEN [Consultant Ref]

          ELSEIF [Live C Vs] > 0 THEN [Consultant Ref]

          ELSEIF [Live C Vs To Spec Job] > 0 THEN [Consultant Ref]

          ELSEIF [Mailshots] > 0 THEN [Consultant Ref]

          ELSEIF [Offers] > 0 THEN [Consultant Ref]

          ELSEIF [Other Interviews] > 0 THEN [Consultant Ref]

          ELSEIF [Reference Calls] > 0 THEN [Consultant Ref]

          ELSEIF [Successful Offers] > 0 THEN [Consultant Ref]

          ELSEIF [Unsuccessful Offers] > 0 THEN [Consultant Ref]

          ELSEIF [Target C Vs] > 0 THEN [Consultant Ref]

          ELSEIF [Target C Vs New] > 0 THEN [Consultant Ref]

          ELSE NULL

          END

           

          Then to get the head count:

           

          {FIXED [Consultant Ref],[_Date]:SUM([ActiveGET6])}

           

          Then just dividing those measures used in ActiveGET6 by the headcount.

          • 2. Re: Accurate head count for KPIs
            John Powell

            Updated workbook attached if you want to look through