topic Re: Calculating an attrition rate in QlikView

Calculating an attrition rate

Mon, 12 Oct 2015 09:33:41 GMT

I would like to show a graph with the attrition rate for various departments.

I do have a table with the monthly headcounts dating back to April 2014 (our fiscal year starts in April).

I also have the number of employees that left the company on a monthly basis dating back to April 2014 in the following format:

Period	Cost Centre	Leavers
30.04.2014	512	2
31.05.2014	512	0
…
31.08.2015	512	1
30.09.2015	512	0
30.04.2014	200	0
31.05.2014	200	2
…	200	1
31.08.2015	200	3
30.09.2015	200	1

The chart should only start showing the values as per April 2015 and I want to have an annualized attrition rate. So for April 2015 the attrition rate should be calculated as follows:

SUM of all leavers between May 2014 and April 2015 for Department X

------------------------------------------------------------------------------------------------------ = Annual Attrition Rate in %

Average of Headcount between May 2014 and April 2015 for Department X

I managed to create a straight table that shows the Average Crew and Cumulative Leavers using the Accumulation of 12 Steps Back in the Expression. But this only works, if I also display the values for 2014. Yet, I do not want to show 2014 values, as these are not for a 12 month basis and therefore not comparable.

Furthermore, if I then try to divide the Leavers / AvgEmployees, I get values that I cannot reconcile.

I'm unsure if it would be easier to do an accumulation during the loading script, or if it would be possible to calculate this using a set analysis.

Thanks for any hints.

Re: Calculating an attrition rate

avinashelite — Mon, 12 Oct 2015 10:06:57 GMT

check this thread :

Calculating rolling n-period totals, averages or other aggregations

Re: Calculating an attrition rate

Tue, 13 Oct 2015 06:53:36 GMT

Thanks for your hint. I didn't see that post yet.

I was hoping that the sum( aggr( rangesum( above( sum(Sales),0,12) ),Month)) would do the trick, but it looks like that is not helping.

I'm a bit reluctant to go for the AsOf table, as it seems to be complicating things quite a bit if you don't have just a simple data model like in your example.

It's kinda disappointing that there isn't a simpler solution to such a common problem

Re: Calculating an attrition rate

avinashelite — Tue, 13 Oct 2015 07:58:35 GMT

Try like this once :

sum( aggr( rangesum( above(total sum({<Month=>}Sales),0,12) ),Month))

Re: Calculating an attrition rate

Thu, 15 Oct 2015 06:11:39 GMT

Hi Avinash

I had to try a bit more and your formula did the job:

That's what I have now and it calculates it correctly.

Sum(Aggr(RangeSum(Above(Total Sum({$<Month=>} Leavers),0,12)), Month))

Thank you very much!!

Re: Calculating an attrition rate

avinashelite — Fri, 16 Oct 2015 05:36:42 GMT

Hi Matthias,

Your always welcome with questions.. Happy learning