Yearly Turn over calculated based on average

madushanfernand — Wed, 25 Nov 2020 16:16:04 GMT

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

topic Yearly Turn over calculated based on average in QlikView

Yearly Turn over calculated based on average