Calculation of FTE for Days worked in Date Range

[Anonymous]

Hi All,

I am trying to calculate FTE, in the load script. Currently we are calculating it in excel by taking the days worked and dividing by the sum of days in each month that an employee worked. Example Bob startdate = 1/20/16 and enddate =4/15/16 sum of days worked would be 86. The denominator would be days in the month of Jan (31),  days in Feb (29) , days in Mar (31) ,  days in Apr (30). The end out put I would be looking for is is .4 in Jan, 1 in Feb, 1 in March and .5 in Apr. I am a little perplexed on how to go about calculating this in the load script.

Any help would be greatly appreciated.

Best,

Joseph

[swuehl · Accepted solution]

Maybe something  like

LOAD EmpID,

          MonthName,

          DaysWorked / DaysInMonth as FTE;

LOAD EmpID,

          MonthName( DateWorkedField) as MonthName,

          Count(DISTINCT DateWorkedField) as DaysWorked,

          Only(Day(MonthEnd(DateWorkedField))) as DaysInMonth

RESIDENT YourTableSource

GROUP BY EmpID, MonthName(DateWorkedField); // or replace with a Month calendar field from your input table

[Anonymous]

Hi Swuehl,

The code you provided works, but I ended up having to do an interval load, because for each FTE I only have a start and end date. I believe that the interval match is causing the error. Aggregating the data by sum of TOA(FTE), I end up with an error in the first and last month of the time series. May and Jan should be .16 and .55. The rest of the months are calculating correctly.

[swuehl]

Shouldn't it be

IntervalMatch( ValidDate, PlacementID)

LOAD DISTINCT StartDate, EndDate, PlacementID

RESIDENT ...

I think the order of field names matter in that case.

Not sure if this or something else causes the duplication for some month.

Could you post a small sample QVW?

[Anonymous]

That was precisely it. Thank you!

[Anonymous]

Hi Stefan,

I know this is an old thread, but wondering if you might have some insight into why the FTE or TOA calc would not work in a canonical calendar?

I have tried several ways to load it in with no luck.

Calculations:

            Correct calculation using date from table         Incorrect using month year from canonical calendar

Thanks in advance for the insight.

Cheers!

[swuehl]

Not quite sure, but maybe you need to filter your aggregation by the type of the canonical calendar field?

For example, in Henric's blog post

Canonical Date

the field to filter on would be DateType ('Shipped' / 'Ordered').

If this is not helping much, then please add a more complete description of your data model and some sample records, e.g. by posting a small sample QVW.

[Anonymous]

One of the things I tried initially, still no luck. Not sure if there is something that I am missing, that will not allow this to work. I attached sample QVD. Thank you for the help!

[Anonymous]

Sample QVD

[swuehl]

Your key field linking the date bridge to the TOA calculations should be granular enough to only link to the records in the TOA table that are related to that canonical date, not to all records that are related to the placement ID.