Yearly Turn over calculated based on average

[madushanfernand]

Hi

I have a data set as follows. My requirement is to get Yearly Turn over rate.

The fields to be used for the calculations are EMP_ID, EMP_START_DATE , EMP_END_DATE, EMP_POS_VALID_FROM_DATE and EMP_POS_VALID_TO_DATE.

The Calculation should be as follows.

For year 2018, Yearly turn over rate = average of monthly turn over

( average of monthly turn over = ( JAN turn over + FEB turn over + ...  + AUG turn over ) / Number of Months up to date )

for JAN turn over = total employees left in JAN / total active in JAN

     total employees left in JAN = count( DISTINCT if (1/01/2018<=EMP_END_DATE and EMP_END_DATE<=31/01/2018 , EMP_ID))

     Total active in JAN       = count( DISTINCT if (EMP_START_DATE <= 31/01/2018 and 31/01/2018<EMP_END_DATE and <=                                              31/01/2018EMP_POS_VALID_FROM_DATE and 31/01/2018<EMP_POS_VALID_TO_DATE, EMP_ID))

The out put is needed in a chart like this

Year	Turn Over Rate
2016	3.2
2017	2.8
2018	4.1

Your Support is highly appreciate on this.

Thanking in advance.

Madushan