topic Re: Current Attrition Percentage of Employees in Visualization and Usability

Current Attrition Percentage of Employees

sunil-kumar5 — Fri, 14 Aug 2020 09:13:05 GMT

Hi Community,

I want to calculate the current attrition% of employees based on HR data like EE code, joining date and leaving date.

Can anyone please help on this?

Thanks

Sunil Kumar

Re: Current Attrition Percentage of Employees

Steven_Haught — Fri, 14 Aug 2020 13:17:53 GMT

I am sure this is possible, I have down a similar app internally. Can you give a little more information? Are you looking at a certain date range ie 90 day Turnover, YTD, YoY etc?

Re: Current Attrition Percentage of Employees

Saravanan_Desingh — Sat, 15 Aug 2020 19:14:38 GMT

Please share a sample data and expected output

Re: Current Attrition Percentage of Employees

sunil-kumar5 — Mon, 17 Aug 2020 05:43:26 GMT

Hi Saran,

I am using my office laptop and not able to attached or paste any data as security does not allow me to do so. You can put an example like we have EE_Code, Joining Date and Leaving date on the basis of that we need to calculate the current month attrition rate along with what attrition rate we have in the year of 2020,2019,2018 and so on.

Please advise

Thanks

Re: Current Attrition Percentage of Employees

sunil-kumar5 — Mon, 17 Aug 2020 05:44:09 GMT

Hi Steven,

we need to calculate the current month attrition rate along with what attrition rate we have in the year of 2020,2019,2018 and so on.

Please advise

Thanks

Re: Current Attrition Percentage of Employees

Usama — Mon, 17 Aug 2020 14:29:55 GMT

try this:

RangeSum(Above(Count(DateofTermination),0,12)) / RangeAvg(Above(Count(EmpID),0,12))

thanks

Re: Current Attrition Percentage of Employees

sunil-kumar5 — Wed, 19 Aug 2020 05:47:30 GMT

Thanks for your reply.

Can you please advice that would it calculate current month or year both. When I put this it shows me 58%, I am new to qlik so if possible please elabroate so that I can understand it.

Thanks

Re: Current Attrition Percentage of Employees

Steven_Haught — Wed, 19 Aug 2020 20:47:44 GMT

So that Rangeabove function is typically used as a chart function and appears to be looking at a 12 month rolling number.

Second question to help narrow down best way to help you. How are you trying to display the numbers? KPI table, chart? Each will slightly change method of calculation.

Re: Current Attrition Percentage of Employees

sunil-kumar5 — Thu, 20 Aug 2020 04:45:04 GMT

Hi Steven,

I want to display the attrition % in KPI and Year & month on chart so that there will be better understanding as what we have in current month and we had in previous months or years

Re: Current Attrition Percentage of Employees

Steven_Haught — Thu, 20 Aug 2020 19:08:22 GMT

@sunil-kumar5

I used something similar to this:

Count(distinct{$<Leaving_Year={desired year}, Active={'N'}>}EE_Code)
/
Count(distinct{$<Active={'Y'}>}EE_Code)

Where we had created a field for active or terminated in the data also. (It was a helpful field to have!) you could also say Leave_date={">0"}. That basic construct flowed to most of the visualizations in my turnover app.

Re: Current Attrition Percentage of Employees

sunil-kumar5 — Fri, 21 Aug 2020 13:00:57 GMT

Hi Steven,

Sorry, but I didn't understand the code to use, as you mentioned desired year (I want this dynamic not specif for any year).

Re: Current Attrition Percentage of Employees

Steven_Haught — Fri, 21 Aug 2020 19:13:35 GMT

Sorry for any confusion. You can try something like this:

Count(distinct{$<LeaveDate-={}>}EE_Code)
/
Count(distinct EE_Code)

Re: Current Attrition Percentage of Employees

Steven_Haught — Sun, 23 Aug 2020 12:51:24 GMT

@sunil-kumar5 Have you tried that at all? From we can add in more criteria in the set analysis to do 90 hires turnover, 6 month turn over, monthly etc.

Re: Current Attrition Percentage of Employees

sunil-kumar5 — Mon, 24 Aug 2020 07:34:51 GMT

Hi Steven,

I didn't get the notification.

I tried this way and getting 58% attrition rate, I believe this is over all, can you please advise as how can I calculate this basis on current MTD and YTD attrition rate?

Re: Current Attrition Percentage of Employees

Steven_Haught — Mon, 24 Aug 2020 19:05:10 GMT

@sunil-kumar5 You may need to create a couple of fields. A month and year field would be good. I would also create an active Y/N field. Then we can add those to the set analysis to dictate the time frame and specifics of what you are looking for.