Calculate a retention rate

[dcz]

Hello,

Table :

LOAD * INLINE [

Week,ID_Employee
2,A285030
3,A285030
3,A809876
2,A816901
2,A826802
3,A826802
2,A890308
2,A907409
2,A997365
3,A997365

];

I have 6 employees in week 2, in week 3 I have 4, 3 were already present in week 2, 1 is new.

My turnover rate is 66.67% :

Count({<Week={'$(vMaxWeek)'}>}distinct ID_Employee)  /   Count({<Week={'$(vMinWeek)'}>}distinct ID_Employee)

My retention rate is 50% (6 employees in week 2, and in week 3 three of them are still present) but I don't know how to find this number.

Ideas ?

Thanks

[frank85richard]

To calculate retention rate, you simply find how many employees in Week 3 were also in Week 2, then divide by the total in Week 2. For your data, it's 3 out of 6, giving you a 50% retention rate. Easy way to track employee consistency!

[Sandhuuuu · Accepted solution]

Hi there, 

Give this a spin: 

(( Count({< "Week" = {'$(vMaxWeek)'}, [ID_Employee] = P({< "Week" = {'$(vMinWeek)'} >} [ID_Employee]) >} [ID_Employee]) - Count({< "Week" = {'$(vMinWeek)'} >} [ID_Employee]) )
/
Count({< "Week" = {'$(vMinWeek)'} >} [ID_Employee]) )

---------

Assuming your vMinWeek = 2 and your vMaxWeek = 3, you should get an output of -50.0% .

Cheers

[dcz]

Hi,

Thank you very much it works perfectly !