Calculating Turnover Rate Dynamically

[mhouck01]

I'm attempting to calculate a turnover rate within a QlikView application using the following formula:

sum(# of Termed Employees) / ((sum(Employees at Beginning of Period) + sum(Employees at End of Period)) / 2)

The main issue is the denominator. It needs to be the average of the number of employees at the beginning of the period and the number at the end of the period. It is not the average of all the employee totals for every day within the period. I want to put this into a line graph with a cyclic dimension, so it can be calculated by day, week, month, quarter, year, etc.

If I wanted to calculate turnover for November, it would be:

sum(# of Termed Employees in November) / ((sum(Employees as of Nov 1st) + sum(Employees as of Nov 30th)) / 2)

I wanted to do this via set analysis, but the set is calculated once for the entire chart, not for each dimension, so that won't work. I also want to avoid sum(if()) due to performance issues. Any ideas how this could be done?

[sunny_talwar · Accepted solution]

Not entirely sure about the exact expression, but may be using FirstSortedValue?

sum(# of Termed Employees in November) / ((FirstSortedValue(Aggr(Sum(Employees), Dimensions), Date) + FirstSortedValue(Aggr(Sum(Employees), Dimension), -Date)) / 2)

[mhouck01]

That's working perfectly. Great idea!