Calculations

UMAMAHESH
UMAMAHESH
in Modeling

Hi experts,

 

we mainatained joining date, Resigned date and last working day  properties in employee dimension.

 

Based on those properties we need to calculate previous 6 months Organization Active employees and resigned employees count.

 

We create one module called headcount planning module.In these scenario current period is july-2018.

333333.PNG

In these module we need to show the Position wise,Location wise actual headcount data. By using Joing date, resigned date and last working day properties we need to calculate data.

 

For eg: in Jan 18 100 employee joined in the organization and 50 employees are left. Then in jan month actual headcount is "Currently working employees+Jan recruited employees-Jan left employees.

Same way we need to calculate previous 6 months actual Headcount data need to be display.

 

Hope you undestand.

 

can you please suggest me.

 

Regards,

Uma

 

Tagged:

Best Answer

  • usman.zia
    usman.zia
    Answer ✓

    Hi Uma, 

     

    You need to create an employee event schedule which will show you if the employee is active (i.e employed) or not. 

    The schedule module will be dimensioned by each employee and time and then in the module where you want to create an active headcount you can lookup the employee type property to sum the headcount. You could then have two line items active employee and notice period active. The active employee would be the start date to last working date. The notice period active would be the resigned date to the last working.

     

    Assuming:

    Just to confirm resigned date is when they give in their notice?

    And last working day is the date of last employment? 

     

    You then need to define is it the first month? of the plan in a time dimensioned module (could be settings module). This requires 2 line items:

    Month - formula = MONTH(ITEM(Time))       Format Number  

    First Month - formula = LAG(Month, 1, 0) = 0    Format Boolean 

     

    The calculations for the employee event schedule would be: 

    IF ZZZ < XXX THEN 0 ELSE IF WWW AND INPERIOD(ZZZ) THEN (ZZZ - START() + 1) / DAYS() ELSE MIN(1, DECUMULATE(MAX(0, END() - YYY + 1) - MAX(0, END() - ZZZ)) / DAYS())

     

    When ZZZ = End of event date (date format) and YYY = (Start of event date) and XXX = (Planning start date) and WWW = (First month line item as above).

     

    This will give an exact proportion of their employment.

    To get the previous 6 months you just need to calculate 'Month' line item formula offset by 6 months. 

     

    I hope this helps and if you have any questions please do let me know. 

     

    Thanks,

     

    Usman

     

Answers

Categories