Hello-

I need help with an equation that calculates headcount for our Budgeted Roster.  In this case, we have 1,667 people in our budget.  When the budget roster is transformed, the  number of lines goes up by approximately 100.  This is due to specific positions being split between departments.  i.e. John Smith might work in two areas so he would be .5 of a headcount in each department.  

The issue that we are running into deals with a Position that has two full-time employees associated with it.  One of the employees is on paid leave and the other is filling in for that person full-time.  The equation to calculate headcount is saying that each of those folks is only .5 of a person rather than 1 person - thus giving us a total headcount of 1,666 in the transform instead of 1,667.

On our Budget Roster Transform, the following equation is used to determine headcount (which should sum to 1,667).

=IF(FTE>0,1,0)/SumIf(Position, Position,1)

So this equation is saying that because the FTE (full time equivalent) for these people is 1, but there are two lines for that position, each of them are only .5 of a headcount (1 FTE/ 2 Positions).  We thought about switching the denominator to sum Names instead of Positions to get headcount, but some of the budgeted positions didn't have people associated with them yet so they are blank.  I guess we could put in "NEW POSITION 1", "NEW POSITION 2", etc if need be.

We think that if there was an equation that worked something like the following we would be able to work around this issue:

IF (FTE=1 and COUNT OF POSITION >1),

THEN (COUNT OF POSITION),

OTHERWISE (IF(FTE>0,1,0))

DIVIDED BY SumIf(Position,Position,1)

Now I know that that isn't an actual equation, but I cannot figure out how to get this idea across in the equations provided in Apptio.  Would it be possible to create an equation that does that?  Or is this something I will need to find another work around for?

Let me know if any of you have any advice!

Thanks!

=if(FTE=1 and sumif(POSITION,POSITION,1)>1,sumif(POSITION,POSITION,1),if(FTE>0,1,0))/sumif(Position,Position,1)

I think that's the formula you outlined. There may be a missing parenthesis in there somewhere...

Hi Molly,

I could be totally wrong but I figure instead of just moving on I'd give it a go! To me this looks like an issue of cardinality. I would try to base my count on some string of Position&&Name&&Department. You were on the right track with:

/snip

"We thought about switching the denominator to sum Names instead of Positions to get headcount, but some of the budgeted positions didn't have people associated with them yet so they are blank.  I guess we could put in "NEW POSITION 1", "NEW POSITION 2", etc if need be.

/snip

Hope this helps!

Good luck,

Lance

Hi @Molly Meinert!

I think these screenshots (quickly put together in Excel) relate to what you're getting at. If you concur, I think it's easy enough to translate the logic represented in the second screenshot into Apptio CT. If not, let me know and I'll come up with something.

Extending on this, I have also created an approach to track FTE values for actual heads per month taking into account start and finish dates of each person on the roster and relative to the number of working days in each fiscal period. If interested, I'm happy to share my approach.

How we calculate actual Headcount and FTE...

Before reading on, take note that we have a Date Details table which I elaborate on in this post Date Dimension Table. I have since made some minor tweaks, but the general idea of the post still very much applies.

In the original global roster table/transform (populated from a Workday extract), we perform these steps:

1. Add a CurrentDate field using
   =CurrentDate("M/d/yyyy")
2. Add a CurrentDateLastDay field using
   =Lookup(CurrentDate,Date Details,DateLookupValue,LastDayOfMonth)
3. Override imported Latest Hire Date field using
   =If($_="",Original Hire Date,$_)
4. Override imported Termination Date field using
   =If(Days(Termination Date)<Days(Latest Hire Date),"",$_)
5. Add a WorkDaysRemainingPrct field using
   =Lookup(Latest Hire Date,Date Details,DateLookupValue,WorkDaysRemInMonthPrctUSA)
6. Add an FTE field using
   =If(Days(Latest Hire Date)>Days(CurrentDateLastDay) OR Days(Termination Date)<Days(CurrentDate),0,If(WorkDaysRemainingPrct>1 OR WorkDaysRemainingPrct="" OR Days(CurrentDate)>Days(Latest Hire Date),If(WorkDaysRemainingPrct=0,0.0225,1),WorkDaysRemainingPrct))
7. Add an FTE External field using
   =If(Worker Type="Contingent Worker",FTE,0)
8. Add an FTE Internal field using
   =If(Worker Type="Employee",FTE,0)

Once the global roster table has been processed and prepped, we feed its output into a new table for Tech Roster which is filtered down to the companies/organization comprising the Technology domain of our corporation. Then, we perform these steps:

1. ITRT Detail
   =LookupEx(Company and Cost Center ID,Company Acctg Unit to ITRT,Company and AU,Return Value)
   NOTE: This multiplies the rows of our Technology labor resources intentionally to permit default ITRT assignments by cost center. This is important to note to better understand subsequent formulas for FTE/headcount calcs.
2. Alloc Percent
   =If(ITRT Detail="",1,Split(ITRT Detail,3))
3. Override FTE field using
   =$_*Alloc Percent
4. Override FTE External field using
   =$_*Alloc Percent
5. Override FTE Internal field using
   =$_*Alloc Percent
6. Filter table rows with an FTE value less than 0.001 as we consider these as zero or close enough to zero. <wink>
7. Add a new Headcount field using
   =FTE/SumIf(Employee ID,Employee ID,FTE)
8. Add a new Headcount External field using
   =If(Worker Type="Employee",0,Headcount)
9. Add a new Headcount Internal field using
   =If(Worker Type="Employee",Headcount,0)